Save Excel without macros?

[Codman]

I'd like to save a workbook via vba without saving the macros using the following code:-


ActiveWorkbook.SaveAs FileName:=Name, FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False

If this is not possible, is it possible to disable the macro's when the new saved document is opened?

CodMan

 

[taupirho]

This should give you a few ideas


Sub savenomacros()

ThisWorkbook.Sheets(Array("sheet1", "sheet2", "sheet3")).Copy

'
' Prevent popup message if SaveAs below is overwriting an existing file
'
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs FileName:="C:\nomacros.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

ActiveWorkbook.Close False

Application.Quit


End Sub

 

[Codman]

Hi tomreid,

Thanks for the idea. I ran this in a new workbook with no problems, adding filepath code as shown below. If I use this within your code it works, but whn I try this using my sheet names I get a subscript out of range error. I've check the sheet names but no joy. Some of the sheet are xlVeryHidden and I tried this on the test workbook, again with no issues. Any clues??


Public Sub no_macro_save()
Dim FileName As String

FileName = Sheet2.Range("A78")



ThisWorkbook.Sheets(Array("Config_Selection", "Forms_Content", "User_Selection", "Diag_Pub", "Price_list, BOM_MASTER" _
, "CTO_BOMSheet", "Config_Price")).Copy


' Prevent popup message if SaveAs below is overwriting an existing file
'
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs FileName:=FileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

ActiveWorkbook.Close False

Application.Quit
End Sub

 

[Codman]

Hi Tomreid,

I noticed that I missed quote marks off two of the file names. Unfortunately this didn't make any diference. Still looking for that magic bullet!!

Pete

 

[Stella740pl]

Codman,

You can try it with numeric indexes instead of names of the worksheets:

ThisWorkbook.Sheets(Array (1, 2, 3)).Copy

Try it with only non-hidden sheets, too. Would it work that way?

 

[Codman]

Hi Stella,

I tried this and managed to get it to work with sheet names NOT conjoined with an underscore. It works with both xlvisible and xlhidden sheets but does not copy xlveryHidden which is all well and good. The problem I have now is that although the vba Module folders are not copied, nor is any code from the 'Thisworkbook' folder, the sheet macro's are still present. Any clues in getting rid of these also? The workbook is ok if I click disable macros on opening?

Pete

 

[Stella740pl]

For now, I can only think of one option.

I guess, to get rid of macros in the worksheets you can only select, copy & paste the contents of the worksheet, not the whole worksheet as in the method shown above. It might take you a little more coding, you will have to loop through the worksheets and do it to every one of them.

(Something like
Cells.Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
etc.)

It might even work for xlveryHidden, I don't know, you should try. You might have to set the hidden attributes again to your newly copied worksheets.

Stella

 

[Codman]

Thanks Stella,

I used this code and successfully with a slight modification. Thanks for advice.

Regards

Pete