Dynamic drive selection to update link

[Kashyap]

I have the below code live.. but can I have a code that search in drive F, G,
H if this path does not exist?

That is it should search in
"F:\Primary\Master-May.xls", _
"G:\Primary\Master-May.xls", _
"H:\Primary\Master-May.xls", _

Sub ref()

ActiveWorkbook.UpdateLink Name:= _
"E:\Primary\Master-May.xls", _
Type:=xlExcelLinks

End Sub

 

[Dave Peterson]

Option explicit
Sub ref()
Dim myList as variant
dim iCtr as long
dim TestStr as string
dim FoundIt as boolean

mylist = array("F:\Primary\Master-May.xls", _
"G:\Primary\Master-May.xls", _
"H:\Primary\Master-May.xls")

foundit=false
for ictr = lbound(mylist) to ubound(mylist)
teststr = ""
on error resume next
teststr = dir(mylist(ictr))
on error goto 0

if teststr = "" then
'not found, keep looking
else
foundit = true
ActiveWorkbook.UpdateLink Name:=mylist(ictr), Type:=xlExcelLinks
exit for
end if
next ictr

if foundit = false then
msgbox "not found in any of those places!"
else
msgbox "Updated!
end if

End Sub

 

[joel]

If you are using a network I would use the drive name rather than the letter

"\\networkname\Master-May.xls"

You can find the network name in windows explorer by going to menu

tools - disconnect Netwrok Drive

MyDrives = Array( _
"F:\Primary\Master-May.xls", _
"G:\Primary\Master-May.xls", _
"H:\Primary\Master-May.xls")

Set fs = CreateObject("Scripting.FileSystemObject")
MyDrive = ""
for each Drv in MyDrives
if fs.DriveExists(Drv) = true then
MyDrive = Drv
exit for
end if
end if

if Drv = "" then
msgbox("Drive not found")
end if