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

  • Question

  • Is it possible to detect if an excel file (xls) contains macros or not without opening it.
    please advise. Thanks
    Friday, September 23, 2011 5:55 AM

Answers

  • 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


    Saturday, September 24, 2011 11:59 AM
    Moderator

All replies

  • 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
    Friday, September 23, 2011 7:16 AM
  • 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


    Saturday, September 24, 2011 11:59 AM
    Moderator
  • 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



    • Edited by JackL123 Sunday, September 25, 2011 5:38 AM
    Sunday, September 25, 2011 4:57 AM
  • 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

    Sunday, September 25, 2011 3:59 PM
    Moderator