File Save Macro

[ward9273]

Can you assist?
I am in search of a macro to save all Excel workbooks in the same folder with a filename that is a combination of cell A1 and B3. Windows 10 and Office 365. Thank you for sharing your expertise and time.

 

[John_w]

Not totally clear what you're asking for, but see if this macro works for you. It renames all the workbooks (*.xlsx) in the selected folder using the values in cells A1 and B3 of the first worksheet in each workbook.

Public Sub Rename_Workbooks()

    Dim folderPath As String
    Dim wbFileName As String, wbNewFileName As String
    Dim targetWb As Workbook
                
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Folder"
        If .Show Then
            folderPath = .SelectedItems(1) & "\"
        Else
            Exit Sub
        End If
    End With
        
    Application.ScreenUpdating = False
        
    wbFileName = Dir(folderPath & "*.xlsx")
    While wbFileName <> vbNullString
    
        Set targetWb = Workbooks.Open(folderPath & wbFileName, ReadOnly:=True)
        wbNewFileName = targetWb.Worksheets(1).Range("A1").Value & targetWb.Worksheets(1).Range("B3").Value & Mid(wbFileName, InStrRev(wbFileName, "."))
        targetWb.Close SaveChanges:=False
        
        Name folderPath & wbFileName As folderPath & wbNewFileName
        
        DoEvents
        wbFileName = Dir
    Wend
    
    Application.ScreenUpdating = True
    
    MsgBox "Finished"
    
End Sub

 

[ward9273]

John, Thank you for your time and expertise! I am pretty sure I can take it from here.

 

[ward9273]

John, Thank you for your time and expertise! I am pretty sure I can take it from here.

John, It failed to run? Can you assist. I am trying to keep it simple. As you already know, I am a newbie.
I put the date and time in "a1" and the partial file name in B3. When I am done with the WB i want to combine a1 and b3 into a time and date stamped filename. Ultimately, I want to store all of my excel wb in the same folder.

 

[John_w]

The code to build a file name using the date and time in A1 depends on whether A1 is formatted to the required date and time format (noting that characters like ":" and "/" aren't allowed in file names) or is unformattted.

Change the following line:

        wbNewFileName = targetWb.Worksheets(1).Range("A1").Value & targetWb.Worksheets(1).Range("B3").Value & Mid(wbFileName, InStrRev(wbFileName, "."))

To either:

        wbNewFileName = targetWb.Worksheets(1).Range("A1").Text & " " & targetWb.Worksheets(1).Range("B3").Value & Mid(wbFileName, InStrRev(wbFileName, "."))

if A1 is formatted to the required date and time format.

Or:

        wbNewFileName = Format(targetWb.Worksheets(1).Range("A1").Value, "yyyymmdd hhmmss") & " " & targetWb.Worksheets(1).Range("B3").Value & Mid(wbFileName, InStrRev(wbFileName, "."))

if A1 is unformatted - change the "yyyymmdd hhmmss" to the date and time format you want.