Macro to look for file on multiple drive letters

L

LB79

Ive created a file and macro that reads from another specific file on
network drive. This works OK apart from 1 problem - Not everyone ha
the same letter name for the network drive. For example, for me th
network drive is G but for my colleague the same drive is under lette
H. Because i have created the macro to use drive G it isnt working fo
everyone.
Is there a way that the macro can search either/or (if drive G no
found look for drive H)?? or can it open the second file using th
network path??

Hope i havent made my request too confusing.
Appreciate any help.

Many thanks
 
J

Jim Rech

You can open a file via its UNC path:

Workbooks.Open Filename:= _
"\\Pma0010f09\sys1\APDV\GOALS\Ver2004_2\GOALS001.XLS"


Also, you could see if the file is there before opening it:


If Dir("g:\apdv\goals\Ver2004_2\Goals001.xls") <> "" Then
Workbooks.Open "g:\apdv\goals\Ver2004_2\Goals001.xls"
Else
Workbooks.Open "h:\apdv\goals\Ver2004_2\Goals001.xls"
End If



-
Jim Rech
Excel MVP
| Ive created a file and macro that reads from another specific file on a
| network drive. This works OK apart from 1 problem - Not everyone has
| the same letter name for the network drive. For example, for me the
| network drive is G but for my colleague the same drive is under letter
| H. Because i have created the macro to use drive G it isnt working for
| everyone.
| Is there a way that the macro can search either/or (if drive G not
| found look for drive H)?? or can it open the second file using the
| network path??
|
| Hope i havent made my request too confusing.
| Appreciate any help.
|
| Many thanks!
|
|
| ---
|
|
 
L

LB79

Thanks for this help - it works - I just have 1 more problem with this
I have a cell value in my current workbook that is a link from anothe
workbook. When i open it it automatically updates from drive G, bu
when someone who is assigned drive letter H opens the file, it doesn
upate. Is there a way around this that will check the drive letter?

Many thanks
 
J

Jim Rech

When i open it it automatically updates from drive G

Do you mean, when you open it manually? If so, I don't think there is any
way to automatically make Excel look to another drive letter.

If you're opening it by macro you could run the same test as in my first
post (using Dir) to see if the link is good. If it's not you could use the
workbook ChangeLink method to fix the link.

--
Jim Rech
Excel MVP
| Thanks for this help - it works - I just have 1 more problem with this.
| I have a cell value in my current workbook that is a link from another
| workbook. When i open it it automatically updates from drive G, but
| when someone who is assigned drive letter H opens the file, it doesnt
| upate. Is there a way around this that will check the drive letter?
|
| Many thanks!
|
|
| ---
|
|
 
L

LB79

It is when opening the workbook manually - I could create a macro t
auto run on open to check the link.
Im not familiar with the workbook ChangeLink method. Would you min
giving an example based on the following

Workbook1, Sheet 1, Cell A1 has the formula
='G:\[Workbook2.xls]Sheet1'!A1

Some people have Drive H instead of G.

Sorry in not expaining myself very well.

Appreciate youe kind help
 
J

Jim Rech

You can just record a macro as you use Edit, Links, Change Source.

Here's what I got:

ActiveWorkbook.ChangeLink Name:="C:\Book1.xls", NewName:="D:\Book1.xls", _
Type:=xlExcelLinks
 
Top