By now, you have probably heard of Microsoft’s new plan is:

and you can learn all about when this is coming to a PC near you, and all the other succulent details, by reading:

Microsoft Office is About to Block ALL Macros & VBA Code

If you haven’t already heard, beginning with Microsoft Office version 2203 (for the Current Channel), set to release in early April 2022, Office will disable all VBA code in files from the Internet.  Here’s the breakdown by Office Update Channel Update channel Version Date Current Channel (Preview) Version 2203 April 2022 Current Channel To be…

Continue reading →

Office to Disable All VBA Code in Files from the Internet

Beginning in April 2022, users will no longer have the option to manually enable VBA code in Office files downloaded from the internet.

In the sections below I will examine:

• Workarounds
  • Trusted Locations
  • Using The SysInternals Streams Utility
  • Using the File System Object (FSO)
  • DeleteFile API
  • PowerShell Unblocking Script
  • Group Policy
• VBScript Versions Of The Approaches
  • Using The SysInternals Streams Utility
  • Using the File System Object (FSO)
  • PowerShell Unblocking Script

Workarounds

As for me, I am looking into what I can do to minimize the frustration of dealing with this new hurdle of adding an extra 3, 5, 10+ clicks for each individual file I want to be able to work from e-mails, websites, forums I support, …
 

Trusted Locations

That is correct, by placing the file(s) in a folder designated as a Trusted Location unblocks the file. It doesn’t actually remove the block, but it will still allow the code to run inhibited. So, if the file is moved, distributed, it will be blocked once again.

If you are not aware of Trusted Locations, I’d urge you to look over my article on the subject:

Using The SysInternals Streams Utility

Another option is to use the SysInternals Streams Utility:

Streams - Sysinternals

Reveal NTFS alternate streams.

You can either run it manually via the Command Prompt, for instance by doing something like:

 cd C:\Users\Daniel\Desktop\Form_SplitFormSimulated
streams64 -nobanner -d C:\Users\Daniel\Desktop\Form_SplitFormSimulated\Form_SplitFormSimulated.mdb

