Macro to Save File based on Month and year on sheet "Summary" U1

[howard]

I have a workbook called "BR1 PL Month End Recon Aug 2022"

I have the curreent month end on Sheet "Summary" U1 for eg Sept-2022

I have set up a macro to remove the previous month and year and replace it with the current month and Year on sheet "Summary" U1

Howver when running the macro, I get a run time error "Microsoft Excel cannot access the file...... "

The code below is highlighted

 ThisWorkbook.SaveAs Left(strFullNm, Len(strFullNm) - 5), 52

See my code below

 Sub SaveWorkbookAsNewName()
    Dim strNameToSave As String
    Dim strFullNm   As String
    Dim lPos        As Long
    Dim s           As String

    strNameToSave = ThisWorkbook.Worksheets("summary").Range("U1").Value
    strFullNm = ThisWorkbook.FullName
    
    If InStr(1, strFullNm, strNameToSave, vbTextCompare) = 0 Then
        lPos = Len(strFullNm) - Len(strNameToSave) - 4
        Mid(strFullNm, lPos, Len(strNameToSave)) = strNameToSave

        ThisWorkbook.SaveAs Left(strFullNm, Len(strFullNm) - 5), 52
    End If
End Sub

It would be appreciated if someone could amend my code

 

[Anthony47]

Try using .text and not .value:

strNameToSave = ThisWorkbook.Worksheets("summary").Range("U1").Text

Or, which should be my preferred choice:

    strNameToSave = Format(ThisWorkbook.Worksheets("summary").Range("U1").Value, "mmm-yyyy")

Consider also using "yyyy-mm" as the format, because that would allow you to order the files by their file name

 

[howard]

I prefer using

 strNameToSave = Format(ThisWorkbook.Worksheets("summary").Range("U1").Value, "mmm-yyyy")

File savees with month and year, but file saved as a text file

I need it to save as an .xls file

 

[Anthony47]

ThisWorkbook.SaveAs Left(strFullNm, Len(strFullNm) - 5), 52

Using FileFormat=52 you should create an "xlsm" file, not a text file; if you need an "xls" format, then check the documentation to see which is the XlFileFormat enumeration to be used: XlFileFormat enumeration (Excel)

 

[howard]

The name of the current file is

 BR1 Sales_Ledger RECON(9.2.3) Aug 2022.xlsm

Cell U1 on Sheet Summary" is Sep-2022

I need the macro to change file name to

 BR1 Sales_Ledger RECON(9.2.3) Sep 2022.xlsm


However, when running the macro, it saves it as BR1 Sales_Ledger RECON(9.2.3) ASep-202


Kindly amend code accordingly

 

[Anthony47]

    strNameToSave = Format(ThisWorkbook.Worksheets("summary").Range("U1").Value, "mmm-yyyy")
    strFullNm = ThisWorkbook.FullName
  
    If InStr(1, strFullNm, strNameToSave, vbTextCompare) = 0 Then
        lPos = Len(strFullNm) - Len(strNameToSave) - 4
        Mid(strFullNm, lPos, Len(strNameToSave)) = strNameToSave
        ThisWorkbook.SaveAs Left(strFullNm, Len(strFullNm) - 5), 52
    End If

When I execute the above code, with the starting name = BR1 Sales_Ledger RECON(9.2.3) Sep 2022.xlsm, I got a file named BR1 Sales_Ledger RECON(9.2.3) ott-2022.xlsm

If you work with Office 2021 on a Windows platform and the macro is inserted into the vba project of the file you are reworking then I don't see why you get a different result; you need to excecute the macro step-by-step and check the intermediate results to understand what get wrong

 

[howard]

Many thanks for the help

I created .xlsm in Cell V1 on sheet "Summary" and joined cell U1 with cell V1 and then run the macro and it worked