file path

[Mark Kubicki]

i need to reference a file:
"E:\@ Timesheets\Job Nos for Timesheets.xls"
(the file is shared)

problem is that some of the office has the server as "E:", some as "F:",
and some as "E:\Server\"

and suggestions for determining and quickly extracting the file path with i
could then concatenate to "@ Timesheets\Job Nos for Timesheets.xls",
creating a full path...

thought at this would give me direction, but alas:

Set fs = Application.FileSearch
With fs
.LookIn = "My Computer"
.Filename = "Job Nos for Timesheets.xls [Shared]"

thanks in advance,
mark

 

[steve]

Mark,

This gets the file name with path without opening the file. It displays the
Open File dialog and allows the user to search and find the file

fname = Application.GetOpenFilename
Msgbox fname

 

[jaf]

Hi Mark,
Try \\servername\shareddrivename\path\filename