Need help with the following code--To Save and Close

[rsrasc]

Hi all,

I'm using the following code to open the following workbooks. I need some help so I can save the files to the same location and close all of them.

I have tried several options but doesn't work for me.

I already have a code to save and close all the open excel files but on this one I just need the ones listed below.

Sub OpenUpFiles_5()
Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\025 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\050 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\056 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\130 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\200 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\250 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\350 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\360 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\400 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\450 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\500 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\600 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
Workbooks.Open ("Z:\FY24 Budget\SSE Revenues\050 FY 2024 Revenues-SSE-Budget.xlsx"), UpdateLinks:=0
Workbooks.Open ("Z:\FY24 Budget\SSE Revenues\130 FY 2024 Revenues-SSE-Budget.xlsx"), UpdateLinks:=0
Workbooks.Open ("Z:\FY24 Budget\SSE Revenues\200 FY 2024 Revenues-SSE-Budget.xlsx"), UpdateLinks:=0
End Sub

For example, I'm using the following code to save and close all the open workbooks in excel but in my case I need to save and close specific files.

Hope this helps.

Regards,
rsrasc

Sub CloseAndSaveOpenWorkbooks_26()
Dim Wkb As Workbook
With Application
    .ScreenUpdating = False
    'Loop through the workbooks collection
    For Each Wkb In Workbooks
        With Wkb
            'if the book is read-only don't save but close
            If Not Wkb.ReadOnly Then
                .Save
            End If
            'We save this workbook, but we don't close it _
            because we will quit Excel at the end, _
            Closing here leaves the app running, but no books
            If .Name <> ThisWorkbook.Name Then
                .Close
            End If
        End With
    Next Wkb
        .ScreenUpdating = True
        .Quit 'Quit Excel
    End With
End Sub

[June7]

You are not using CODE tags correctly. Click the # icon on post edit toolbar to get proper tags.

AFAIK, if you want to reference specific open file, need to know file's Windows handle.

[JosephTurner]

You can customize this code by adding more file paths and the corresponding open/close operations for each workbook you want to process. To run this code:

1. Press Alt + F11 to open the VBA editor in Excel.
2. Insert a new module by clicking Insert > Module.
3. Paste the code into the module.
4. Modify the FilePath variable with the paths to your desired workbooks.
5. Close the VBA editor.
6. Run the macro by pressing Alt + F8, selecting "OpenAndSaveWorkbooks," and clicking "Run."

If you're a fan of strategy and precision, gold miner promises to keep you engaged, but its dated graphics may not meet the expectations of gamers accustomed to cutting-edge visuals.

[Paul_Hossler]

Not tested, but maybe something like this

Option Explicit


'Sub OpenUpFiles_5()
'Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\025 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
'Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\050 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
'Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\056 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
'Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\130 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
'Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\200 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
'Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\250 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
'Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\350 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
'Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\360 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
'Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\400 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
'Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\450 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
'Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\500 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
'Workbooks.Open ("Z:\FY24 Budget\SSE Expenses\600 FY 2024 Expenses-SSE-Budget.xlsx"), UpdateLinks:=0
'Workbooks.Open ("Z:\FY24 Budget\SSE Revenues\050 FY 2024 Revenues-SSE-Budget.xlsx"), UpdateLinks:=0 <<<<<<<<< Dup
'Workbooks.Open ("Z:\FY24 Budget\SSE Revenues\130 FY 2024 Revenues-SSE-Budget.xlsx"), UpdateLinks:=0 <<<<<<<<< Dup
'Workbooks.Open ("Z:\FY24 Budget\SSE Revenues\200 FY 2024 Revenues-SSE-Budget.xlsx"), UpdateLinks:=0 <<<<<<<<< Dup
'End Sub




Sub CloseAndSaveOpenWorkbooks_26()
    Dim aryWorkbooks As Variant, v As Variant
    Dim Wkb As Workbook
    Dim sPath As String, sName As String
    
    aryWorkbooks = Array("025", "050", "056", "130", "200", "250", "350", "360", "400", "450", "500", "600")


    Application.ScreenUpdating = False
    
    'Loop through the workbooks collection
    For Each v In aryWorkbooks
        sPath = "Z:\FY24 Budget\SSE Revenues\" & v & " FY 2024 Revenues-SSE-Budget.xlsx"
        sName = v & " FY 2024 Revenues-SSE-Budget.xlsx"
        Debug.Print sPath, sName
        
        On Error GoTo NextWorkbook
        Set Wkb = Workbooks(sName)
        On Error GoTo 0
        
        If Wkb.ReadOnly Then
           'if the book is read-only don't save but close
            Wkb.Close
            
        Else
            Wkb.Save
            'We save this workbook, but we don't close it _
                because we will quit Excel at the end, _
                Closing here leaves the app running, but no books
            If Not Wkb Is ThisWorkbook Then Wkb.Close
        End If
NextWorkbook:
    Next


    Application.ScreenUpdating = True
    
    'Quit Excel
'   application.Quit        '   <<<<<<<<<<<<<<< commented out
End Sub