Results 1 to 9 of 9

Saving a .xlsm file as .xlsx using VBA

This is a discussion on Saving a .xlsm file as .xlsx using VBA within the Excel Questions forums, part of the Question Forums category; I have a macro (posted below) that saves the current workbook with a new filename. I would like it to ...

  1. #1
    New Member
    Join Date
    Dec 2010
    Posts
    3

    Default Saving a .xlsm file as .xlsx using VBA

    I have a macro (posted below) that saves the current workbook with a new filename. I would like it to make a copy and also remove the macros and save it as a plain Excel 2007 workbook. The workbook is used by a grounds maintenance company to track work and services done on different routes, each with its list of locations.

    Here is the macro:

    Sub fileSave()
    '
    Dim newFileName As String, originalFileName As String, fileSaveName As String, fileNamePathSaved As String, fileNameSaved As String
    Dim response As VbMsgBoxResult, currentRoute As String
    '
    ThisWorkbook.RefreshAll
    ActiveWorkbook.Save ' save the current workbook before messing with it
    currentRoute = Worksheets("Control").[currentRoute] ' grab the current route name
    newFileName = currentRoute & " (" & Format(Now(), "yyyy-mm-dd at hh.mm") & ").xlsm" ' generates a new filename with the current route #, date and time.
    Application.DisplayAlerts = False ' turns off alerts and messages
    ' Save file name and path into a variable
    originalFileName = ActiveWorkbook.FullName ' grabs the current filename and path

    ' Default directory would be the current directory. Users however will have the ability to change where to save the file if need be.
    fileSaveName = Application.GetSaveAsFilename(InitialFileName:=newFileName) ' prompts for a save location with the recommended filename and original path
    If fileSaveName = "False" Then ' exits if cancel is clicked
    Exit Sub
    End If
    ' Remove the Control Page and save the file
    Worksheets("Control").Delete ' removes the control page (it is not needed for the data file saved)
    ActiveWorkbook.SaveAs Filename:=fileSaveName 'saves the file
    fileNamePathSaved = ActiveWorkbook.FullName ' grabs the name and path of the saved file
    fileNameSaved = ActiveWorkbook.Name ' grabs the name of the saved file
    MsgBox "Your Route file has been successfully created at: " & vbCr & vbCr & fileNamePathSaved ' informs user the file has been saved

    ' Go back to Excel format after Route file has been created and saved
    Workbooks.Open Filename:=originalFileName 'reopens the original workbook file
    Application.DisplayAlerts = True ' turns the alerts and messages back on
    Worksheets("Control").routeSpinner.Visible = True ' displays the route spinner hidden after the route started
    'provide an opportinity to clear the incident report flag
    If incidentFiled = True Then response = MsgBox("Do you want to clear the Incident Report?", vbInformation + vbOKCancel, "Incident Report Form")
    If response = vbOK Then incidentFiled = False
    'close the newly made file

    Workbooks(fileNameSaved).Close False ' sub terminates at this point
    '
    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    8,105

    Default Re: Saving a .xlsm file as .xlsx using VBA

    Hello and Welcome to the Board,

    You can use this syntax to save as an xl2007 workbook:

    Code:
    ActiveWorkbook.SaveAs Filename:="myFile.xlsx",  FileFormat:=xlOpenXMLWorkbook
    You can find more options in the Excel Help (Excel Developer Reference) under Workbook.SaveAs Method
    Using Excel 2016

  3. #3
    New Member
    Join Date
    Dec 2010
    Posts
    3

    Default Re: Saving a .xlsm file as .xlsx using VBA

    Thanks! I was having issues doing it that way as it would give me runtime errors with the macros being in the original workbook. Eventually I did get it to work by stripping the VBA Project from the copy and then saving it, but this required extra security permissions so I gave up that approach as my clients wanted an easily distributable workbook (no setup). I then worked out how to save worksheets to a new workbook. This gave me more control over what was saved and resulted in smaller output files.

  4. #4
    New Member
    Join Date
    Jul 2013
    Posts
    3

    Default Re: Saving a .xlsm file as .xlsx using VBA

    Quote Originally Posted by JS411 View Post
    Hello and Welcome to the Board,

    You can use this syntax to save as an xl2007 workbook:

    Code:
    ActiveWorkbook.SaveAs Filename:="myFile.xlsx",  FileFormat:=xlOpenXMLWorkbook
    You can find more options in the Excel Help (Excel Developer Reference) under Workbook.SaveAs Method

    This does not suppress then "features cannot be saved in macro-free workbooks" error. Is there another paramater I could pass to the saveAs method which will strip the macros off of the file to be save-as'd?

  5. #5
    New Member
    Join Date
    Jul 2013
    Posts
    8

    Default Re: Saving a .xlsm file as .xlsx using VBA

    You can try this:
    Code:
    Sub SaveAsName()
    Dim save_as As Variant
    Dim file_name As String
    Dim ProgramName As String
    file_name = ProgramName
        ' Get the file name.
        save_as = Application.GetSaveAsFilename(file_name, _
            FileFilter:="Excel Files,*.xlsx,All Files,*.*")
        ' See if the user canceled.
        If save_as = False Then Exit Sub
        ' Save the file with the new name.
        Application.DisplayAlerts = False
        If LCase$(Right$(save_as, 4)) <> ".xlsx" Then
            file_name = save_as & ".xlsx"
        End If
        ActiveWorkbook.SaveAs Filename:=save_as, FileFormat:= _
            xlOpenXMLWorkbook, CreateBackup:=False
        
    End Sub

  6. #6
    New Member
    Join Date
    Jul 2013
    Posts
    3

    Default Re: Saving a .xlsm file as .xlsx using VBA


    Thanks for your reply. I ended up solving my problem with a solution I read somewhere else, where you copy the worksheets first. Not sure how it works but it certainly does.

    simple:


    Code:
    sub saveAsXlsx1
    Worksheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy
    ActiveWorkbook.SaveAs fileName:="myFileName.xlsx"
    end sub





    or, more robustly:

    Code:
    sub saveAsXlsx
        Dim mySheetList() As String
        ReDim mySheetList(0 To (ThisWorkbook.Sheets.Count) - 1)
        Dim a As Integer
        a = 0
        For Each ws In ActiveWorkbook.Worksheets
            mySheetList(a) = ws.Name
            a = a + 1
        Next ws
    
        'actually save
        Worksheets(mySheetList).Copy
        ActiveWorkbook.SaveAs fileName:=flenme 'default ext

  7. #7
    New Member
    Join Date
    Apr 2016
    Posts
    1

    Default Re: Saving a .xlsm file as .xlsx using VBA

    Quote Originally Posted by Jerry Sullivan View Post
    Hello and Welcome to the Board,

    You can use this syntax to save as an xl2007 workbook:

    Code:
    ActiveWorkbook.SaveAs Filename:="myFile.xlsx",  FileFormat:=xlOpenXMLWorkbook
    You can find more options in the Excel Help (Excel Developer Reference) under Workbook.SaveAs Method
    This won't work. This code will give you run-time 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."

  8. #8
    New Member
    Join Date
    Jun 2016
    Posts
    2

    Exclamation Re: Saving a .xlsm file as .xlsx using VBA

    I had to help out here. To suppress the stupid microsoft error message you simply add this line of code before the save line of code. Works perfectly.

    Add Application.DisplayAlerts = False

  9. #9
    New Member
    Join Date
    Jun 2016
    Posts
    2

    Default Re: Saving a .xlsm file as .xlsx using VBA

    Quote Originally Posted by mikestilly View Post
    I had to help out here. To suppress the stupid microsoft error message you simply add this line of code before the save line of code. Works perfectly.

    Add Application.DisplayAlerts = False
    Unfortunately this website doesnt let you edit your posts. Disregard the add it should be the below. I didnt want to confuse anyone.

    Application.DisplayAlerts = False

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com