A selection of topics on IT and its application to finance.
Send me your comments, questions or suggestions by clicking here
Saving an EXCEL spreadsheet without its macro code
Here's a problem I came across recently. I was working on a spreadsheet for a client which contained some macro code to download some data from an Oracle spreadsheet, do some cell formatting and some general tidying up of the data. The end result was a workbook with three sheets which I wanted to then send to the client via email. However their email system, for security reasons, would not allow them to receive ant attachments which contained executable code. If my workbook had had only one sheet this wouldn't have been a problem since I could easily have saved it as a CSV format text file and sent it. But you can only save 1 sheet at a time in this way and I didn't want to send the client three different files. So, how to save the spreadsheet as is but without the macros? Here's some code that will do exactly that.
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, _