Macro to Save workbook by saving with month and year in Cell E6

[howard]

I have the month end Month and Year in Cell E6

I have the file name below

David James Comm.Oct 2021 New deals Ver 1.1.xlsm

I would like a macro to save the file by first removing the month and year after the . and before "New" and to include the month and year in Cell E6 after the .

If E6 contains Nov 2021 then the new file should save as David James Comm.Nov 2021 New deals Ver 1.1.xlsm


Your assistance is most appreciated

 

[Zot]

You can use the function below to rename the whole string. Usage GetDateFromString(<filename>)
David James Comm.Oct 2021 New deals Ver 1.1.xlsm
to
David James Comm.Nov 2021 New deals Ver 1.1.xlsm

I'm assuming that the range E6 date is in string format

Public Function GetDateFromString(inputString As String) As String

Dim RegEx As Object

Set RegEx = CreateObject("VBScript.RegExp")
GetDateFromString = ""

With RegEx
    .Global = True
    .Pattern = "\.[A-Z][a-z]{2}\s\d{4}"
    GetDateFromString = .Replace(inputString, "." & Range("E6"))
End With

End Function

 

[howard]

Thanks for the help

How do I use the formula to amend file name with new month and year in E6 ?

I have used this part of formula below but need to be able to use it to show new file name and can then write code to save file with is name

 =GetDateFromString(E6)

 

[Zot]

The E6 contains only Nov 2021, right? I was imagining that you have the save file macro and use the function to write as new name.

Actually, how is your operation? You said you have a file by that name. So, how would you run your macro? The macro is in one other workbook?

 

[howard]

E6 only contains Nov 2021

I am currently saving the file manually. I would like a macro to save the file each month by using the month and year in Cell C6
If Current File is :
David James Comm.Oct 2021 New deals Ver 1.1.xlsm

When running Macro it must change to name below

David James Comm.Nov 2021 New deals Ver 1.1.xlsm

 

[Zot]

The macro is in the same David James Comm.Oct 2021 New deals Ver 1.1.xlsm file?

 

[howard]

That is correct

 

[Zot]

Something like this.

Sub Test()

Dim oldFName As String, newFName As String

With ActiveWorkbook
    oldFName = .FullName
    newFName = GetDateFromString(oldFName)
    .SaveAs Filename:=newFName
End With

End Sub

 

[howard]

Thanks for the code. When I activate the macro, it saves as

David James Comm.Commission % New deals Ver 1.1.xlsm

 

[Zot]

Thanks for the code. When I activate the macro, it saves as

David James Comm.Commission % New deals Ver 1.1.xlsm

This has something to do with what you have in range E6. I was expecting you have Nov 2021 in string format.

One thing to note that since the code did not specify the sheet, the range E6 will be the value in E6 of active sheet when the code run. The best way is to define the sheet. Normally you would not want to define in UDF because it will restrict usage to specific workbook. So, I'll pass the sheet parameter to function.

Modified code

Sub Test()

Dim oldFName As String, newFName As String
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ActiveWorkbook
Set ws = wb.Sheets("Sheet1")  ' Modify if necessary

Application.ScreenUpdating = False

With ActiveWorkbook
    oldFName = .FullName
    newFName = GetDateFromString(ws, oldFName)
    .SaveAs Filename:=newFName
End With

End Sub

Public Function GetDateFromString(ws As Worksheet, inputString As String) As String

Dim ws As Worksheet
Dim RegEx As Object

Set RegEx = CreateObject("VBScript.RegExp")
GetDateFromString = ""

With RegEx
    .Global = True
    .Pattern = "\.[A-Z][a-z]{2}\s\d{4}"
    GetDateFromString = .Replace(inputString, "." & ws.Range("E6"))
End With

End Function