C# code that remove all macros from Excel file

Question

I have a Windows form app project (C#).

I am trying unsuccessfully to write code so that when I click a button I created, it will load an excel file that actually has macros in it and delete them all.

I know you can delete macros manually in the excel file itself but I need a way to do it programmatically (I just delete them all for the user).

I know that the macros in Excel files are written in VBA language, so I try to use the related libraries in c# but I get an error when I try to use VBProject, VBComponent. This what I tried so far:

using Microsoft.Office.Interop.Excel;

namespace MacroRemover
{
    public partial class Main : Form
    {
         private void Btn_Click(object sender, EventArgs e)
        {
            string filePath = "path\\to\\file.xlsm";

            Application excel = new Application();
            Workbook workbook = excel.Workbooks.Open(filePath);
            VBProject vbProject = workbook.VBProject;

            foreach (VBComponent component in vbProject.VBComponents)
            {
                vbProject.VBComponents.Remove(component);
            }

            workbook.Save();
            workbook.Close();
            excel.Quit();
        }
    }
}

Any way that works will help me, I would appreciate the help

Thanks in advance guys!!!

You can try this: https://stackoverflow.com/a/49279076/453348 — tttony, Jan 21 '23 at 17:39
@tttony Thanks for the response, I was able to use it in some way actually :) — Mor b, Jan 21 '23 at 20:54

score 1 · Answer 1 · answered Jan 21 '23 at 20:42

1

If you don't need to save xlsm format of the file, you can save this file to xlsx format and all scripts will be removed.

using Aspose.Cells;     
var workbook = new Workbook("input.xlsm");
workbook.Save("Output.xlsx");

answered Jan 21 '23 at 20:42

Bushuev

557
1
10
29

I actually did it at first and yes it works. I just had to do it more programmatically using VBA, but you right, its one solution, thanks :) – Mor b Jan 21 '23 at 20:56

score 0 · Answer 2 · answered Jan 21 '23 at 21:11

Thanks for all the replies, @tttony's response helped me to understand more deeply and @Bushuev's response here is definitely a possible and simple solution for deleting the macros.

I finally managed to delete all the macros like this:

string filePath = Path_TxtBox.Text;

            Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
            Workbook workbook = excel.Workbooks.Open(filePath);
            VBProject project = workbook.VBProject;

            for (int i = project.VBComponents.Count; i >= 1; i--) 
            {
                VBComponent component = project.VBComponents.Item(i);

                try
                {
                    project.VBComponents.Remove(component);
                }
                catch (ArgumentException)
                {
                    continue;
                }
            }

            for (int i = project.VBComponents.Count; i >= 1; i--) 
            {
                VBComponent component = project.VBComponents.Item(i);
                component.CodeModule.DeleteLines(1, component.CodeModule.CountOfLines);
            }

            workbook.Save();
            workbook.Close();
            excel.Quit();

            MessageBox.Show("Macros Removed");

It should only be noted that I encountered an error: 'Programmatic access to Visual Basic Project is not trusted'

It was solved after I realized that I had to change the option to access the VBA Project object model in the Trust Center settings.

C# code that remove all macros from Excel file

2 Answers2