Identify Excel Files containing macros programmatically

Question

Hi,

I have developed an Access Database to load file name and extensions along with other file infomations (ie date created) into an access table.

We need to identify excel files (2003 and 2010 versions) that contain macros in them without opening each one to look (as there are thousands of Excel files).

We need to know this for regulatory purposes related to documentation of the macros.

My Question is:

Is there a way to tell if an Excel file has macros programmatically (with VBA, hopefully)?  If there is an .xlms file with no macro in it, we would also like to know this.

This would save a lot of work.

Many Thanks

Kody_Devl

Answer 1

Hi

This question comes back regularly. Seems difficult without opening the files.

See https://social.msdn.microsoft.com/Forums/office/en-US/a6af7e3f-6854-43a6-a3be-910a82977247/how-to-identify-the-excel-file-contains-macro-or-not?forum=exceldev

These looks like exercises so do not use their solution of opening a workbook without first disabling events firing., i.e. Application.EnableEvents=False, to avoid any Workbook_Open event code to fire.

HTH

Regards

JY

Answer 2

JY,

I counted over 200,000 excel files on our network.   If I have to open them with code to check for macros that may be the best option.

I my get hit with security and password issues on some (I don't yet know how many).   Thank you.