Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic drive selection to update link
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic drive selection to update link
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 Kashyap wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic drive selection to update link
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 "Kashyap" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
update all hyperlinks to another drive | Excel Programming | |||
Import from MS Access - Lose the drive letter..link only to drive | Excel Programming | |||
Link to Network Drive | Excel Discussion (Misc queries) | |||
Link to Network Drive | New Users to Excel | |||
Link workbooks-C drive to network drive | Excel Worksheet Functions |