Thread: macro to open and close Excel workbooks

I have created a system to manage mortgage accounting. Each mortgage has it's own spreadsheet. File names: mortgage.xlsx, mortgage1.xlsx, mortgage2.xlsx up to mortgage49.xlsx.

I have created a master control spreadsheet that pulls information from all the accounts (spreadsheets).
Some of the spreadsheets may not exist, so to prevent a Macro error the file is only opened if it exists. Thus:

If Dir("C:\Users\Owner\Documents\MortAcc\mortgage.xlsx") <> "" Then
Workbooks.Open ("C:\Users\Owner\Documents\MortAcc\mortgage.xlsx")

End If

If Dir("C:\Users\Owner\Documents\MortAcc\mortgage1.xlsx") <> "" Then
Workbooks.Open ("C:\Users\Owner\Documents\MortAcc\mortgage1.xlsx")
   
End If

This part works fine. Having opened them I then need to close them in the same Macro.

However the following does not work and results in a Runtime error 9. Subscript out of range.
This trips up at the first statement below, which IS an existing file:

If Dir("C:\Users\Owner\Documents\MortAcc\mortgage.xlsx") <> "" Then
Workbooks("C:\Users\Owner\Documents\MortAcc\mortgage.xlsx").Close SaveChanges:=False

End If

If Dir("C:\Users\Owner\Documents\MortAcc\mortgage1.xlsx") <> "" Then
Workbooks("C:\Users\Owner\Documents\MortAcc\mortgage1.xlsx").Close SaveChanges:=False
   
End If

We could parse the 1-49, too with another loop, but just in case you want to use any random text strings for your workbook names, you can put them all into an array of string values like so, then just run them all:

Option Explicit

Sub OpenClose()
Dim fileARR As Variant
Dim fName As Long

fileARR = Array("mortgage", "mortgage1", "mortgage2", "mortgage3", "mortgage4", "mortgage5", _
        "mortgage6", "mortgage7", "mortgage8", "mortgage9", "mortgage10", "mortgage11", _
        "mortgage12", "mortgage13", "mortgage14", "mortgage15", "mortgage16,", "mortgage17", _
        "mortgage18", "mortgage19", "mortgage20", "mortgage21", "mortgage22,", "mortgage23", _
        "mortgage24", "mortgage25", "mortgage26", "mortgage27", "mortgage28", "mortgage29", _
        "mortgage30", "mortgage31", "mortgage32", "mortgage33", "mortgage34", "mortgage35", _
        "mortgage36", "mortgage37", "mortgage38", "mortgage39", "mortgage40", "mortgage41", _
        "mortgage42", "mortgage43", "mortgage44", "mortgage45", "mortgage46", "mortgage47", _
        "mortgage48", "mortgage49")
            
For fName = LBound(fileARR) To UBound(fileARR)
    If Dir("C:\Users\Owner\Documents\MortAcc\" & fileARR(fName) & ".xlsx") <> "" Then _
        Workbooks.Open ("C:\Users\Owner\Documents\MortAcc\" & fileARR(fName) & ".xlsx")
Next fName

'other code....

For fName = LBound(fileARR) To UBound(fileARR)
    If Dir("C:\Users\Owner\Documents\MortAcc\" & fileARR(fName) & ".xlsx") <> "" Then _
        Workbooks("C:\Users\Owner\Documents\MortAcc\" & fileARR(fName) & ".xlsx").Close SaveChanges:=False
Next fName

End Sub

Thank you. I really appreciate you taking the time to help me.

Unfortunately your revised version fell apart at the same point as my original one.

Error 9.

Workbooks("C:\Users\Owner\Documents\MortAcc\" & fileARR(fName) & ".xlsx").Close SaveChanges:=False

I added

Application.DisplayAlerts = False

and

Application.DisplayAlerts = True

at the beginning and end of the code. Here is the final version:

Option Explicit

Sub Openspreadsheets()

Application.DisplayAlerts = False

Dim fileARR As Variant
Dim fName As Long

fileARR = Array("mortgage", "mortgage1", "mortgage2", "mortgage3", "mortgage4", "mortgage5", _
        "mortgage6", "mortgage7", "mortgage8", "mortgage9", "mortgage10", "mortgage11", _
        "mortgage12", "mortgage13", "mortgage14", "mortgage15", "mortgage16,", "mortgage17", _
        "mortgage18", "mortgage19", "mortgage20", "mortgage21", "mortgage22,", "mortgage23", _
        "mortgage24", "mortgage25", "mortgage26", "mortgage27", "mortgage28", "mortgage29", _
        "mortgage30", "mortgage31", "mortgage32", "mortgage33", "mortgage34", "mortgage35", _
        "mortgage36", "mortgage37", "mortgage38", "mortgage39", "mortgage40", "mortgage41", _
        "mortgage42", "mortgage43", "mortgage44", "mortgage45", "mortgage46", "mortgage47", _
        "mortgage48", "mortgage49")
            
For fName = LBound(fileARR) To UBound(fileARR)
    If Dir("C:\Users\Owner\Documents\MortAcc\" & fileARR(fName) & ".xlsx") <> "" Then _
        Workbooks.Open ("C:\Users\Owner\Documents\MortAcc\" & fileARR(fName) & ".xlsx")
Next fName

'other code....

For fName = LBound(fileARR) To UBound(fileARR)
    If Dir("C:\Users\Owner\Documents\MortAcc\" & fileARR(fName) & ".xlsx") <> "" Then _
        Workbooks("C:\Users\Owner\Documents\MortAcc\" & fileARR(fName) & ".xlsx").Close SaveChanges:=False
Next fName

Application.DisplayAlerts = True
End Sub

I just don't understand it as both your coding and mine looks like it should do the job.

Please revise your post to add code tags around the code, as shown in my posts and demonstrated in my signature below.

I'm thinking it may be as simple as blowing through those errors. Add this near the top with all the other App settings:

On Error Resume Next

You don't want the path:

Workbooks(fileARR(fName) & ".xlsx").Close SaveChanges:=False

When you close a workbook, you're referring to a workbook in memory, not the workbook on disk, so you don't include the path.

I can't imagine why you would want to open all the workbooks at once, rather than process them sequentially:

Sub OpenSpreadsheets()
    Const sPath     As String = "C:\Users\Owner\Documents\MortAcc\"
    Dim i           As Long
    Dim sFile       As String
    Dim wkb         As Workbook

    For i = 0 To 49
        sFile = "mortgage" & WorksheetFunction.Text(i, "0;;")
        If Len(Dir(sPath & sFile)) Then
            Set wkb = Workbooks.Open(sPath & sFile)

            With wkb
                ' process here, e.g.,
                MsgBox .Worksheets(1).Name
                ' ...

                wkb.Close SaveChanges:=False
            End With
        End If
    Next i
End Sub