Cannot save file in Excel after using filesavename in VBA

Question

So i have this VBA at work, that I made a while ago. It used to work perfectly, but as of today it will not save my file after it opens the Save as window. It just goes to the MsgBox ive given it.

At first the problem was that LDate = Date somehow started returning the date with a forward slash. Ive fixed this by adding a format for LDate. But the bigger problem remains. No matter what i do, what code i remove or add, what name i write manually, the file wont save in any folder i give it.

Sub Export()
'
' Export Macro
'
    ' copy range from work workbook, create a new workbook and paste selection
    Sheets("NewTemplate").Range("A1:M29").Copy
    Workbooks.Add
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Paste

    ' rename active sheet and active workbook
    ActiveSheet.Name = "Create"
    ActiveWorkbook.Windows(1).Caption = "Newly Generated Table"
    Range("A1").Select
     Application.CutCopyMode = False

    ' open Save As window, set file save name to custom prefix + date
    Dim IntialName As String
    Dim fileSaveName As Variant
    InitialName = "Import_Feature_Values_"
    Dim LDate As String
    LDate = Date
    LDate = Format(Now, "dd_mm_yyyy")
    fileSaveName = Application.GetSaveAsFilename(FileFilter:= _
                                                 "Microsoft Excel Macro- Enabled Worksheet (*.xlsm), *.xlsm", InitialFileName:=InitialName & LDate)

    'error box if filesavename fails
    If fileSaveName <> False Then
    MsgBox "Failed to Save as " & fileSaveName
    End If
'
End Sub

GetSaveAsFilename only returns a path & filename it does not save. You will need to use something like ThisWorkbook.SaveAs — Pᴇʜ
– Pᴇʜ, Commented Mar 28, 2018 at 11:45
I wanted to leave the option for the person to choose a directory before saving, so the actual clicking of "Save" is manual. Also, I just tried ThisWorkbook.SaveAs and it still did not want to save the file. — Simo Todorov
– Simo Todorov, Commented Mar 28, 2018 at 11:47
You've written Dim IntialName, but tried to use InitialName - that extra i makes a difference. You may want to add Option Explicit to the top of the Module (turn on "Require Variable Declaration" in Tools > Options to do this automatically in future) — Chronocidal
– Chronocidal, Commented Mar 28, 2018 at 12:11

Pᴇʜ · Accepted Answer · 2018-03-28 12:17:38Z

3

GetSaveAsFilename does not save a file.
It only does what the function name says: Get a SaveAs filename from the dialog box.

So your variable fileSaveName just contains a file path and file name that was chosen in the dialog box, and you still need to save the file yourself.

Fore example to save the current workbook (that workbook code is running at) with the chosen filename:

ThisWorkbook.SaveAs Filename:=fileSaveName

or for the active workbook (that workbook that is on top):

ActiveWorkbook.SaveAs Filename:=fileSaveName

For macro enabled files define a file format according to XlFileFormat-Enumeration:

ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlOpenXMLWorkbookMacroEnabled

edited Mar 28, 2018 at 12:17

answered Mar 28, 2018 at 11:49

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Simo Todorov Over a year ago

Ok, so i added ActiveWorkbook.SaveAs Filename:=fileSaveName but now it give me an error 1004 what am i doing wrong? I placed this command after the filesavename line and before the If statement for the msgbox

Simo Todorov Over a year ago

Runtime error 1004 this extension can not be used with the selected file type. Change the file extension in the file name text box or select a different file type by changing the Save as type.

Pᴇʜ Over a year ago

you need to specify the fileformat, see my edit (I didn't see it was a macro enabled file)

Chronocidal Over a year ago

Running the code I also get Error 1004 at the Range("A1").Select lines - this seems to be because Range is not qualified. ActiveSheet.Range("A1").Select will work, but it would be better to store your created workbook in a Workbook object, and avoid using ActiveSheet or Select wherever possible

Chronocidal Over a year ago

fileSavename contains the location you are saving the file, e.g. "C:\Book.xlsm" - this will evaluate to True, i.e. Not False. You might be able to use If Not ActiveWorkbook.Saved Then to see if the file thinks it's saved?

|

Collectives™ on Stack Overflow

Cannot save file in Excel after using filesavename in VBA

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related