Results 1 to 6 of 6

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 Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    Location
    San Diego, California
    Posts
    7,092

    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 2013

  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

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