Unblocking Blocked Files Via Macros

A couple of months ago, our managers started to have problems with their Excel files from our 3rd Party Vendor: the Excel downloads were invisible to my macros so our reporting system was failing. Our tech guy and I think the change had something to do with Microsoft updating their operating system to combat security issues and our vendor was not keeping up. What was really weird was that when the files were open, we did not receive the security message that the file was in Protected View or anything like that. Instead, our tech guy said that the files were opening up as a separate instances of Excel rather than all being opened in the same Excel instance, so he suggested opening the first file and then from there, open the remaining files using File-Open.

It has been working until possibly this past Monday when one of the managers said the macro was giving her errors. I’m hoping it was just a fluke but I vaguely recall this manager as being one of the first ones to have problems with the vendor downloads. I don’t know for sure, but as a precaution, I’ve been working this week on figuring out another work around, although I thought the solution from the tech guy was really good.

The problem is: on my machine I just don’t have the problem…yet, so it’s really hard to create a solution to a problem that your machine won’t produce. I tried to fake produce what I thought could be happening. I tried emailing an Excel file from the vendor to myself and then copying the file from the email to my desktop but my machine still wouldn’t apply the protection. My machine recognized the file as one I produced and saved onto my machine.

And, nope, my Trusted Center is set up to flag files from the internet and emails with Protection Mode. It’s just that my machine somehow recognizes that the vendor site is a trusted site and the emails from myself is well, from myself. My machine knows.

I finally came up with a method where the file opens up as either Read Only or in Protected Mode: do File-Open, go to the folder where the files you want to open are located, and then instead of clicking on the Open button, click on the down arrow and choose either Open Read Only or Open in Protected View.

From there, I did research to craft a macro that would turn a Read Only file into ReadWrite and turn off the Protected Mode.

Changing Read Only to Read/Write

You can Google to find codes on how to change a file that is read only to read/write. Here’s a link to a web page:

https://www.mrexcel.com/forum/excel-questions/666983-vba-changing-file-read-only.html

Here’s another one that has code to determine if the file is read only or note:

https://stackoverflow.com/questions/20412010/vba-check-if-a-read-only-workbook-is-opened

Off to the right is the code I ended up with. The part about PERSONAL.XLSB is only if your desktop has personal macros running in the background. You can take out that part if you do not have personal macros. Also the line with enabling events can be taken out if you don’t have event macros starting up whenever you make a change in the files. (Due to my past work, I have personal macros that will start up an event macro whenever a change is made in file – which is just about all the time – so I have to turn it off.) So the lines of code in green you can take out if you don't need them.

Sub UnProtectView()
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim bk As Workbook, w As Long, wCount As Long
Dim wbDest As Workbook

Set wbDest = ActiveWorkbook

wCount = Application.ProtectedViewWindows.Count
On Error Resume Next
If wCount > 0 Then

For w = 1 To wCount + 1
Application.ProtectedViewWindows(w).Activate
wname = Application.ProtectedViewWindows(w).Workbook.Name
If wname <> "UnProtectView.xlsm" Then
Application.ProtectedViewWindows(1).Edit
End If
Next w

End If

On Error GoTo 0

wbDest.Sheets("Code").Activate (there is a sheet called "Code" in my file where there are two buttons that initiates the macro when pressed)

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox ("Program done")

'If wCount > 0 Then
' wname1 = Application.ProtectedViewWindows(1).Workbook.Name
' wname2 = Application.ProtectedViewWindows(2).Workbook.Name
' wname3 = Application.ProtectedViewWindows(3).Workbook.Name

' For w = 1 To wCount + 1
' wCount2 = Application.ProtectedViewWindows.Count
' wname1 = Application.ProtectedViewWindows(1).Workbook.Name
' wname2 = Application.ProtectedViewWindows(2).Workbook.Name
' wname3 = Application.ProtectedViewWindows(3).Workbook.Name
' Application.ProtectedViewWindows.Item(w).Activate
' wname = Application.ProtectedViewWindows.Item(1).Workbook.Name
' If wname <> "UnProtectView.xlsm" Then
' Application.ProtectedViewWindows.Item(1).Edit
' End If
' Next w
'End If
'
End Sub

Changing Protected View to "UnProtected View"

The code to the left is to “unprotect view” the file. The code I found gave me a partial answer but it was deceiving. The line with the code Application.ProtectedViewWindows(w).Workbook.Name fooled me because I thought I had to use Application.ProtectedViewWindows(w).Edit to mark the file as “editable”, similar to “Enable Editing” in the security message that you get when you have a file in Protected View. What this program does is count how many open files are in Protected View, and then go through each Protected View file w and make them editable. The problem was the program would “unprotect view” only one open file rather than all of the open files.

The culprit was Application.ProtectedViewWindows(w).Edit. It needed to be Application.ProtectedViewWindows(1).Edit, but you are not going to find that answer with an explanation anywhere, at least I didn’t when I Googled. In the site where I got the code, the person asking for help had the same problem I did and never got an answer. I finally figured it out by using the code that is marked as comment beneath the main code. If you go through that part of the code using the step in method (of course, you have to take off the comment ‘ symbol to make the lines operational), you will start to see why (1) is to be used instead of (w).

Continue down below

So, this is all well and good but my real issue is the files were opening on the manager’s desktop without showing any security messages and were remaining hidden to my macros. There was something unique going on. I really wanted to tackle the problem where the files were being marked as “blocked”. If you go to File Explorer, and right click on a file that was downloaded from the internet, click on Properties and then at the bottom of the General tab, there will be a box for “Blocked”. This box and “Blocked” appears only when the file is in some kind of protection mode. When I used File-Open Open Read Only or File-Open Open in Protected View, the file was not blocked. I needed a file that was blocked.

Why? These managers are not Excel savvy and for some, it takes a lot of explaining about right clicking on a file, looking for Properties, and then checking for “Blocked” in the General tab. And it’s just a bit more of a hassle for them. They don’t use computers much, at least some don’t. So I needed something simpler.

Turning Files into Blocked Files

I finally found a method of turning an unblocked file into a blocked file. It involves using powershell in cmd, and so it gets “geeky”. Rather than go into a full blown explanation, I will just show some of the command lines used to turn an unblocked file into blocked file. For a fuller explanation of what is going on, please go to this website:

https://blogs.technet.microsoft.com/askcore/2013/03/24/alternate-data-streams-in-ntfs/

Once I was able to induce a blocked file, it turns out that the two macros (the read only to read-write and the unprotect view) still work. So, come Monday, if that manager still has problems, I will send her a file with two buttons that will run the two macros. Hopefully, she won’t have any problems but if she does, I can test the programs to see if they work on her machine.

Powershell Instructions

Save file into folder, possibly just below 5th level or at 6th level. Also, don't name the folder or file with blanks. It's either the level or the folder name that gives problems in powershell.
Example: C:\Users\myfolder\Document\Veronique\test
Go to Start or search bar and type "cmd" (without quotes) to bring up the black screen.
Type "powershell" to get you into powershell mode
To set the file as blocked do:
set-content file.name -stream zone.identifier
Value[0]: [ZoneTransfer]
Value[1]: ZoneId=3
Value[2]:
To see if the file has a zone identifier type: Get-Item -Path C:\Users\myfolder\Documents\Veronique\test\filename
.xls -stream *
To figure out what zone the zone identifier is: Get-Content -Path C:\Users\myfolder\Documents\Veronique\test\filename
.xls -stream Zone.Identifier

Value Settings for Zone.Identifier

0     My Computer
1     Local Intranet Zone
2     Trusted sites Zone
3     Internet Zone
4     Restricted Sites Zone