Check if external link exists before updating

[dparteka]

I have a main-workbook that has hundreds of links to sub-workbooks. The sub-workbooks get created daily so their quantity is always growing day by day. The main-workbook already has all the links to the existing sub-workbooks in it and also the ones that have yet to be created.

I'm using the code shown below in a commandbutton to perform the link updates as needed. That code is for the first two links, all the others are the same except 18-xxx changes.

This works fine except when it gets to the first link that does not exist, when that happens it wants to be directed to the location of the link which does not exist.

The question: Is there code that can skip over the ones that do not have a link or code that checks if there is a link and if not then moves on. I've tried On Error Resume Next but this doesn't work, probably because it's not really an error.

Thank you for looking, your help will be genuinely appreciated.

  ActiveWorkbook.UpdateLink Name:= _
  "H:\Public\Quality Assurance\Issue Resolution Reports\2018\Records\18-001.xlsm" _
  , Type:=xlExcelLinks

  ActiveWorkbook.UpdateLink Name:= _
  "H:\Public\Quality Assurance\Issue Resolution Reports\2018\Records\18-002.xlsm" _
  , Type:=xlExcelLinks

 

[Marc L]

Just use Dir function …​

 

[Belleke]

Maybe?

 Sub checkdir()
    Dim FilePath As String
    FilePath = "H:\Public\Quality Assurance\Issue Resolution Reports\2018\Records\18-*.xlsm"
    If Dir(FilePath) <> "" Then
        'your code
    Else
        MsgBox "File doesn't exist."
    End If
End Sub

or

 Sub doesWBexist()
    With Application.FileSearch
        .LookIn = "H:\Public\Quality Assurance\Issue Resolution Reports\2018\Records"
        .Filename = "18-*.xlsm"
            If .Execute > 0 Then
                'your code
            Else
                MsgBox "File doesn't exist."
            End If
    End With
End Sub

 

[dparteka]

Belleke... I did get your doesWEexist code to work, had to tweak it a bit because of this https://answers.microsoft.com/en-us...s/669118de-e3a5-4815-bb95-0a83eb6bf984?auth=1

I really appreciate the help, thank you

 

[Belleke]

Thanks for the feed back.

 

[dparteka]

UPDATE... it looked as if this was working, it runs through the code without errors but it's not updating the links. Can anyone see what I'm missing here, I sure can't.

Private Sub CommandButton9_Click()
'Update Petition Links
    With ApplicationFileSearch
        LookIn = "H:\Public\Quality Assurance\Issue Resolution Reports\2018\Records"
        Filename = "18-*.xlsm"
            If Execute > 0 Then
    ActiveWorkbook.UpdateLink Name:= _
        "H:\Public\Quality Assurance\Issue Resolution Reports\2018\Records\18-001.xlsm" _
        , Type:=xlExcelLinks
    ActiveWorkbook.UpdateLink Name:= _
        "H:\Public\Quality Assurance\Issue Resolution Reports\2018\Records\18-002.xlsm" _
        , Type:=xlExcelLinks
                   Else
                Exit Sub
            End If
    End With
End Sub