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

I doubt you can do it, that sounds like it would be a major security risk. — Mathias
– Mathias, Commented Aug 15, 2011 at 19:25
That's what I had anticipated... I'm hoping that I could at least make the batch changes though, assuming I have write-access to do it with... I'd prefer not to have to go through the directory, making all the changes manually... — Jonathan
– Jonathan, Commented Aug 15, 2011 at 19:30
What setting is it exactly that you talk about? Is it the Trust access to the VBA project object model setting? This is an application wide setting, not a per-file setting. If you cannot change that you could write your own parser to extract the macro objects from the binary file directly (This could be done in 2-3 days of work). — Dirk Vollmar
– Dirk Vollmar, Commented Aug 15, 2011 at 20:32
@0xA3 What do you mean by it being an 'application wide setting'? Is there some function I can call from my Excel.Application object to make this change? Yes. I am talking about the 'Trust access to the VBA project object model setting'. — Jonathan
– Jonathan, Commented Aug 16, 2011 at 17:00

Gary McGill · Accepted Answer · 2011-08-16 17:23:27Z

4

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

edited Aug 16, 2011 at 17:23

answered Aug 16, 2011 at 17:13

Gary McGill

27.8k26 gold badges126 silver badges210 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jonathan Over a year ago

So, how would I enable this setting for a program which searches the content of the macros of each .xlsm file in a directory? Do I need to physically open one of the files before running the program and change the setting? And this would work even for a read-only file?

Gary McGill Over a year ago

@Jonathan: the "trust access" setting has nothing to do with the files. It's an application-wide setting that applies to all files. If you open Excel you can change this setting even if you have no files open. Excel will remember that setting forever (until you change it again).

Gary McGill Over a year ago

@Jonathan: the password-protect setting by contrast is specific to each file, so you need to unlock each file individually when you open it (every time you open it). I don't know if you can do that via code. (I'm not saying you can't - I've just never tried it). It doesn't matter that the file is read-only, since you're not changing anything by unlocking the project - it only stays unlocked until you close the file. (On the other hand, you could remove the password-protection, in which case that would be a change that you'd have to save).

Collectives™ on Stack Overflow

C# Programmatic Access to Excel macros

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related