[RESOLVED] Excel Runtime error 1004 FileName could not be found error

[GrayS]

I am a noob to these boards (and fairly new to Excel VBA), so please forgive if this is a dumb question.

I have an Excel 2003 spreadsheet ("DgetWork.xls") with some VBA to automatically open a second spreadsheet ("DgetData.xls") when the first is opened. It used to work perfectly. Then work gave me a new laptop (still Excel 2003 though). Now it always fails with error:

Run-time error '1004'
'DgetData.xls' could not be found. Check the spelling....

Please can somebody help me to get this working again?
I have installed all the add-on packs to no avail. The spelling of the file name is correct, and the two spreadsheets are in the same folder. The Excel 2003 VBA in the first spreadsheet is as follows:

Function FileIsOpen(pFileName As String) As Boolean
  'Function to test if a spreadsheet is open or not.

  On Error Resume Next
  FileIsOpen = Len(Excel.Application.Workbooks(pFileName).Name)
  
End Function


Private Sub Workbook_Open()
    
  ' Open data worksheet.    
  If Not FileIsOpen("DgetData.xls") Then
    Workbooks.Open "DgetData.xls"
  End If
  
  ' Switch focus back to orignal worksheet.
  Workbooks("DgetWork.xls").Activate
    
  ' Now force update of links manually.
  ActiveWorkbook.UpdateRemoteReferences = True
  
End Sub

It fails on the line:

Workbooks.Open "DgetData.xls"

This used to work. Now it doesn't. Please help.

Cheers,

Gray

[westconn1]

you need to supply the full path to the workbook to open, as well as the filename

[GrayS]

I am very sorry, but why should I now need to specify the full path, when I never needed to previously?

I swear it used to worked perfectly.....

Also, I want to distribute these spreadsheets to work colleagues, and they might not save the files in the same location as me. So the path would therefore automatically be wrong. As long as the two files were in the same folder, this always used to work just fine.

Sorry, but I am very confused.

[westconn1]

there are several reasons why the default path of excel may have changed
if the 2 workbooks are in the same folder, you could use thisworkbook.path as the path to the second workbook

[GrayS]

This sounds very hopeful....
Please could you give me an example of it's usage, as I am not familiar with the syntax of this command?
Cheers,
Gray

[GrayS]

Got it.
THANK YOU for your help! Changed the error code line to be:

Workbooks.Open ThisWorkbook.Path & "\DgetData.xls"

And it works!
You're a hero.

[westconn1]

glad you got it working, better you figured it out for yourself