VBA "Save As", no error but file does not save

[theDrew]

Hi all,

I have written the following simple macro to import some data into a worksheet and then prompt the user to save the file in Excel 2003 format (the system to which we will upload this data does not accept formats later than 2003). The template is in "*.xlsm" format.

The code executes without error, but when the user hits the "Save" button in the "Save As" dialog box, nothing happens. The "Save As" box closes, but no file is saved.

Help?

Private Sub cmdImportData_Click()

Dim sFName As String

'On Error Resume Next

   PrepData
   CopyData
   FormatColumns

'prompt the user to save the file in "*.xls" format
sFName = Application.GetSaveAsFilename("upload", "Excel files (*.xls), *.xls")

End Sub

 

[xenou]

GetSaveAsFileName gets you a file name but doesn't save anything. You have to write that code too (note that you should handle cases where the user hits cancel, which might happen if they get confused and don't enter a file name):

[COLOR="Navy"]Sub[/COLOR] foo()
[COLOR="Navy"]Dim[/COLOR] sFName [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]

    sFName = Application.GetSaveAsFilename("upload", "Excel files (*.xls), *.xls")
    
    [COLOR="Navy"]If[/COLOR] sFName <> "False" [COLOR="Navy"]Then[/COLOR]
        ActiveWorkbook.SaveAs sFName, 56
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="SeaGreen"]'//Do something - user cancelled[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

[COLOR="Navy"]End Sub[/COLOR]

Possibly a simpler way to go would be to use the same file name with an .xls extension and file type? Or assign the file name automatically if it has a standard name that should be used?

 

[Richard Schollar]

GetSaveAsFilename only returns a string - it doesn't actually execute the SaveAs command. So you have the sFName, then you just need to execute SaveAs:

If sFName<>"False" Then ActiveWorkbook.SaveAs sFName

 

[theDrew]

Awesome! That worked. Thanks so much!