Monday Morning Mulling: June 2023 Challenge

On the final Friday of each month, we set an Excel / Power Pivot / Power Query / Power BI problem for you to puzzle over for the weekend.  On the Monday, we publish a solution.  If you think there is an alternative answer, feel free to email us.  We’ll feel free to ignore you.

The challenge this month was to unblock multiple macro-enabled Excel files from an external source. 

The Challenge

Imagine, you received several Excel files containing macros from your friend, your colleague or from the internet.  You needed all these Excel files to have macros enabled and standing in your way was the security.  Unblocking all the security from the ‘Properties’ window of all the files you have is a time-consuming task.  Hence, we devised a challenge for you to find a simple and efficient way to unblock all the macro-enabled files.  You can download the question file here.

This month’s challenge was to get rid of this message for all of your Excel files that contain  macros (below).

As always, there were some requirements:

• the solution should be simple
• no coding was allowed.

Suggested Solution

Before we begin, let’s discuss the relevant Microsoft security features here.

Microsoft’s software puts up a message called ‘SECURITY WARNING’ if we are opening files from the internet.  The reason for this ‘SECURITY WARNING’ is due to the fact that when you open an Excel file that contains macros, most people ignore this security warning and press the ‘Enable Content’ button right away (below): 

This can potentially infect the PC with a virus, especially if the Excel macro-enabled files come from an untrusted source.  Hence, Microsoft must implement different security checks for every file originating from the internet or Restricted zones.  Now, you will see a ‘SECURITY RISK’ message appear after you press ‘Enable Content’:

The way Microsoft detects whether a file is from the internet is by a mark.  If we download an Excel file from the internet it will have the ‘Mark of the Web’ (MOTW).  We can see the indicator of MOTW via the Properties -> General -> Security:

The standard way we remove the MOTW is to tick the unblock box and apply it.  It will unblock macros for our workbook.  However, if we have hundreds or thousands of Excel files that contain macros, it will take significant time to unblock them all.

Solution 1: Zip and Unzip

To solve this issue, we can utilise the built-in Zip program in Windows.  This is a simple solution that involves zipping all the Excel files that contain macros downloaded from the web or that have the MOTW (Mark of the Web) attribute.  Once the files have been zipped, we can then proceed to unzip the files, which will automatically unblock all the Excel files in the zip.  The pictures below will show what happens to the MOTW:

We can see here that for our source file (Properties window on the left) the size on disk is 68 KB. The size jumps up to 72 KB with  a MOTW (Properties window in the middle).  After we zip and unzip the size on disk of the file (Properties window on the right) is the same as our original source file.  Thus, this means that files downloaded from the internet will have a MOTW added which increase the file size.  By zipping and unzipping the file(s) we will remove the MOTW from our file(s). 

If we are using a different zip program, we might need to unblock the security for the zip file before proceeding with the unzipping process.  To do this, we can right-click on the zip file, select Properties -> General -> Security -> Unblock then proceed with unzipping the file to achieve the same result.

Solution 2: Trusted Location

We can also set up a trusted location where we can drop all excel files that contain macros.  We can do this by going to File -> Options -> Trust Center -> Microsoft Excel Trust Center -> Trust Center Settings.

This will pop up the Trust Center window.  From here we go to Trusted Location -> Add new location.

In the Microsoft Office Trusted Location, we can specify the folder path in the Path section or we can browse to the folder we want with the Browse button.

In this example, our trusted location is: 

Although we just moved my file into the trusted location, the MOTW is still there:

Well, don’t worry about that!  If we open our Excel files from here, there is no longer any security risk message, and we may use our macro-enabled workbook(s) freely.

Solution 3: SharePoint

SharePoint is a web-based platform for collaboration that seamlessly integrates with Microsoft 365.  Similar to designating a Trusted Location, uploading an Excel workbook that contains macros to SharePoint will unblock those with security issues, allowing macros to run freely.  This removes a major point of friction for Excel macro workflows by making the macros available right away to all team members with access to that SharePoint document library.

The MOTW warning will remain in the workbook properties to remind users that macros should only be enabled for files stored within the organisation's secure SharePoint site.  Macros should function properly once there in SharePoint as it “transforms” into a digital command centre for your most important Excel macro projects.

Word to the Wise

There are some other ways to unblock the macro files, like writing VBA code on your local PC or using PowerShell, which can quickly turn off the MOTW attributes.  This might be a challenge for another Final Friday Fix…

The Final Friday Fix will return on Friday 28 July 2023 with a new Excel Challenge.  In the meantime, please look out for the Daily Excel Tip on our home page and watch out for a new blog every business working day.