Prompt user to select directory
 

I'm using this to open a wb on a shared network drive: Set wb =
Workbooks.Open("N:\CP H6 Lookup.xls"). But if for some reason the user
has this drive mapped on O: it's obviously going to error out b/c it
can't find the file, so what can I add to this so that if it does
error out it will prompt the user to select the appropriate directory
containing this file?

Thanks,
-- Dan

Prompt user to select directory
 

You are correct in that you have to handle the case where the file is not
found but instead of using N: you should be using the full UNC. On you system
take a look at how your n drive is mapped. It will be something like
"\\Myserver\MyDirectory" (Check it in Windows Explorer by selecting Tools -
Map New Directory - Select your N drive)...
Now redefine how you find the file something like

dim wbk as workbook

on error resume next
set wbk = Workbooks.Open("\\Myserver\MyDirectory\CP H6 Lookup.xls")
if wbk is nothing then
msgbox "Lets go find CP H6 Lookup.xls"
Application.Dialogs(xlDialogOpen).Show
end if
--
HTH...

Jim Thomlinson


"Dan R." wrote:

I'm using this to open a wb on a shared network drive: Set wb =
Workbooks.Open("N:\CP H6 Lookup.xls"). But if for some reason the user
has this drive mapped on O: it's obviously going to error out b/c it
can't find the file, so what can I add to this so that if it does
error out it will prompt the user to select the appropriate directory
containing this file?

Thanks,
-- Dan

Prompt user to select directory
 

On Mar 2, 12:38 pm, "Dan R." wrote:
I'm using this to open a wb on a shared network drive: Set wb =
Workbooks.Open("N:\CP H6 Lookup.xls"). But if for some reason the user
has this drive mapped on O: it's obviously going to error out b/c it
can't find the file, so what can I add to this so that if it does
error out it will prompt the user to select the appropriate directory
containing this file?

Thanks,
-- Dan

Use the actual computer name instead of a drive letter. If it's a
network drive, it's probably called something else anyway. Go to your
drive list (my computer) and check the network / computer name for the
drive letter. In most cases, clicking the Tools-Disconnect Network
Drive... will show you the currently designated drive letters and
their real names. (be sure not to actually do the disconnect!)
then replace your drive letter with the actual path.
N: is replaced ...
maybe N is mapped to \\computername\
maybe it is mapped to a folder under that e.g. \\computername\folder
\folder\
either way, it'll work no matter what the drive letter is that's
mapped.

Prompt user to select directory
 

Excellent, thanks guys, and thanks for the code Jim.

-- Dan