Open One Spreadsheet from Another

[Bob Myers]

If I run the following macro code (VBA) from a folder on my internal C: hard
drive, it runs correctly (the running Excel spreadsheet with the macro finds
and opens the 2nd Excel spreadsheet).

ChDir (ActiveWorkbook.Path)
Workbooks.Open Filename:=File.xls

However, if co-locate the 2 spreadsheets on a thumb drive, the 1st one runs,
but when it tries to open the 2nd one, I get the message, 'it can't find the
second spreadsheet.' I've checked the Path in Debug and it is correct.
I've also tested this on an external hard drive with the same result. Both
the thumb drive and the external hard drive connect through a USB2 port.
Finally, I made the same test with the 2 spreadsheets co-located in my D:
partition on my internal hard drive with the same result.

I guess my question is, "is this normal behavior, one Excel spreadsheet
can't find and open another Excel spreadsheet unless the 2 of them are
located on the same drive as the Excel application?"

FYI, I'm running an older version of Excel (2003) and my OS is Vista Home
Premium.

Respectfully submitted,
Bob Myers

 

[Dave Peterson]

ChDir doesn't change the drive--it just changes the current/logged directory on
that drive:

ChDrive activeworkbook.path
ChDir ActiveWorkbook.Path

'and this line isn't right as written. Maybe missing the double quotes???
Workbooks.Open Filename:="File.xls"

Or maybe better is to not change drive or folders:

workbooks.open filename:=activeworkbook.path & "\" & "file.xls"
or
workbooks.open filename:=activeworkbook.path & "\file.xls"