(cd into the folder housing the streams*.exe and then run the command.

Or you can automate it via VBScipt, VBA, … by doing something like:

'SI_UnblockFile "C:\Users\Daniel\Desktop\Form_SplitFormSimulated\Form_SplitFormSimulated.mdb"
Public Sub SI_UnblockFile(ByVal sFile As String)
    Dim sCmd As String
    Const sEXEPath = "C:\Users\Daniel\Desktop\"
    
    sCmd = "streams64 -nobanner -d """ & sFile & """"
    Call Shell("cmd.exe /c cd " & sEXEPath & " & " & sCmd, 1)
End Sub

'SI_UnblockFolder "C:\Users\Daniel\Desktop\Form_SplitFormSimulated"
Public Sub SI_UnblockFolder(ByVal sFolder As String, Optional bIncludeSubFolders As Boolean = True)
    Dim sCmd As String
    Const sEXEPath = "C:\Users\Daniel\Desktop\"
    
    If bIncludeSubFolders = True Then
        sCmd = "streams64 -nobanner -s -d """ & sFolder & """"
    Else
        sCmd = "streams64 -nobanner -d """ & sFolder & """"
    End If
    Debug.Print sCmd
    '/k => stays open, /c => closes
    Call Shell("cmd.exe /c cd " & sEXEPath & " & " & sCmd, 1)
End Sub

The beauty here is this actually deletes the Alternative Data Stream (ADS) for the file, so now you can distribute it internal without issue.
 

Using the File System Object (FSO)

Another possibility would be to use FSO to overwrite the Zone.Identified ADS by doing something along the lines of:

Public Sub FSO_UnblockFile(ByVal sFile As String)
    Dim oFSO                  As Object
    Const ForWriting = 2
   
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    With oFSO.OpenTextFile(sFile & ":Zone.Identifier:$DATA", ForWriting, True)
        .Write ""
    End With
End Sub

and then calling it like:

Call FSO_UnblockFile("C:\Users\Daniel\Desktop\Form_SplitFormSimulated\Form_SplitFormSimulated.mdb")

Once again, this can be used via VBScript, VBA, …

This will unblock the file, but doesn’t actually delete the ADS, just blanks it, makes it = “”. I prefer using an approach that actually removes it completely.
 

DeleteFile API

The following was contributed by Dan W in the comments bellow and I thought it was worthy of adding to the main article. As he explains,

#If VBA7 Then
    Private Declare PtrSafe Function DeleteFile Lib "kernel32" Alias "DeleteFileA" (ByVal lpFileName As String) As Long
#Else
    Private Declare Function DeleteFile Lib "kernel32" Alias "DeleteFileA" (ByVal lpFileName As String) As Long
#End If

'? API_UnBlockFile("C:\Users\Daniel\Destop\Contacts.accdb")
'? API_UnBlockFile("C:\Users\Daniel\Destop\ContactsDb.zip")
Function API_UnBlockFile(ByVal sFile As String) As Boolean
    'returns True => file unblocked
             False => file not unblocked, check Read-only, File Exist, permissions, ...
    API_UnBlockFile = (DeleteFile(sFile & ":Zone.Identifier:$DATA") <> 0)
End Function

and to process all the files within a directory you could do something like:

' API_UnBlockDirectory "C:\Users\Daniel\Destop", "zip" 'Only zip files in the folder
' API_UnBlockDirectory "C:\Users\Daniel\Destop" 'All files in the folder
Function API_UnBlockDirectory(sPath As String, Optional sFilter As String = "*")
    Dim sFile                 As String

    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
    sFile = Dir(sPath & "*." & sFilter)
    Do While sFile <> vbNullString
        If sFile <> "." And sFile <> ".." Then
            'Debug.Print "Unblocking '" & sFile & "'"
            DeleteFile sPath & sFile & ":Zone.Identifier:$DATA"
        End If
        sFile = Dir
    Loop
End Function

for more information on this API, refer to the official documentation:

DeleteFileA function (fileapi.h) - Win32 apps

Deletes an existing file. (DeleteFileA)

PowerShell Unblocking Script

The Basic Idea

So I am currently looking at simply using a simple PowerShell script (so a single double-click) to unblock files that I will have in a designated folder (downloads probably) in one shot. You can use a command such as:

Dir C:\Users\Dev\Downloads\* | Unblock-File

or to just unblock accdb files

Dir C:\Users\Dev\Downloads\*.accdb | Unblock-File

and so on.

Taking Things Further – Drop In Place Solution

Now, PowerShell is great, but I don’t want to have to fire up the PowerShell ISE to run the command each time, all those extra clicks, copy/paste of the command, would not actually be simplifying things at the end of the day.  No, I want to be able to unblock a bunch of files in one action, one click (or double-click).

So that’s what I’ve done and here’s how you can do it as well.

Attempt 1

To make things even more flexible, I create a ps1 (PowerShell script file) with the following:

#$searchPattern = '*.mdb'
#$searchPattern = '*.accdb'
#$searchPattern = '*.*db'
#$searchPattern = '*.xlsm'
$searchPattern = '*.*'
$scriptPath = (Split-Path $MyInvocation.MyCommand.Path -Parent) + '\' + $searchPattern
Dir $mypath | Unblock-File

and then save it as UnblockFiles.ps1

Next, I create a bat file with the following:

Powershell.exe -executionpolicy remotesigned -File "%~dp0UnblockFiles.ps1"

and save it under any name I like (I like making it match the ps1 file so I use UnblockFiles.bat, but its name truly isn’t important).

The nice thing here is the ps1 and bat files are both self-aware, in the sense that it extracts its own location when run ($MyInvocation.MyCommand.Path & %~dp0) . So, you can drop these 2 scripts into any folder without needing to modify them in any way and they will work.

This way, all you need to do is:

• Create a folder (or use an existing one) in which you will save downloaded, saved your e-mail attachments, ….
• Drop the ps1 and bat files in it.

Now, whenever you want to unblock file, save them to the folder and:

Run the bat file by double-clicking on it.

OR

Create a shortcut to the bat file, place it on your desktop and use it to unblock any files found within that folder.

Attemp 2 – Improved Version

I wasn’t quite satisfied with my PowerShell script from Attempt 1 and continued plugin away and finally came up with a more versatile version that can apply multiple filters and can recurse through sub-folders.  So if you want such capabilities feel free to use this ps1 code instead:

[String]$curDir = Split-Path $MyInvocation.MyCommand.Path -Parent
Get-ChildItem -Path $curDir -Recurse | Unblock-File

The -Recurse tells the script to recurse through the sub-folder files.

If you want to apply filter(s) to only unblock specific types of files, then we would need to add an  Where-Object{} filter and thus the code would become:

[String]$curDir = Split-Path $MyInvocation.MyCommand.Path -Parent
Get-ChildItem -Path $curDir -Recurse | Where-Object{ $_.Extension -in '.jpg', '.png'} | Unblock-File

where you simply add/remove extensions in the ‘-in’ section of the code.

The rest remains unchanged.  You use the same bat file and place the 2 files in the folder with the blocked files.  Then to run it, simply launch the bat file.

You can also create simple VBA subs like:

'PS_UnblockFile "C:\Users\Daniel\Desktop\Form_SplitFormSimulated\Form_SplitFormSimulated.mdb"
Sub PS_UnblockFile(ByVal sFile As String)
    Dim sCmd As String

    sCmd = "Unblock-File -Path '" & sFile & "'"
    Call PS_Execute(sCmd)
End Sub

and

'PS_UnblockFolder "C:\Users\Daniel\Desktop\Form_SplitFormSimulated\"        'All subfolders as well
'PS_UnblockFolder "C:\Users\Daniel\Desktop\Form_SplitFormSimulated\", False 'Do not process subfolders
'PS_UnblockFolder "C:\Users\Daniel\Desktop\Form_SplitFormSimulated\*.accdb" 'All subfolders, but only for accdb files
Sub PS_UnblockFolder(ByVal sFolder As String, Optional bIncludeSubFolders As Boolean = True)
    Dim sCmd As String
    
    If Right(sFolder, 1) <> "\" Then sFolder = sFolder & "\"
    If bIncludeSubFolders = True Then
        sCmd = "Get-ChildItem -Path '" & sFolder & "' -Recurse | Unblock-File"
    Else
        sCmd = "Get-ChildItem -Path '" & sFolder & "' | Unblock-File"
    End If
    Call PS_Execute(sCmd)
End Sub

and then run then by using my PS_Execute procedure found at:

VBA - Run PowerShell Command

Have you ever wanted to run a PowerShell command from within VBA and get back a value? Executing a PowerShell Command From VBA Before getting into returning a response, let first look at simply executing a command. There are many instances in which we simply want to run a PowerShell command and don’t need to…

Continue reading →

Group Policy

Microsoft has since, July 20th 2022, published information that this can be bypassed using Group Policies so the existing behavior remains and MOTW is not used to cripple files.  You can read their full article on the subject for further information:

Macros from the internet are blocked by default in Office - Deploy Office

Provides guidance for admins about how Office blocks macros in files from the internet.

VBScript Versions Of The Approaches

Below are VBScript versions of the VBA code available in the Demo database. This can be useful for automation routine, startup scripts, …, or even for giving users access to run themselves to unblock folders/files.

Using The SysInternals Streams Utility

To unblock an individual file, you can do something like:

Dim sCmd
Const sStreamsEXEPath = "C:\Testing\"  'Location of the SysInternals Streams*.exe
Const sFile = "C:\Testing\Databases\MyDb.accdb" 'File to unblock

Set oShell = CreateObject ("WScript.Shell") 
sCmd = "streams64 -nobanner -d """ & sFile & """"
oShell.run "cmd.exe /k cd " & sStreamsEXEPath & " & " & sCmd
Set oShell = Nothing

To unblock a folder’s content, you can do something like:

Dim sCmd
Dim bProcessSubFolders
Const sStreamsEXEPath = "C:\Testing\"  'Location of the SysInternals Streams*.exe
Const sFolder = "C:\Testing\Databases\" 'Folder to unblock

bProcessSubFolders = True 'True = Process subfolders, False = Only process original folder
Set oShell = CreateObject ("WScript.Shell") 
If bProcessSubFolders = True Then
	sCmd = "streams64 -nobanner -s -d """ & sFolder & """"
Else
	sCmd = "streams64 -nobanner -d """ & sFolder & """"
End If
oShell.run "cmd.exe /c cd " & sStreamsEXEPath & " & " & sCmd
Set oShell = Nothing

To unblock the folder’s content in which the VBScript file is located (self-aware), you can do something like:

Dim sCmd
Dim bProcessSubFolders
Dim sFolder
Const sStreamsEXEPath = "C:\Testing\"  'Location of the SysInternals Streams*.exe

sFolder = Left(WScript.ScriptFullName,(Len(WScript.ScriptFullName) - (Len(WScript.ScriptName) + 1))) 'Processes the folder in which the VBScript resides
bProcessSubFolders = True 'True = Process subfolders, False = Only process original folder

Set oShell = CreateObject ("WScript.Shell") 
If bProcessSubFolders = True Then
	sCmd = "streams64 -nobanner -s -d """ & sFolder & """"
Else
	sCmd = "streams64 -nobanner -d """ & sFile & """"
End If
oShell.run "cmd.exe /c cd " & sStreamsEXEPath & " & " & sCmd
Set oShell = Nothing

Using the File System Object (FSO)

To unblock an individual file, you can do something like:

Dim oFSO
Const ForWriting = 2
Const sFile = "C:\Testing\Databases\MyDb.accdb" 'File to unblock

Set oFSO = CreateObject("Scripting.FileSystemObject")
With oFSO.OpenTextFile(sFile & ":Zone.Identifier:$DATA", ForWriting, True)
	.Write ""
End With

To unblock a folder’s content, you can do something like:

Dim oFSO
Dim oFldr
Dim oSubFldr
Dim oFile
Dim bProcessSubFolders 
Const ForWriting = 2

Const sFolder = "C:\Testing\Databases" 'Folder to unblock
bProcessSubFolders = True 'True = Process subfolders, False = Only process original folder

Set oFSO = CreateObject("Scripting.FileSystemObject")
Call ProcessFolder(oFSO, sFolder, bProcessSubFolders)


Function ProcessFolder(oFSO, sFolder, bProcessSubFolders)
	Set oFldr = oFSO.GetFolder(sFolder)
	For Each oFile In oFldr.Files
		With oFSO.OpenTextFile(oFile & ":Zone.Identifier:$DATA", ForWriting, True)
			.Write ""
		End With
	Next
	
	If bProcessSubFolders = True Then
        For Each oSubFldr In oFldr.SubFolders
            Call ProcessFolder(oFSO, oSubFldr.Path, bProcessSubFolders)
        Next
    End If
End Function

To unblock the folder’s content in which the VBScript file is located (self-aware), you can do something like:

Dim oFSO
Dim oFldr
Dim oSubFldr
Dim oFile
Dim sFolder
Dim bListSubFldrs 
Const ForWriting = 2

bListSubFldrs = True 'True = Process subfolders, False = Only process original folder
sFolder = Left(WScript.ScriptFullName,(Len(WScript.ScriptFullName) - (Len(WScript.ScriptName) + 1))) 'Processes the folder in which the VBScript resides

Set oFSO = CreateObject("Scripting.FileSystemObject")
Call ProcessFolder(oFSO, sFolder, bListSubFldrs)


Function ProcessFolder(oFSO, sFolder, bListSubFldrs)
	Set oFldr = oFSO.GetFolder(sFolder)
	For Each oFile In oFldr.Files
		With oFSO.OpenTextFile(oFile & ":Zone.Identifier:$DATA", ForWriting, True)
			.Write ""
		End With
	Next
	
	If bListSubFldrs = True Then
        For Each oSubFldr In oFldr.SubFolders
            Call ProcessFolder(oFSO, oSubFldr.Path, bListSubFldrs)
        Next
    End If
End Function

PowerShell Unblocking Script

To unblock an individual file, you can do something like:

Dim sCmd
Const sFile = "C:\Testing\Databases\MyDb.accdb" 'File to unblock

sCmd = "Unblock-File -Path '" & sFile & "'"
Call PS_Execute(sCmd)


Sub PS_Execute(sPSCmd)
	Dim oShell
	
	Set oShell = CreateObject ("WScript.Shell") 
	sPSCmd = "powershell -command " & sPSCmd
	oShell.run sPSCmd, 0, True
	Set oShell = Nothing
End Sub

To unblock a folder’s content, you can do something like:

Dim bProcessSubFolders
Const sFolder = "C:\Testing\Databases\" 'Folder to unblock

bProcessSubFolders = True 'True = Process subfolders, False = Only process original folder

Call PS_UnblockFolder(sFolder, bProcessSubFolders)


Sub PS_UnblockFolder(sFolder, bIncludeSubFolders)
    Dim sCmd
    
    If Right(sFolder, 1) <> "\" Then sFolder = sFolder & "\"
    If bIncludeSubFolders = True Then
        sCmd = "Get-ChildItem -Path '" & sFolder & "' -Recurse | Unblock-File"
    Else
        sCmd = "Get-ChildItem -Path '" & sFolder & "' | Unblock-File"
    End If
    Call PS_Execute(sCmd)
End Sub

Sub PS_Execute(sPSCmd)
	Dim oShell
	
	Set oShell = CreateObject ("WScript.Shell") 
	sPSCmd = "powershell -command " & sPSCmd
	oShell.run sPSCmd, 0, True
	Set oShell = Nothing
End Sub

To unblock the folder’s content in which the VBScript file is located (self-aware), you can do something like:

Dim bProcessSubFolders
Dim sFolder

bProcessSubFolders = True 'True = Process subfolders, False = Only process original folder
sFolder = Left(WScript.ScriptFullName,(Len(WScript.ScriptFullName) - (Len(WScript.ScriptName) + 1))) 'Processes the folder in which the VBScript resides

Call PS_UnblockFolder(sFolder, bProcessSubFolders)


Sub PS_UnblockFolder(sFolder, bIncludeSubFolders)
    Dim sCmd
    
    If Right(sFolder, 1) <> "\" Then sFolder = sFolder & "\"
    If bIncludeSubFolders = True Then
        sCmd = "Get-ChildItem -Path '" & sFolder & "' -Recurse | Unblock-File"
    Else
        sCmd = "Get-ChildItem -Path '" & sFolder & "' | Unblock-File"
    End If
    Call PS_Execute(sCmd)
End Sub

Sub PS_Execute(sPSCmd)
	Dim oShell
	
	Set oShell = CreateObject ("WScript.Shell") 
	sPSCmd = "powershell -command " & sPSCmd
	oShell.run sPSCmd, 0, True
	Set oShell = Nothing
End Sub

So as you can see, it isn’t very hard to adapt VBA to run as VBScript, or vice versa!
 

Download a Demo Database

Feel free to download a 100% unlocked copy by using the link provided below:

Disclaimer/Notes:

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

Page History