CODEWITHSUNDEEP
vbaexcelsave
anonymous
anonymous

Reputation: 576

VBA SaveAs saving as File type instead of .xlsm

When a user opens up this workbook, I want to force them to save the file as a new file immediately. The dialog box opens up but it will only let you save it as "All Files".

enter image description here

Dim Workbook_Orig As Variant

    Workbook_Orig = Application.GetSaveAsFilename

    If Workbook_Orig <> False Then
        ActiveWorkbook.SaveAs _
        Filename:="File Name", _
        FileFormat:=52
    End If

In place of the "52" I've tried "xlOpenXMLWorkbookMacroEnabled" but that made no difference.

Can you not SaveAs immediately? Do you have to make a change to the file or something?

Any help is greatly appreciated.

Upvotes: 1

Views: 4821

Answers (3)

jellz77
jellz77

Reputation: 354

Why not present the user with an InputBox? Have them enter the new file name and have the macro perform the save as.

Something like:

Sub saveAs()
    userAnswer = InputBox("Please enter filename")
    ActiveWorkbook.saveAs Filename:= _
    "C:\" & userAnswer & ".xls", FileFormat:=xlExcel8, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
End Sub

EDIT

Sub saveAs()
    userAnswer = InputBox("Please enter filename")
    userDirectory = InputBox("Please enter directory to save file")
    ActiveWorkbook.saveAs Filename:= _
    userDirectory & "\" & userAnswer & ".xls", FileFormat:=xlExcel8, _
    Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
    CreateBackup:=False
End Sub

Upvotes: 1

gtwebb
gtwebb

Reputation: 3011

Changing the FileFormat only matters if they don't select anything in the dialogue box (since that is the only time workbook_orig = False)

Try specifying the file filter parameter

Workbook_Orig = Application.GetSaveAsFilename(fileFilter:="Excel Macro-Enabled Workbook (*.xlsm),*.xlsm")

In your GetSaveAsFileName instruction.

Upvotes: 3

poppertech
poppertech

Reputation: 1294

Try specifying a file filter:

Workbook_Orig = Application.GetSaveAsFilename( _
        fileFilter:="XLSM Files (*.xlsm), *.xlsm")

Upvotes: 3

Related Questions