This small application could be considered as a variation of my previous application “Folders, Subfolders & Files”. The difference is that the particular workbook is able to list:
- the number of worksheets for Excel files,
- the number of pages for Word and PDF files,
- the number of slides for Power Point files and
- the number of objects (tables, queries, forms, reports) for Access files.
Just follow these steps:
- Open the workbook (and enable macros).
- Press the RUN button and select a folder that contains the files you want to be listed. Then, press OK and wait.
- The macro creates a new worksheet, which is named after the folder’s name. For each file in the folder (and its sub-folders) the macro writes: the file name, the file type and its number of sheets/pages/objects. If the file is not Excel, Word, Power Point or PDF file, it writes a “-“ in the corresponding column. Moreover, the filename works as an hyperlink; by clicking upon the filename the file opens.
- Finally, a message box will inform you when the macro finished the task.
- As an extra feature, you can delete the sheets of the workbook (apart from the first one) and you can close the workbook (without saving) by simply pressing the corresponding buttons on the form (Delete Sheets & Close Application buttons).
The output will look like the picture below:
Note that the macro opens every Office/PDF file in order to count its sheets/pages/objects. So, in the case that your file requires a password to be opened, a pop will appear asking you to enter the password. If you entered it correctly, the macro will continue to work normally. For PDF files, the VBA code uses objects from Adobe Reader/Professional library. So if you have not installed any of these two products in your computer, the macro will fail.
Download it from here
This file can be opened with Office 2007 or newer. It could be opened even with older Office versions that incorporate the compatibility pack from Microsoft. However, do not forget to enable macros before using it.