​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


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

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