CODEWITHSUNDEEP
excelexcel-2016vba
Simo Todorov
Simo Todorov

Reputation: 36

Cannot save file in Excel after using filesavename in VBA

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

Upvotes: 0

Views: 3514

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

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

Upvotes: 3

Related Questions