Save file as .xlsx in VBA Code

Topic

New Reply

Hi,

How would I change the following code to ensure that the file saves as .xlsx format? (Excel 2013). I had changed the code after the Do command to include the .xlsx extension, but Excel still sees it as a .xls format.

Code:

Sub Save_OutputFile()
restart_Loop:
currDate = Mid(Now(), 7, 4) & “-” & Mid(Now(), 4, 2) & “-” & Left(Now(), 2)
FileDate = InputBox(“”, “Report Date”, currDate)
Do
    fName = Application.GetSaveAsFilename(DefaultOutputPath & Left(origReport, Len(origReport) – 4) & “_” & currDate & “.xlsx”)
    counter = counter + 1
    If counter >= 3 Then
        noSave = MsgBox(“You have chosen to exit without saving” & vbCr & vbCr & “Please confirm this selection”, vbYesNoCancel)
        If noSave = 2 Then
            counter = 0
            GoTo restart_Loop
        ElseIf noSave = 7 Then
            counter = 2
            GoTo restart_Loop
        ElseIf noSave = 6 Then
            MsgBox (“The file has not been saved”)
            Exit Sub
        End If
        Exit Sub
    End If
Loop Until fName  False
ActiveWorkbook.SaveAs Filename:=fName

End Sub

I have always worked around this, but now the code is being used by others and I don’t want them doing any manual workarounds.

Regards,
Maria

Reply | Quote

Replies

You have to specify the file format:

Code:

ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbook

Reply | Quote

Hi Maria

..and instead of using..

Code:

currDate = Mid(Now(), 7, 4) & "-" & Mid(Now(), 4, 2) & "-" & Left(Now(), 2)

why not use this instead:

Code:

currDate = Format(Now(), "yyyy-mm-dd")

..it’s shorter (and easier to follow??)
..and perhaps use the vba constants instead of values (to make it easier to follow):

The return values for MsgBox are as follows:

Constant

Value

Description

vbOK : 1 signifies that the OK button was pressed
vbCancel : 2 signifies that the Cancel button was pressed
vbAbort : 3 signifies that the Abort button was pressed
vbRetry : 4 signifies that the Retry button was pressed
vbIgnore : 5 signifies that the Ignore button was pressed
vbYes : 6 signifies that the Yes button was pressed
vbNo : 7 signifies that the No button was pressed

zeddy

Reply | Quote

Here is one I have used to saveas .XLS
Sub SaveAsXLS()
FNAME = ActiveWorkbook.Name
FNAME = Left(FNAME, Len(FNAME) – 4)
ActiveWorkbook.SaveAs Filename:=”C:PERSONAL” & FNAME & “.xls”, _
FileFormat:=xlNormal, Password:=””, WriteResPassword:=””, _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub

Reply | Quote