detect if an excel file (xls) contains macros or not without opening it

Question

Is it possible to detect if an excel file (xls) contains macros or not without opening it.
please advise. Thanks

Answer 1

Hi

There is a property called HasMacros. You can try that.

You need to use DSOFile if you want to extract the property without opening Excel file.

Cheers

Shasur

 

---

http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com

Answer 2

Shasur & Jack,

There is no property as such called HasMacros. The early DSOFile v1.4 had a member HasMacros which simply checked the file for an indication of it having a VBProject. Similar can be done by reading the file as text, and searching for say "_VBA_PROJECT_CUR". However all that does is confirm the file has at least once been open at the same as the VB editor was open. If there's no evidence of a vbProject it can be concluded it does not have code, however the opposite does not prove the file does have vba code.

That ambiguity is probably why HasMacros was removed from later versions of the DSOFile.dll.

Although with some files it may be possible to conclude no macros are present, AFAIK there is no direct way to conclude a file has code (macros) without opening the file.

FWIW about 5 years ago I posted an approach to return a listing of all files in a folder that either definately don't contain code, or potentially might contain code. Post back if interested.

The above concerns 2003/xls, with 2007/xlsm we can look for "vbaProject.bin" in the zip

Peter Thornton

Answer 3

Thanks Shasur & Peter

 

Sharing your knowledge would be much appreciated.

And if I read .xls as a binary file and search for "&H00000001" to decide if there's macro. Does it work? or has the same bug as DSOFile?

 

Thanks
Jack

Answer 4

Searching for "&H00000001" wouldn't be meaningful, use the string I suggested before. Post back if not sure how to go about getting a file listing and searching their binaries.

In a light test of checking a few hundred files searching for the string gives exactly the same results as HasMacro in the old DSOFile v1.4

Whilst the old HasMacro is in a sense buggy, it'd be wrong to classify searching the binary the way I suggested as buggy. The approach correctly indicates if the file has a code project. If it doesn't you can conclude there is no code, but the presence of a project does not prove there is code.

Typically most users never open the VB Editor so chances are you'll be able to quickly confirm most of their files do not have any code. But my VB Editor is almost permanently open, so most of my files will include a project, though the project may or may not contain any code.

Peter Thornton