CODEWITHSUNDEEP
vbaexcel
Jon Smith
Jon Smith

Reputation: 93

How to skip to next in a list if vba can't find a file

I have a code that uses a list of company names, a code then converts these to file destinations and then another code goes through each and opens them and takes values off of each.

e.g. Admiral_Group-2015-AR converts to C:\Users\Jon\Desktop\CodeBackups\Companies\Admiral_Group-2015-AR.xlsx (FSOURCE)

The issue I have is that I do not have all the files for the ones in the list yet and so the code errors when it cannot find a file. How can I make it skip to the next file in the loop instead?

This is the part of code that I have:

For startno = 1 To endno

    If IsEmpty(WS_Companies.Range("A:A").Find(what:="File Name").Offset(startno, 0).Value) = False Then

    FSource = WS_Companies.Range("A:A").Find(what:="File Name").Offset(startno, 1).Value

    Set WB_Report = Workbooks.Open(FSource)

Thanks

Upvotes: 2

Views: 546

Answers (1)

Alex K.
Alex K.

Reputation: 175816

Check for its existence with Dir$() before attempting to open it:

If Len(Dir$(FSource)) then
   '// file exists on disk
   Set WB_Report = Workbooks.Open(FSource)
   ...
End if

Upvotes: 3

Related Questions