Programatically Trap Missing Reference Compile Error upon Opening Excel Macro

[darrendrake]

I have a scenario where I am attempting to convert .xlsm files to .xlsx.

Scenario:

1. Browse to a folder with say 500 .xlsm files
2. Open each file, strip out the macro code and save as a non-macro enabled file(.xlsx).​

I have code that achieves the above scenario; however, all of the .xlsm files have references to third party tools that no longer exist in my environment.

This has created missing references. I have attempted to remove the references programmatically. However, each time I attempt to programmatically open one of the .xlsm files from my controlling macro file I receive a compile error in the .xlsm file that is attempting to open. The error states, “Can’t find project or library.” This is because the .xlsm file has missing references.

As you can imagine opening 500 files manually to remove stale object library references is a daunting task.

I tried trapping the error, but the error is actually thrown in the file that is attempting to open opposed to the one from which I am initiating the open command. This is the source of my frustration.

Area where help is needed:
I am looking for suggestions as to how to programmatically deal with the compilation error generated upon opening the various .xlsm files. Assuming this is possible, I am looking for help with trapping the error that occurs in the opening .xlsm file from the controlling macro file, so that I can divert the focus to my procedure that removes the stale references.

I hope this make sense and appreciate any assistance anyone may provide.

 

[RoryA]

I haven't ever tried it, but have you tried using application.automationsecurity = msoAutomationSecurityForceDisable to disable all macros in files you open and then simply save as xlsx? You shouldn't need to actually run or remove any code yourself other than opening and saving if you do that.

 

[darrendrake]

Rory, thanks for the response.

I have not tried that which you have suggested. I don't believe it will solve my immediate issue of dealing with the compile errors that are thrown when opening the files. The errors are thrown in response to missing references. This is the issue that I am looking to resolve.

 

[darrendrake]

Rory,

I played around with the class you suggested, and it did resolve my issue.

Thanks for your help!

 

[RoryA]

Glad to help.