C# Programmatic Access to Excel macros

Question

I am working with a directory of Excel files to get information about each file. I am trying to use C# Excel interop to gather information about VBA Macros associated with some of these files. The code for this is found below. The problem is that none of the excel files have programmatic access to macros enabled. I can switch this manually on local copies of the file, but I currently only have read access to the directory of files. Is there any way I can temporarily change the programmatic access setting inside my code (to read the VBA code, not make any changes) without having write permission?

Also, I only know how to make the change to programmatic access manually (through the settings in each excel file). Seeing as I may eventually just need to get read/write access, is there any way I can do this in a batch process to save a lot of time manually opening and closing files?

        VBA.VBProject project = WorkBook.VBProject;
        VBA.VBComponents VBComponents = project.VBComponents;
        string projectName = project.Name;
        VBA.vbext_ProcKind procedureType = Microsoft.Vbe.Interop.vbext_ProcKind.vbext_pk_Proc;
        VBA.VBComponent vbFunction;

        foreach (Excel.Worksheet sheet in VBComponents)
        {
            vbFunction = sheet as VBA.VBComponent;

            if (vbFunction != null)
            {
                VBA.CodeModule componentCode = vbFunction.CodeModule;
                int componentCodeLines = componentCode.CountOfLines;

                int line = 1;
                while (line < componentCodeLines)
                {
                    //EXAMINE LINE

                    line++;
                }
            }
        }

.

EDIT:

The exact error message that is produced is "COMException was unhandled - Programmatic access to Visual Basic Project is not trusted".

I have since found that I get a different error message if I open one of the read-only files and change the setting. I cannot save the file, but if I leave it open, when it reaches the first .xlsm file, it prints the error message "COMException was unhandled - can't perform the operation since the project is protected".

Answer 1

The "Trust Access to Visual Basic Project" setting can be found in Tools -> Macro -> Security, on the "Trusted Publishers" tab. (This is for Excel 2003; for 2007, it can be found in Excel Options -> Trust Center -> Trust Center Settings -> Macro Settings).

It's a Excel application setting, and applies to all instances of Excel started by the user from that point forward (whether started manually or programatically).

You need to ensure that this setting is enabled wherever Excel is running (independently of where the Excel files you are processing are stored).

(You can't change this setting programatically - that would make it a completely pointless setting).

---

EDIT: you're now getting a different error: "COMException... the project is protected".

You'll get this error if the VBA project in the Excel file is protected with a password (Project Properties, Protection tab). In this case, you need to unlock the project before attempting to open it.

I've written a few macros that access the VBA code in a protected project, but in my case I've been doing it one file at a time, so I've simply asked the user to unlock it and try again.

I'm not sure if it's possible to programmatically unlock the project if you know the password (but I'm pretty sure it's not possible if you don't).