C# code that remove all macros from Excel file-CodePudding

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!!!

CodePudding user response：

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");

CodePudding user response：

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.