VBA: Skip file path that cannot be found

[wirescool]

Hello!

I have a number of file paths listed in Column A of Sheet1 that have to be opened and saved daily. There are formulas in these sheets that are linked back to other data sheets that should remain open the whole time.

However, the current macro I have fails to a full stop if one of the files is moved or replaced. Instead of this, I would like to have the word "Review" in Column B next to the file that could not be opened, and for the macro to continue on to the next file.

Here's what I have so far:

Sub openandsave()

Dim lastRow As Long
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    'Open all database files
    Workbooks.Open Filename:= _
        "...\Current.xls", UpdateLinks:=3


    Workbooks.Open Filename:= _
        "...\DataLinks.xls", UpdateLinks:=3


    Workbooks.Open Filename:= _
        "...\Market Statistics.xls", UpdateLinks:=3


    Workbooks.Open Filename:= _
        "...\PartnershipPrices.xls", UpdateLinks:=3


    Workbooks.Open Filename:= _
        "...\Positions.xls", UpdateLinks:=3
        
    Workbooks.Open Filename:= _
        "...\Options Database.xls", UpdateLinks:=3


    Workbooks.Open Filename:= _
        "...\PricesNewInvest.xls", UpdateLinks:=3


    Workbooks.Open Filename:= _
        "...\Weekly Invest List.xls", UpdateLinks:=3
    
    For i = 1 To lastRow
        On Error Resume Next
        With Workbooks.Open(Range("A" & i), UpdateLinks:=3)
            .Save
            .Saved = True
            .Close
        End With
    Next
    
    
    'Close database workbooks
    Workbooks("Current.xls").Close SaveChanges:=True
    Workbooks("DataLinks.xls").Close SaveChanges:=True
    Workbooks("Market Statistics.xls").Close SaveChanges:=True
    Workbooks("PartnershipPrices.xls").Close SaveChanges:=True
    Workbooks("positions.xls").Close SaveChanges:=True
    Workbooks("PricesNewInvest.xls").Close SaveChanges:=True
    Workbooks("Options Database.xls").Close SaveChanges:=True
    Workbooks("Weekly Invest List.xls").Close SaveChanges:=True




    Application.ScreenUpdating = True


End Sub

TIA!

 

[RobertSF]

Hi, there. I can't test any of this from this computer right now, but I can think of a couple of ways.

First, you can use the Dir (or Dir$) statement to see if the file is there.

Dim myFile as String
myFile = Dir$("C:\mydir\myfile.xlsx")

If the file exists, the variable myFile will receive the value "myfile.xlsx." If not, it will receive "".

So you could check, and if the file is not there, put "Review" in column B and continue to the next one.

The difference between Dir and Dir$ is that the first returns a Variant type and the second a String type. I don't think it matters much which you use.

The second way assumes that trying to open a file that's not there generates an error. I can't remember offhand if it does, but from what you say (the macro " fails to a full stop"), I'm guessing it does.

In that case, look into error handling with the On Error Goto statement. Basically, it goes like this:

On Error Goto Handler
'Your code that might generate an errror here
Exit Sub

Handler:
On Error Goto 0 'turn error catching off
'Your code that puts "Review" in column B
Exit Sub

Here are three good links about error handling in VBA.
http://www.cpearson.com/excel/errorhandling.htm
https://excelmacromastery.com/vba-error-handling
https://stackoverflow.com/questions/6028288/properly-handling-errors-in-vba-excel

Note: Stackoverflow is an excellent source, but beware of asking questions there with a thin skin. The people there are incredibly picky about how people ask questions.

 

[wirescool]

Thank you! These resources helped me get it working!

Here's what I did:

Sub ErrorTest()    
Dim lastRow As Long


    Application.DisplayAlerts = False
    Application.ScreenUpdating = False


    With ActiveSheet
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    'Loop through files, save, and close
    For i = 1 To lastRow
        On Error GoTo errhandler:
        With Workbooks.Open(Range("A" & i), UpdateLinks:=3)
            .Save
            .Saved = True
            .Close
        End With
    Next
    
errhandler:
    Range("B" & i).Value = "Review"
    Resume Next
     
End Sub

 

[RobertSF]

You're welcome! I'm very glad you found a solution.