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