Saving a .xlsm file as .xlsx using VBA

PrgmMe

New Member
Joined
Dec 10, 2010
Messages
3
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
 
ok, did some searching and the key to avoid that popup error is to disable the Alerts first , before the main code, and then enable it after. This works well.

Sub Excel20000()
Application.DisplayAlerts = False
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 = True
End Sub
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
ok, did some searching and the key to avoid that popup error is to disable the Alerts first , before the main code, and then enable it after. This works well.

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


typo in "workbook", single r only
 
Upvote 0

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

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


I tried that code but it saved my file "June 2016.xlsm" in My Documents and also as file "myfilename.xlsx".
 
Upvote 0
i have made a mistake and wanted to correct it. Need to add the "" in the SaveAs command. I discovered this error while running the macro on my work computer where the original code failed. The original code somehow worked on my home PC withouth the slash. I don't understand the error but still, use the backward slash which should make for a good macro.

Sub Excel20000()
Application.DisplayAlerts = False
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 = True
End Sub
 
Last edited:
Upvote 0
i tried to edit my post but the changes were not taking effect. Weird. Need the backslash

in place of the "". Why doesn't this editor accept this change??





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

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top