Dynamic drive selection to update link

kashyap

Board Regular
Joined
Mar 28, 2009
Messages
173
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
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
This should update all your links:

Code:
Sub ref()
    Dim aLinks As Variant
    Dim i As Integer
    aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(aLinks) Then
        For i = LBound(aLinks) To UBound(aLinks)
            ActiveWorkbook.UpdateLink Name:=aLinks(i)
        Next i
    End If
End Sub
 
Upvote 0
Thanks Andrew..

is can I open a file in the same way as well?

my code is

Workbooks.Open "E:\Primary\Misc\Disposition_of_Calls-May", True


And also above code will not give me any msg if the file is read only.. how to do that?
 
Last edited:
Upvote 0
In the code I posted then name of the source workbook is in the valriable aLinks(i), so you can use that if you need to open it. To open a workbook read only set the Open method's ReadOnly argument to True.
 
Upvote 0
I don't want to open workbook as read only, but when someone else had opened that file and when we open the same file tru macro, then it will not give warning as it does when opened directly..
 
Upvote 0
Hi Andrew, is there a way to update only specific workbook?


This should update all your links:

Code:
Sub ref()
    Dim aLinks As Variant
    Dim i As Integer
    aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(aLinks) Then
        For i = LBound(aLinks) To UBound(aLinks)
            ActiveWorkbook.UpdateLink Name:=aLinks(i)
        Next i
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,558
Messages
6,179,512
Members
452,920
Latest member
jaspers

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top