Worksheet Tab Being Renamed During Save Process & Unable to open newly created .xlsx file (invalid file extension?)

[Ark68]

Please consider this code ...

       With wb_classcsv
            .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Stats"
            Set ws_stats = .Worksheets("Stats")
            With ws_stats
                .Range("A3") = "Date:  "
                .Range("B3") = Format(usr_date, "dd-mmm-yy")
                .Range("C3") = Format(usr_date, "00000")
                .Range("A4") = "Created:  "
                .Range("B4") = Format(Now, "dd-mmm-yy")
                .Range("C4") = Format(Now, "h:mm:ss am/pm")
                .Cells.EntireColumn.AutoFit
            End With
            fname = Format(usr_date, "mmm-dd (ddd)") & " Data.xlsx"
            stname = "U:\PWS\Parks\Parks Operations\Sports\WATSOP19\DATA\" & fname
            Application.DisplayAlerts = False
            .SaveAs Filename:=stname
            Application.DisplayAlerts = True
        End With

'wb_classcsv' is opened as a .csv file.
WIth the lines in blue, I create a new worksheet in workbook 'wb_classcsv' and name it 'stats' and set ws_stats to to it.
The red line saves the file with a name of (example) "Jun-24 (Mon).xlsx".

I am running into two problems...

1) The tab I named 'stats' is changing to 'Jun-24 (Mon)' with the saving of this file for some reason I cannot explain. This will cause problems for me down the road, I cannot allow this tab to be renamed to anything other than 'stats'

2) I cannot open this newly created file. I get the error "Excel cannot open the file 'Jun-24 (Mon).xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file."

Please, can anyone help?

 

[sadboy309]

Please consider this code ...

       With wb_classcsv
            .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Stats"
            Set ws_stats = .Worksheets("Stats")
            With ws_stats
                .Range("A3") = "Date:  "
                .Range("B3") = Format(usr_date, "dd-mmm-yy")
                .Range("C3") = Format(usr_date, "00000")
                .Range("A4") = "Created:  "
                .Range("B4") = Format(Now, "dd-mmm-yy")
                .Range("C4") = Format(Now, "h:mm:ss am/pm")
                .Cells.EntireColumn.AutoFit
            End With
            fname = Format(usr_date, "mmm-dd (ddd)") & " Data.xlsx"
            stname = "U:\PWS\Parks\Parks Operations\Sports\WATSOP19\DATA\" & fname
            Application.DisplayAlerts = False
            .SaveAs Filename:=stname
            Application.DisplayAlerts = True
        End With

Do you forget "Data" for name of file? You check-in it so!
Filename can 'Jun-24 (Mon) Data.xlsx'

 

[RoryA]

You need to specify the fileformat argument when saving as a different format.

 

[Ark68]

Thank you Rory!

https://docs.microsoft.com/en-us/office/vba/api/excel.xlfileformat

 

[Ark68]

Do you forget "Data" for name of file? You check-in it so!
Filename can 'Jun-24 (Mon) Data.xlsx'

Thank you sadboy, that was an error in my post. I failed to include the 'data' in my example, but thank you for pointing out the need to be accurate when posting!