Saving a .xlsm file as .xlsx using VBA

[PrgmMe]

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

 

[Jerry Sullivan]

Hello and Welcome to the Board,

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

ActiveWorkbook.SaveAs Filename:="myFile.xlsx",  FileFormat:=xlOpenXMLWorkbook

You can find more options in the Excel Help (Excel Developer Reference) under Workbook.SaveAs Method

 

[PrgmMe]

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.

 

[Samv6271]

Hello and Welcome to the Board,

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

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?

 

[CROforce]

You can try this:

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

 

[Samv6271]

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:

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

or, more robustly:

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

 

[kind03cn]

Hello and Welcome to the Board,

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

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."

 

[mikestilly]

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 <code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: pre-wrap; color: rgb(36, 39, 41); background-color: rgb(239, 240, 241);">Application.DisplayAlerts = False</code>

 

[mikestilly]

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 <code style="margin: 0px; padding: 1px 5px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: pre-wrap; color: rgb(36, 39, 41); background-color: rgb(239, 240, 241);">Application.DisplayAlerts = False</code>

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

 

[Dallas1]

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

thanks for the advice and I added your line. The code below works as it takes only the filename of the .xlsm, since the ".xlsm" is always 5 characters. But I keep getting the Display error. How to avoid this error and the manual prompt (I'll be making a few dozen copies with different names, so don't want this pop-up box).


Sub Excel20000()
Dim x As String, y As String
x = ActiveWorkbook.Path
y = Left(ActiveWorkbook.Name, Len(ActiveWorrkbook.Name) - 5)
ActiveWorkbook.SaveAs x & "" & y & ".xlsx", FileFormat:=xlOpenXMLWorkbook
Application.DisplayAlerts = False
End Sub