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:
It fails on the line:
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)
Private Sub Workbook_Open()
' Open data worksheet.
If Not FileIsOpen("DgetData.xls") Then
' Switch focus back to orignal worksheet.
' Now force update of links manually.
ActiveWorkbook.UpdateRemoteReferences = True
This used to work. Now it doesn't. Please help.