Strip Excel file of Macros with C#

Question

I've been asked to strip an Excel file of macros, leaving only the data. I've been asked to do this by converting the Excel file to XML and then reading that file back into Excel using C#. This seems a bit inefficient to me and I was thinking that it would be easier to simply load the source Excel file into C# and then create a new target Excel file and add the sheets from the source back into the target.

I don't know where macros live inside an Excel file, so I'm not sure if this would accomplish the task or not. So, will this work? Will simply copying the sheets from one file to another strip it of it's macros or are they actually stored at the worksheet level?

As always, any and all suggestions are welcome, including alternate suggestions or even "why are you even doing this???". :)

Answer 1

Your best bet is to save the workbook as an xlsx, close it, open it, then save as a format of your choice.

This will strip the macros and is robust. It will also work if the VBA is locked for viewing.

Closing and reopening the workbook is necessary otherwise the macros are retained.

Answer 2

To do this programmatically, you can use the ZipFile class from the System.IO.Compression library in .NET from C#. (.NET Framework 4.5)

Rename the file to add a ".zip" extension, and then open the file as a ZIP archive. Look for an element in the resultant "xl" folder called "vbproject.bin", and delete it. Remove the .zip extension. Macros gone.

Answer 3

If you're needing to use C# to do this, I agree that it would be easier to load the source Excel file into C# and create a new target file only copying over the cells and sheets you need. Especially if you're doing this for a large amount of excel files I would recommend just creating a small console app that, when given an excel sheet, will automatically generate a new excel sheet with just the data for you.

One tool that I've found extremely useful and easy to use for such tasks is EPPlus.