Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    90
    Thanks
    15
    Thanked 5 Times in 5 Posts

    Save file as .xlsx in VBA Code

    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
    Maria
    Simmo7
    Victoria, Australia

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You have to specify the file format:

    Code:
    ActiveWorkbook.SaveAs Filename:=fName, FileFormat:=xlOpenXMLWorkbook
    Regards,
    Rory

    Microsoft MVP - Excel

  3. The Following User Says Thank You to rory For This Useful Post:

    simmo7 (2015-01-06)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    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

  5. The Following User Says Thank You to zeddy For This Useful Post:

    simmo7 (2015-01-06)

  6. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •