Workbook SaveAs private function to include date & time stamp - saved file won't open

dougmarkham

Active Member
Joined
Jul 19, 2016
Messages
252
Office Version
  1. 365
Platform
  1. Windows
Hi Folks,

I discovered some VBA to be able to save a workbook with a custom filename that contains date and time stamp.
My goal is just to save the file as *.xlsx.

The code below works in regards to saving the workbook with the bespoke filename; however, when you try and open the workbook, the following error message results:

Excel cannot open the file 'order-details-export_200190517-1627 Orders Importer.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and the file exension matches the format of the file'

Code:
[B]Public Sub SaveFileAs()[/B]
    Dim TodaysDate As String
    Dim RawDataFileName As Variant
    Dim SaveFileName As Variant
    Dim SavePath As Variant
    Dim SavePathAndFileName As Variant
    
    TodaysDate = GetTodaysDate
    
    SaveFileName = "order-details-export_" & TodaysDate & " Orders Importer"
        
    SavePath = "C:\Users\username\Desktop"
    
    SavePathAndFileName = SavePath & "\" & SaveFileName
 
    SaveWorkbookAs (SavePathAndFileName)

[B]End Sub[/B] [COLOR=#006400]'SaveFileAs
[/COLOR]

 
[B]Private Function GetTodaysDate()[/B]


    GetTodaysDate = Format(Now(), "yyyymmdd-hhmm")


[B]End Function[/B] [COLOR=#006400]'GetTodaysDate[/COLOR]




[B]Private Sub SaveWorkbookAs(SaveFileName As String)[/B]
    
    Dim FileExtension As String
    
    Dim SaveFile As String


    FileExtension = "Excel Workbook (*.xlsx), *.xlsx," & _
               "All files (*.*), *.*"
 
    SaveFile = Application.GetSaveAsFilename( _
                InitialFileName:=SaveFileName, _
                FileFilter:=FileExtension)
                
    MsgBox "Save File is called " & SaveFile
                
    ActiveWorkbook.SaveAs SaveFile, xlWorkbookNormal
        
[B]End Sub[/B] [COLOR=#006400]'SaveWorkbookAs[/COLOR]

Would anybody be willing to regarding what is wrong with the last sub outlining the extension that would cause this error?

Kind regards,

Doug.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Doug

Try changing xlWorkbookNormal to xlOpenXMLWorkbook.
 
Last edited:
Upvote 0
Doug

Try changing xlWorkbookNormal to xlOpenXMLWorkbook.

Hi Norie,

That has solved the issue!
Thank you for helping me :)

I guess the other format---xlWorkbookNormal---can be read without opening?

Kind regards,

Doug.
 
Upvote 0

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top