How do you document all XLSX/XLSM files?

[serbach]

After @RoryA answered my question on Thursday, Jan. 27th, about how to tell if a workbook is password-protected, I thought I would post here with the method I came up with for documenting a whole pile of Excel files (we have over 50,000 of them)... but then I wondered whether I simply missed the fact that maybe someone had already solved that puzzle.

For every XLSX/XLSM file in a directory structure, my method documents:

• Author & Last modified by
• Date created & Date last saved
• All file names of Links to external Excel workbooks
• The number and type of Connections (OLE, ODBC, Worksheet, Power Pivot, etc.)
• The connection details (SQL Command strings, PowerPivot model details and column names, and Connection strings, etc.)
• The number of Power Queries and their details (M language statements)
• The names of all worksheet tabs
• The names of all Excel tables
• The names of all PivotTables

So, what do you use to document in one place all of the scattered Excel files in your organization?

Sincerely,

Steve Erbach
Green Bay, WI