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

[Melissa71]

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?

 

[John_w]

Try something like this:

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.

 

[Melissa71]

Try something like this:

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....

 

[John_w]

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'.