Save As Without any macros or modules

atreidies
atreidies used Ask the Experts™
on
I am using:

Application.Dialogs(xlDialogSaveAs).Show ("M:\ExcelCopies\File1.xls")
to save a copy and not alter the original. But I need to remove the On_Open Event from the Save As Copy.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
You can't do exactly what you describe, but a workaround would be to save the file in .xlsx format (which strips the macros), then save it again with its original file name. The code in the snippet below is working correctly in Excel 2010 and Excel 2003 (I have a compatibility pack installed on my computer).

Brad
Sub Test()
Dim flName As String, myName As String
Dim dVersion As Double
Application.DisplayAlerts = False
myName = ActiveWorkbook.FullName
flName = "C:\VBA\Sample '10\Neutered.xlsx"
ActiveWorkbook.SaveAs flName, FileFormat:=51    '.xlsx file format, without macros
dVersion = Application.Version
If dVersion > 11 Then
    ActiveWorkbook.SaveAs myName, FileFormat:=56    '.xls file format, with macros
Else
    ActiveWorkbook.SaveAs myName, FileFormat:=xlNormal    '.xls file format, with macros
End If
Application.DisplayAlerts = True
End Sub

Open in new window

byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
Rather than showing the SaveAs dialog, I prefer to use the GetSaveAsFileName method. This displays the file browser dialog and can default the file name for you--but it doesn't actually save the file. You do that separately using something like the previously suggested code.

The snippet shows code that you might use in Excel 2003 through 2010 to display the file browser and let the user pick a name. As shown, it defaults to the current name of the file--change the ThisWorkbook.Name to get a different default.

Brad
        'The FileFilter parameter eliminates the double quotes surrounding the default file name in the GetSaveAsFilename dialog, _
            provided that the existing file extension matches the default filter. _
            You get the text for the FileFilter from the File...Save As dialog. Look in the "Save as type" field. _
            Feel free to change the text before the comma. The comma and text following it must match exactly, however!
    flPath = Application.GetSaveAsFilename(ThisWorkbook.Name, FileFilter:="Excel workbook (*.xls),*.xls,Excel workbook (*.xlsx),*.xlsx", _
        FilterIndex:=IIf(ThisWorkbook.Name Like "*.xls", 1, 2))
            'You don't need the FilterIndex parameter if there is only one filter type. _
                The Iif function is like the worksheet IF function. It defaults the file type according to the current file name.
    If flPath = "False" Then Exit Sub   'GetSaveAsFilename returns "False" if user clears the file name field

Open in new window

Author

Commented:
We are unable to open .xlsx files.
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
It is possible to remove the code if "Trust access to Visual Basic project" has been checked in the macro security dialog. The code would then need to know where the macros need to be deleted from (which modules and code panes). The procedure is described here: http://www.cpearson.com/excel/VBE.aspx

It is much easier to save the file in .xlsx file format, which automatically removes any macros. As long as the compatibility pack has been installed on the computer that runs the macro, then it can use the .xlsx method to produce a .xls file without macros. People opening that .xls file do not need to have the compatibility pack, nor do they receive a macro warning message. This approach is followed in the code snippet below and sample file.

I tested the macro in both Excel 2003 and Excel 2010. I used files that originally had .xlsm and .xls file extensions.

Brad
Sub SaveWithoutMacros()
Dim flPath As String, myName As String
Dim iFileFormat As Integer
Dim wb As Workbook
Dim dVersion As Double
flPath = "C:\VBA\Sample '10\Neutered.xls"       'Default path & name for file without macros
myName = ActiveWorkbook.FullName
iFileFormat = ActiveWorkbook.FileFormat
    'The FileFilter parameter eliminates the double quotes surrounding the default file name in the GetSaveAsFilename dialog, _
        provided that the existing file extension matches the default filter. _
        You get the text for the FileFilter from the File...Save As dialog. Look in the "Save as type" field. _
        Feel free to change the text before the comma. The comma and text following it must match exactly, however!
flPath = Application.GetSaveAsFilename(flPath, FileFilter:="Excel workbook (*.xls),*.xls")
        'You don't need the FilterIndex parameter if there is only one filter type. _
            The Iif function is like the worksheet IF function. It defaults the file type according to the current file name.
If flPath = "False" Then Exit Sub   'GetSaveAsFilename returns "False" if user clears the file name fieldActiveWorkbook.SaveAs flName, FileFormat:=51    '.xlsx file format, without macros

Application.DisplayAlerts = False
Application.ScreenUpdating = False
ActiveWorkbook.SaveAs flPath & "x", FileFormat:=51    '.xlsx file format, without macros
dVersion = Application.Version
ActiveWorkbook.SaveAs myName, FileFormat:=iFileFormat
Set wb = Workbooks.Open(flPath & "x")
wb.SaveAs flPath, FileFormat:=IIf(dVersion > 11, 56, xlNormal) '.xls file format
Kill flPath & "x"
wb.Close SaveChanges:=False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Open in new window

SaveWithoutMacrosQ-26310140.xls

Author

Commented:
This worked...Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial