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:


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

If you're a fan of strategy and precision, gold miner (https://goldmineronline.com) 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