VBA TO SAVE AS.... IF FILE NAME CONTAINS

Melissa71

New Member
Joined
Jul 23, 2018
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi all
I am trying to figure out how to write the VBA coding to save multiple workbooks each day based on their file name. I save several system reports I download daily into Excel and save to a specific folder with a specific name that all my reporting macros use throughout the day

I have to download approx 10-15 system reports into excel then manually save each one before I can run all my other reporting. I am looking to write a macro that will loop through all the open files and save them into a specific name and folder that will remain static based on if their file name contains specific text. The system report names change daily (includes date).

an example:
System report named (will be variable with date): US - APM DATA REPORT (07-27-2021 06_05 PST).xlsx

I want to be able to loop through all open workbooks and look for the workbook that contains "APM DATA REPORT" and once it finds that workbook it will save it under a specific folder as; "US - APM DATA REPORT - 10884.xlsx"

I then want to loop through all open workbooks and look for the next keywords for another workbook and save that under a specific file name.

File name I am looking for will be variable with date, save as file name will always be the same.

Can anyone help?
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Try something like this:
VBA Code:
Public Sub Save_Current_Workbooks()

    Dim wbWindow As Window
    Dim wb As Workbook
    
    For Each wbWindow In Windows
        Set wb = wbWindow.Parent
        If InStr(wb.Name, "APM DATA REPORT") Then
            Save_Workbook wb, "C:\folder\path\US - APM DATA REPORT - 10884.xlsx"
        ElseIf InStr(wb.Name, "ANOTHER FILE NAME") Then
            Save_Workbook wb, "C:\folder\path\NEW FILE NAME.xlsx"
        End If
    Next

End Sub


Private Sub Save_Workbook(wb As Workbook, newFileName As String)
    Application.DisplayAlerts = False
    wb.SaveAs fileName:=newFileName, FileFormat:=xlOpenXMLWorkbook
    wb.Close SaveChanges:=False
    Application.DisplayAlerts = True
End Sub
Delete the DisplayAlerts lines if you want Excel to display a warning if the new files already exist. Delete the Close line if you want to keep them open.
 
Upvote 0
Try something like this:
VBA Code:
Public Sub Save_Current_Workbooks()

    Dim wbWindow As Window
    Dim wb As Workbook
   
    For Each wbWindow In Windows
        Set wb = wbWindow.Parent
        If InStr(wb.Name, "APM DATA REPORT") Then
            Save_Workbook wb, "C:\folder\path\US - APM DATA REPORT - 10884.xlsx"
        ElseIf InStr(wb.Name, "ANOTHER FILE NAME") Then
            Save_Workbook wb, "C:\folder\path\NEW FILE NAME.xlsx"
        End If
    Next

End Sub


Private Sub Save_Workbook(wb As Workbook, newFileName As String)
    Application.DisplayAlerts = False
    wb.SaveAs fileName:=newFileName, FileFormat:=xlOpenXMLWorkbook
    wb.Close SaveChanges:=False
    Application.DisplayAlerts = True
End Sub
Delete the DisplayAlerts lines if you want Excel to display a warning if the new files already exist. Delete the Close line if you want to keep them open.
Thanks John
I haven't tried this yet but does it take into account that the file name it's looking for will be variable day to day? I think If InStr means that but just making sure.

I did eventually come up with the coding - here is mine - I am just waiting to find some bug with it.

Application.DisplayAlerts = False
'
Dim name As String
For Each WB In Application.Workbooks

If WB.name Like "*APM DATA REPORT*" Then
On Error Resume Next
WB.Activate
ActiveWorkbook.SaveAs Filename:="\\MASTER REPORT FOLDER for Macros\APM DATA REPORT - 10884.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End If

If WB.name Like "*WEEKLY WHOH*" Then
On Error Resume Next
WB.Activate
ActiveWorkbook.SaveAs Filename:="\\MASTER REPORT FOLDER for Macros\WEEKLY WHOH-PACK 9650.xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

End If

etc, etc....
 
Upvote 0
Solution
I haven't tried this yet but does it take into account that the file name it's looking for will be variable day to day? I think If InStr means that but just making sure.
Yes it does. If InStr(wb.Name, "APM DATA REPORT") Then says 'if the first string contains the second string then'.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top