Loop not appropriately opening file paths

[wirescool]

Hello!

I have a list of file paths in a workbook, in cells A1:A307. I would like to open some master data files, then open the files listed in Column A one-by-one, update formula values that are referring to the master data files, save them, and then close them.

If the file is unable to be opened, I would like for "Review" to be placed in Column B next to the path.

I have the following code, but when I run it, it appears to not have attempted to open all the files listed in Column A, but it doesn't stop working on any specific line of code either. I placed some dummy erroneous paths at the bottom of the list, and these aren't getting flagged with the "Review" designation. Additionally, the legitimate files do not show a modified date/time corresponding to the time that I attempt to run the macro.

Have I gone wrong somewhere in my error handling? It seemed simple enough, but I guess I was wrong!

TIA!

Sub openandsave()

Dim lastRow As Long
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    With ActiveSheet
        lastRow = Range("A1").End(xlDown).Row
    End With
    
    'Open all database files
    Workbooks.Open Filename:= _
        "C:\Test\Current.xls", UpdateLinks:=3


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


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


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


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


    Workbooks.Open Filename:= _
        "C:\Test\PricesNewInvestrak.xls", UpdateLinks:=3


    Workbooks.Open Filename:= _
        "C:\Test\Weekly Investrak List.xls", UpdateLinks:=3
    
    'Loop through files, save, and close
    For i = 1 To lastRow
        On Error GoTo allocationerror:
        With Workbooks.Open(Range("A" & i), UpdateLinks:=3)
            .Close SaveChanges:=True
        End With
    Next i
    
allocationerror:
    Range("B" & i).Value = "Review"
    Resume 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("PricesNewInvestrak.xls").Close SaveChanges:=True
    Workbooks("Options Database.xls").Close SaveChanges:=True
    Workbooks("Weekly Investrak List.xls").Close SaveChanges:=True


    Application.ScreenUpdating = True


End Sub

 

[cerfani]

i dont think you can tell it to go to the error label and then it will jump back and resume the loop. Maybe Im wrong, but vba is not a typical language so maybe that works. I believe it will just start closing the workbooks and the sub will end after though.

You should be resuming next on errors and checking if the openedworkbook is nothing... if it is nothing then it failed and you should write review.

On Error Resume Next
For i = 1 To lastRow
    Set wb = Workbooks.Open(Range("A" & i), UpdateLinks:=3)
    If wb Is Nothing Then
        Range("B" & i).Value = "Review"
    Else
        wb.Close SaveChanges:=True
    End If
Next i

 

[wirescool]

I've updated my side to look like this, and removed all but two file paths from Column A so I now have one good path and one bad path.

I ran the updated version, and still have the same results - nothing was updated. It took a really long time to finish up, too. Maybe 10 minutes or so, for just the two paths being used by the loop?

How can this be silently failing both ways around?

 

[cerfani]

let's see your code

maybe opening the workbooks is taking long, maybe just check if the files exist or something instead of trying to open the workbooks.

 

[wirescool]

Got this working earlier today with the old error handling method. It just needed specification on the source sheet where the file paths were coming from. Totally my mistake!

It took about 40 minutes in total to finish, using 307 file paths in Column A. After more review, 10 minutes does seem realistic for opening all of the master data sheets at the beginning. They're pretty large files, so I'm alright with that.

Here's what worked for me:

Sub openandsave()

Dim lastRow As Long
Dim wb As Workbook
Dim srcWS As Worksheet


Set srcWS = ActiveSheet
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    With srcWS
        lastRow = Range("A1").End(xlDown).Row
    End With
    
    'Open all database files
    Workbooks.Open Filename:= _
        "C:\Test\Current.xls", UpdateLinks:=3


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


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


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


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


    Workbooks.Open Filename:= _
        "C:\Test\PricesNewInvestrak.xls", UpdateLinks:=3


    Workbooks.Open Filename:= _
        "C:\Test\Weekly Investrak List.xls", UpdateLinks:=3
    
    'Loop through files, save, and close
    For i = 1 To lastRow
        On Error GoTo allocationerror:
        With Workbooks.Open(srcWS.Range("A" & i), UpdateLinks:=3)
            .Close SaveChanges:=True
        End With
    Next i
    
allocationerror:
    srcWS.Range("B" & i).Value = "Review"
    Resume 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("PricesNewInvestrak.xls").Close SaveChanges:=True
    Workbooks("Options Database.xls").Close SaveChanges:=True
    Workbooks("Weekly Investrak List.xls").Close SaveChanges:=True


    Application.ScreenUpdating = True


End Sub