Run-time Error '1004' The file could not be accessed in Excel 2016

I've been given an Excel 2016 spreadsheet which I don't own and do not maintain. A macro is mapped to a button, which passes a filename such as .\input\common\datafile.txt.

The spreadsheet ships with the datafile.txt file in the input\common\ subdirectory below the spreadsheet root directory.

When other members of the team execute the macro in the spreadsheet, the datafile.txt file loads without problem. On my PC, the macro halts with run time error 1004, saying that the file cannot be accessed.

The macro looks like this:

Sub Load_Input_File(file_location As String)

' Commented out other code'

Workbooks.OpenText filename:=file_location, Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True

and the debugger shows that file_location is ".\input\common\datafile.txt". If I change file_location to be "D:\Temp\input\common\datafile.txt", the macro runs correctly.

For all other users, the macro runs as is. On my system, which is the same standard laptop (Windows 10, Excel 2016) as my teammates, the relative path fails.

Since I don't own the spreadsheet or the macro, changing the code is not an option. Can anyone think of any environmental reason why Excel reports not finding a file based on a relative pathname? It's almost as if it's thinking it's running from a different location or something.

I'm neither an Excel guru nor a VBA wizard. This was provided by another group as a "it just works" solution that has in fact run for everyone but me. I supposedly have the same installation of Office and etc. as my team mates. I don't see any privilege or permission differences between our accounts.

Does anyone have any ideas?

As Tim Williams wrote in the comments, your issue is that .\input\common\datafile.txt points to the so called current working directory (cwd). This cwd can change at any time, so it's not a very clever idea to use this as path in the code. Note that the cwd is valid for the whole Excel session (including VBA), not only one workbook. You can check the cwd in VBA with the command CurDir.

Anyhow, as you stated that you can't change the code, you need to change the cwd so that points to the folder where the Excel with the macro is (or, more precise, where the Input-subfolder is located.

You can change the cwd by opening the File Browser Dialog: In Excel, use File->Open and click Browse. Now navigate to the folder. If your Excel Workbook with the data is not already open, you can open it from here, if it is already open, just press Cancel.

Alternative is to use the ChDir-command in VBA. You could create a small macro in your Personal file and maybe assign a keyboard shortcut to it (if you don't have a Personal workbook yet, see the Microsoft documentation).

Sub openTheMagicMacroFile()
    Const Path = "C:\Users\FunThomas\Documents\TestVBA"
    Const Filename = "LoadInput.xlsm"
    
    ChDrive left(path, 2)
    ChDir Path
    Workbooks.Open Path & "\" & Filename
End Sub

Just adapt the Path and Filename to your needs. If you want, you can also add a call to the macro within the workbook (use the Application.Run command)

Update Thanks to the hint of Tim Williams: You will need to issue a ChDrive to set the drive first. In the background, there is a kind of cwd for every drive, so if you issue the chDir command for a different drive, the cwd of that drive changed, but the real cwd remains untouched. Code adapted.