Error when VB tries to open file from Windows Explorer

[Guest]

I have a series of files that are maintained by several different users.
When they are finished updating, they click a button that "uploads" (copies &
pastes) their data into a master file. Here is the problem: If the user
opens their file from Windows Explorer (vs. opening from within Excel), they
get an error message when they try to perform the upload process that says
the [master file] cannot be found. On the other hand, if they open their
individual file within Excel (vs. thru Windows Explorer), they get no error
message when they perform the upload. It almost seems like I need to add
code that clearly identifies the file type as being "Excel" based?? Anyway,
here is the code that tries to open the master file:

Dim MasterFile
MasterFile = Master Tracker Q108.xls

ChDir "R:\Sales\Shared\NEW ITEM TRACKING\Q108\"
Workbooks.Open Filename:=MasterFile, WriteResPassword:="JDAY"

 

[Dave Miller]

Dim MasterFile As String

MasterFile = "R:\Sales\Shared\NEW ITEM TRACKING\Q108\Master Tracker
Q108.xls"

Workbooks.Open MasterFile, , , , , "JDAY"

 

[Guest]

I tried your suggestion, but for some reason, it did not work. In fact, now
it even results in an error if you open the "user" file within
Excel---whereas before, at least that would work. Is there anything else I
can try? Perhaps something I can do with the AutoOpen procedure to ensure
that regardless of where the file is opened (Excel or Explorer) that it
"knows" the file is Excel? Thanks for your help.

 

[NickHK]

Considering is not valid VBA, what are you actually using ?

Dim MasterFile
MasterFile = Master Tracker Q108.xls

Maybe
Dim MasterFile As String
MasterFile = "Master Tracker Q108.xls"

Also, are you sure all users have the same drive letter in their mapping ?
And what does the ChDir achieve ?

NickHK

I have a series of files that are maintained by several different users.
When they are finished updating, they click a button that "uploads" (copies &
pastes) their data into a master file. Here is the problem: If the user
opens their file from Windows Explorer (vs. opening from within Excel), they
get an error message when they try to perform the upload process that says
the [master file] cannot be found. On the other hand, if they open their
individual file within Excel (vs. thru Windows Explorer), they get no error
message when they perform the upload. It almost seems like I need to add
code that clearly identifies the file type as being "Excel" based?? Anyway,
here is the code that tries to open the master file:

Dim MasterFile
MasterFile = Master Tracker Q108.xls

ChDir "R:\Sales\Shared\NEW ITEM TRACKING\Q108\"
Workbooks.Open Filename:=MasterFile, WriteResPassword:="JDAY"