Every organisation sooner or later has to deal with Office documents with macros enabled. Risk assessment and auditing are the first steps when planning to disable them via group policy or just to mitigate risk implied.
The priority should target real usage and avoid any untrusted macro. In this article I will try to help to create a simple report where we can simply leverage powershell to find documents with macros enabled.
This example function creates a list of all files in a drive or specific folder with an extension that is associated to Microsoft Office with macros enabled.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
#requires -runasadministrator <# .Synopsis Get all office documenta with Macros .DESCRIPTION Get all office documents with Macros .EXAMPLE Get a list of all documents with macros saved on a specific folder (in case SHARED FOLDER needs to be mapped in advance) and format the result as a table. PS D:\> Get-FilesWithMacros "d:\" | ft Directory Name LastWriteTime LastAccessTime Length --------- ---- ------------- -------------- ------ D:\ New Microsoft Excel Worksheet.xlsm 18/01/2018 6:45:27 PM 18/01/2018 6:45:27 PM 6164 D:\ New Microsoft Word Document.docm 18/01/2018 6:45:33 PM 18/01/2018 6:45:33 PM 0 .EXAMPLE How to create a Report in CSV Format with all macro documents gfwm "d:\" | Export-CSV "Report_D_drive_macros.csv" .NOTE Author Paolo Frigo, [email protected] https://www.scriptinglibrary.com #> function Get-FilesWithMacros { [CmdletBinding()] [Alias('gfwm')] [OutputType([string])] Param ( # Param1 help description [Parameter(Mandatory = $true, ValueFromPipelineByPropertyName = $true, Position = 0)] $folder ) Begin { } Process { #List of all Office Documents Extensions with Macros enabled $macro_extensions = ".docm", ".dotm", ".xlsm",".xlm", ".xltm", ".xla", ".pptm", ".potm", ".ppsm", ".sldm" get-childitem -Path $folder -Recurse -ErrorAction SilentlyContinue| Where-Object { $macro_extensions -contains $_.Extension} | Select-Object Directory, Name, LastWriteTime, LastAccessTime, Length } End { } } |
To get a report of all drives (where used space is greater than 0) in one-liner :
|
1 |
(Get-PSDrive | ?{$_.Used -gt 0} ).Root | % {gfwm $_}| ft |
Feel free to re-use this script! Remember to check my github repository for the latest version.
Hi Mate,
Thanks for sharing. I am aiming to see which files have macros enabled. Where to get started and where to execute the scrip you have mentioned above ?
I am fairly new to powershell.
Thanks
Hi Azeem,
Thanks for your comment. I’m glad you find this useful. If you are new to powershell this script is quite simple except fo the the one-liner at the bottom of the article.
You can download the script from the github repository or copy and paste into your editor with “ps1” as extension.
At the bottom of the file you can add this line if you want to scan the volume you would like to scan for instance “D:”
Get-FilesWithMacros “d:\” | ft
If you want to run it for all drives (even the external) attached you can copy and paste the one-liner at the end of the article instead.
Regards
This only locates files saved in macro enabled file formats.
Old office file formats, e.g. .doc, .xls and .ppt can also contain macros.
Also, files saved in macro enabled formats can exist without actually containing macros.
The proper way to check is by creating application com objects for excel, word etc, in powershell, then using the com objects you can query the files to see if the property “HasVBProject” is set to true. This way you can identify all office files that contain macros regardless of the file format.
Hi Thomas,
Thanks for your comment. I think your feedback is great.
The only objection that I have is that doc, xls, ppt files are almost extinct today, I haven’t seen one in the last decade. Considering we don’t see documents older than 2007 very frequently.
The com/dll approach or using the office interop assemblies and embedding C# into a powershell script are all viable options, but I will go down that route only if again I am dealing with files that are really old.