Issues with Working and Activating Multiple Workbooks excel VBA

Question

I'm having some issues when working with calling and working with multiple workbooks. I have a macro that updates some excel sheets using some data. All 5 of those spreadsheets are assigned a variable with the filename and the file path. When I run a second SUB / Macri to Save and Close all 5 of those workbooks after they are done updating - It doesn't select the right workbooks even though they've been assigned the same variable names as the previous macro. So I believe my issue is - If the workbooks are already open I can't associate a variable too them. - I assume that if the file name and file path are right, the workbooks open can be set as variables and then be able to close them - Thoughts?

    Sub CloseWorkbooks()

    Dim MB, WB1, WB2, WB3, WB4, WB5 As Workbook
    Dim FP1, FN1, FN2, FN3, FN4, FN5 As String


    FP1 = "G:\DATA......"

        FN5 = "Book1.xlsx"
        FN2 = "Book2.xlsx"
        FN3 = "Book3.xlsx"
        FN4 = "Book4.xlsx"
        FN1 = "Book5.xlsx"

        Application.DisplayAlerts = False

            Set WB1 = Workbooks.Open(Filename:=FP1 & FN1)
            WB1.Activate
            WB1.Save

            'Application.Wait (Now + TimeValue("00:00:05"))
            WB1.Close


                Set WB2 = Workbooks.Open(Filename:=FP1 & FN2)
                WB2.Activate
                WB2.Save
                'Application.Wait (Now + TimeValue("00:00:05"))
                WB2.Close


                    Set WB3 = Workbooks.Open(Filename:=FP1 & FN3)
                    WB3.Activate
                    WB3.Save
                    Application.Wait (Now + TimeValue("00:00:02"))
                    WB3.Close


                        Set WB4 = Workbooks.Open(Filename:=FP1 & FN4)
                        WB4.Activate
                        WB4.Save
                        Application.Wait (Now + TimeValue("00:00:02"))
                        WB4.Close


                            Set WB5 = Workbooks.Open(Filename:=FP1 & FN5)
                            WB5.Activate
                            WB5.Save
                            Application.Wait (Now + TimeValue("00:00:02"))
                            WB5.Close



End Sub

This works if the worksheets are NOT Open, but it doesn't work if the worksheets are open - which is what I want it to accomplish. The previous macro opens all the worksheets and updates them. I want this macro (2nd one shown above) to save and close all the workbooks.

-Thank you.

Are the workbooks open when you run this code? – NickSlash Mar 30 '20 at 22:35 — NickSlash, Mar 30 '20 at 22:35

NickSlash · Answer 1 · 2020-03-30T22:58:20.063

If you replace your Workbooks.Open(...) with a call to this it should return the workbook if its open, and open and return it if it is not.

Public Function getWorkbookByFileName(ByVal FileName As String) As Workbook
Dim Book As Workbook: Set Book = Nothing
Dim Count As Integer: Count = Application.Workbooks.Count
Dim Index As Integer: Index = 1
Do
    If Application.Workbooks(Index).FullName = FileName Then
        Set Book = Application.Workbooks(Index)
        Exit Do
    End If
    If Index < Count Then
        Index = Index + 1
    Else
        Exit Do
    End If
Loop
If Book Is Nothing Then
    Set Book = Application.Workbooks.Open(FileName:=FileName)
End If
Set getWorkbookByFileName = Book
End Function

This is fairly long winded, looking at some of the answers to Detect whether Excel workbook is already open I see that it can be achieved without looping through the workbooks.

score 0 · Answer 2 · answered Mar 30 '20 at 22:50

This will close the correct workbook.

Sub CloseWorkbooks()
    Application.DisplayAlerts = False

    Dim wb As Workbook
    Dim i As Long

    For i = 1 To 6
        Set wb = Workbooks.Open(Filename:="G:\DATA......Book" & i & ".xlsx")
        Application.Wait (Now + TimeValue("00:00:02"))
        wb.Close SaveChanges:=True
    Next i
Exit Sub

Issues with Working and Activating Multiple Workbooks excel VBA

2 Answers2