Office Migration Planning Manager (OMPM) doesn't report Word and Excel documents with macros (VBA projects)

  • Article

Please see this blog posting on how to ensure you get Word and Excel documents reported correctly:

https://channel9.msdn.com/wiki/default.aspx/OfficeDeployment.OMPMContributions 

Here's the content of that blog:

Filter for Excel, Word, PPT files with Macros

The OMPM does not report on any macro conversion issues, but you can use it to find all documents with VBA projects. The OMPM scanner can detect if a Word, Excel, or PowerPoint file has a VBA project or not. (Even if the VBA project is empty, it will still be reported as having a project). With a few simple tweaks, you can import this information in the OMPM database and filter for files with Macros. This does require a new database, but you won't need to rescan as the scan xml logs already contain the VBA Project information

  • Open up OMPM\Database\Include\ProvisionDB.SQL (you may want to back it up)
  • Insert the following line into the section that creates the osVBAProperty table (line 718). This adds a new column "NoVBAProject" to osVBAProperty table
      [NoVBAProject] [bit] NULL,
  • Open up OMPM\Database\Schemas\LogFileMapping.xml (you may want to back it up first)
  • Insert the following line at the end of the section describing the other VBA project properties (line 95)
      <ElementType name="NoVBAProject" dt:type="boolean" sql:datatype="bit"/>
  • Insert the following line into the element type description of the relation between VBA Properties and the osVBAProperty table (line 287)
      <element type="NoVBAProject" sql:field="NoVBAProject" />
  • Create a new OMPM database
  • Import Scan results (you can reimport older scan results by moving them one level out of the \OMPMImported folder)
  • This new "NoVBAProjects" column is not exposed in the Reporting Tool, so you'll need to run some SQL directly against the OMPM database:
       SELECT * FROM osScanFile WHERE scanfileID IN (SELECT scanfileID FROM osVBAProperty WHERE NoVBAProject = 0)
      (You can customize this query and join it with other tables for more information if desired)
  • Now you have a list of Excel, PPT, and Word files with Macros!