Microsoft’s Zone Identifier

Grrrrrrrr. There has been so many obstacles lately where things just don’t work. It’s been discouraging.

This time it is a Windows machine that won’t allow my macro to recognize files downloaded from the internet which is where our vendor site lies. Let’s call the person with a machine that won’t see the downloaded files “Ann”. Ann apparently got a new machine, so my macro suddenly didn’t work: the macro could no longer see the files downloaded from our vendor. But if Ann sent the files to me, the macro on my machine worked. Or, if I send her my downloads, the macro on her machine then worked. This scenario led me to suspect her machine is reading a file as either something safe or something dangerous. Microsoft is protecting Ann too well.

Then we tried using macros I created some time ago, and did a post here, to see if a new approach would work. Apparently, the one designed to work on the “Read Only” files led to nirvana – I couldn’t believe it. It was a cause for happy celebration.

But then on Monday, it didn’t work.

I should have questioned her more because the more I think about it,  the more I think it could be that she forgot to do some essential steps, but anyway, on with the story …

I tried to create macros that would use PowerShell to go into something called Zone Identifier and change the Zone Identifier from “an item from the internet” to “an item from own computer”. Normally I don’t like to do this kind of stuff because I don’t like to mess with security, but when Microsoft protects too well that we can’t do our job, then I’m forced to try to find a solution, especially when an easy solution is not available. Last time when I did a post on Microsoft protection, I hadn’t succeeded yet on creating such a macro. But this time I succeeded! (Of course with the help from the internet.)

Now there’s a new obstacle. Microsoft has declared that file with the new macro going into the Zone Identifier and reading it (never mind changing it) as a malicious virus.

Sigh.

Sigh.

Sigh.

---

Macro to get the Zone Identifier.

This one uses the PowerShell command.

Here are the meanings of the Zone Identifier:

• Zone Id = 0 own computer
• Zone Id = 1 local intranet zone
• Zone Id = 2 trusted sites zone
• Zone Id = 3 internet zone
• Zone Id = 4 restricted sites zone

Source of help: https://stackoverflow.com/questions/32101010/run-a-powershell-command-not-script-from-excel-vba

(Sorry, could not get the formatting to work in the code section. Copying over the code just brought in a ton of spaces. I haven’t figured out how to work this Gutenberg editor and it keeps changing as WordPress improves the product.)

[ap_divider color=”#CCCCCC” style=”solid” thickness=”4px” width=”100%” mar_top=”0px” mar_bot=”0px”]

Sub GetZoneId()

‘This macro requires that the files remain closed and that this file with the macro resides in the same folder as the files in question. This file with the program contains a tab called “Start here” and in cell B3 the directory is listed.

Dim retval As Variant

Dim pscmd As String

‘For text file

Dim txtfile, text, textline As String

Application.EnableEvents = False

Application.ScreenUpdating = False

Application.DisplayAlerts = False

 

‘******************NOTES************************************************************

‘ -noexit seems to keep open the Powershell screen, without it, the screen does not stay up

‘ retval does not give you the answers

‘ to get output, need to write it to text file and then read the text file.

‘***********************************************************************************

 

DirPath = Sheets(“Start here”).Range(“B3”)

Filename = Sheets(“Start here”).Range(“B6”) & “.xls”

FullCDirectory = DirPath & Filename

 

pscmd = “Powershell -Command “”Get-Content -Path ” & FullCDirectory & ” -stream Zone.Identifier |

   out-file ” & DirPath & “output.txt”””

retval = Shell(pscmd, vbNormalFocus)

 

txtfile = DirPath & “output.txt”

 

‘slow down the macro for 3 seconds so that output.txt is saved to directory

newHour = Hour(Now())

newMinute = Minute(Now())

newSecond = Second(Now()) + 3

waitTime = TimeSerial(newHour, newMinute, newSecond)

Application.Wait waitTime

 

‘Reading result in PowerShell into text file

Open txtfile For Input As #1

 

Do Until EOF(1)

    Line Input #1, textline

    text = text & textline

Loop

 

Close #1

 

MsgBox text

 

Kill DirPath & “output.txt”      ‘deletes file from directory

 

Application.EnableEvents = True

Application.ScreenUpdating = True

Application.DisplayAlerts = True

 

MsgBox (“Program done”)

End Sub

[ap_divider color=”#CCCCCC” style=”solid” thickness=”4px” width=”100%” mar_top=”0px” mar_bot=”0px”]

Macro to set the Zone Identifier.

The approach is different but I’ll take whatever will work

Source of help: https://stackoverflow.com/questions/12993350/accessing-ntfs-alternate-data-streams-with-vbscript

[ap_divider color=”#CCCCCC” style=”solid” thickness=”4px” width=”100%” mar_top=”0px” mar_bot=”0px”]

Sub SetZoneId()

‘For this macro version, don’t open the files. Leave them in the directory. Have this file in the same directory as those files that you want to change zones.

Dim MyObj As Object

Dim MySource As Object

Dim DirPath As String

Dim file As Variant

 

Application.EnableEvents = False

Application.ScreenUpdating = False

Application.DisplayAlerts = False

 

DirPath = Sheets(“Start here”).Range(“B3”)

 

Set MyObj = CreateObject(“Scripting.FileSystemObject”)

Set MySource = MyObj.GetFolder(DirPath)

 

For Each file In MySource.Files

    If file.Name <> “PERSONAL.XLSB” And file.Name <> “UnProtectView.xlsm” And file.Name <>

                 “~$UnProtectView.xlsm” Then

        FullCDirectory = DirPath & file.Name

        Set MyFile = MyObj.CreateTextFile(FullCDirectory & “:Zone.Identifier”)

        MyFile.WriteLine “[ZoneTransfer]” & vbNewLine & “ZoneId=3”

        MyFile.Close

    Else

        ‘do nothing

    End If

Next file

 

Application.EnableEvents = True

Application.ScreenUpdating = True

Application.DisplayAlerts = True

 

MsgBox (“Program done”)

End Sub

[ap_divider color=”#CCCCCC” style=”solid” thickness=”4px” width=”100%” mar_top=”0px” mar_bot=”0px”]

The following called “UnProtectView” is an Excel file. Another thing I need to figure out is how to upload my Excel file so you can see it on the screen. Ah, the joy of changes.