Save As Without any macros or modules

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

Select allOpen in new window

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

Select allOpen in new window

We are unable to open .xlsx files.

This worked...Thanks