VBA to check data link status?

Question

Is there a way to check data link status in excel?

My code changes a data link, but doesn't check to make sure that the link actually points to something useful.

Dim rCountryOld As Range
Dim rCountryNew As Range
Dim vLink() As Variant
Dim sDefPath As String
Dim i As Integer

With ThisWorkbook
    Set rCountryOld = .Names("CountryLink").RefersToRange
    Set rCountryNew = .Names("Country").RefersToRange
    sDefPath = Left(.Names("FileList").RefersToRange.Cells(1, 2).Value, _
        InStrRev(.Names("FileList").RefersToRange.Cells(1, 2).Value, "\") - 1)
    'check for a change in country
    If rCountryOld <> rCountryNew Then
        vLink = .LinkSources
        For i = LBound(vLink) To UBound(vLink)          'if changed, find the country datasheet link
            If UCase(Left(vLink(i), InStrRev(vLink(i), "\") - 1)) = UCase(sDefPath) Then
                Exit For
            End If
        Next i
            .ChangeLink Name:=vLink(i), NewName:=WorksheetFunction.VLookup(rCountryNew, .Names("FileList").RefersToRange, 2, False), _
            Type:=xlExcelLinks 'change to the new country's datasheet
        rCountryOld.Value = rCountryNew.Value
    End If
End With

Thanks for the assistance!

I'd like to specify a bit more: When the `.ChangeLink` part executes, it seem that if the new link name isn't found, the link doesn't actually change. This is the situation I'm interested in detecting — ArcherBird, Mar 24 '16 at 22:18
I suppose I can just add another if statement that compares the link's current path to the path I just tried to change it to. If that is true, I can spit out a messagebox,... — ArcherBird, Mar 24 '16 at 22:36
Should be easy: http://stackoverflow.com/questions/11573914/check-if-the-file-exists-using-vba — Florent B., Mar 24 '16 at 22:55

VBA to check data link status?

0 Answers0