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

 

[Andrew Poulsom]

This should update all your links:

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

 

[kashyap]

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?

 

[Andrew Poulsom]

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.

 

[kashyap]

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..

 

[Andrew Poulsom]

Here is a way to check if a file is already open:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;291295

 

[kashyap]

Hi Andrew, is there a way to update only specific workbook?

This should update all your links:

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

 

[Andrew Poulsom]

Yes, if you know its name:

ActiveWorkbook.UpdateLink Name:="NameOfWorkbook"