Remove protected view from Excel sheet opened programmatically in Access

Question

I have a spreadsheet I open programmatically using the VBA in Access:

Set xl = CreateObject("Excel.Application")
With xl
    Call RunASCFormatting(xl, wb, strPath)
    'More code

Sub RunASCFormatting(xl As Excel.Application, wb As Excel.Workbook, strPath As String)
    With xl
        If .ProtectedViewWindows.count > 0 Then
            .ActiveProtectedViewWindow.Edit
        End If
        Set wb = .Workbooks.Open(Trim(strPath) & "ASC.xls", True, False)
        wb.Sheets(1).Rows("1:1").Delete Shift:=xlUp
        .ActiveWorkbook.SaveAs FileName:=Trim(strPath) & "ASC.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    End With
End Sub

I have added in the "If" statement in the sub as I was hoping it would remove the "Protected View - Editing this file type is not recommended due to your File Block settings in the Trust Center" message. What I'm trying to achieve is to get the "Enable Editing" button removed, so this macro can enable editing and run as planned.

Currently, the code falls at the "Set wb" line. What is the proper way to achieve what I'm after?

Removing protected view needs user permission. It will be security breach if there is a way to remove it pragmatically without user intervention. — Kasim Husaini, Sep 17 '14 at 11:54
I'm fairly certain I've seen it done. I'm not concerned about the security issue here, as this is purely being done for me and one colleague. — Andrew Martin, Sep 17 '14 at 11:58

score 6 · Accepted Answer · answered Sep 17 '14 at 14:03

One possibility is to change the macro security settings programmatically to the lowest before you open the Excel workbook. After manipulating your data, re-enable the previous setting of the macro security.

Here's some revised code which I found at http://www.mrexcel.com/forum/excel-questions/631545-change-trust-center-settings-visual-basic-applications.html:

Public Sub MySubroutine()
    Dim lSecurity As Long

    lSecurity = Application.AutomationSecurity
    Application.AutomationSecurity = msoAutomationSecurityLow

    '''''''''''''''''''''
    '   Run code here   '
    '''''''''''''''''''''

    Application.AutomationSecurity = lSecurity
End Sub

As a side comment, VBA implements Integer as Long, so it could actually be slightly more performance degrading to declare Integer variables as it has to reinterpret the Integer keyword. When I learned that, I started declaring an Integer as Long instead. I actually read this in some Microsoft documentation, but I lost the link to it years ago.

Here is the link to support your statement: https://learn.microsoft.com/en-us/previous-versions/office/developer/office-xp/aa164754(v=office.10)?redirectedfrom=MSDN — jainashish, Feb 24 '20 at 02:14
It clearly says: there is no longer a performance advantage to using Integer variables; in fact, Long variables might be slightly faster because VBA does not have to convert them. — jainashish, Feb 24 '20 at 02:15

score 1 · Answer 2 · answered Feb 03 '17 at 06:34

Sub trusted_locations(path_to_add)

    Const HKEY_CURRENT_USER = &H80000001

    Dim oRegistry
    Dim sDescription        'Description of the Trusted Location
    Dim bAllowSubFolders        'Enable subFolders as Trusted Locations
    Dim bAllowNetworkLocations  'Enable Network Locations as Trusted
                    '   Locations
    Dim bAlreadyExists
    Dim sParentKey
    Dim iLocCounter
    Dim arrChildKeys
    Dim sChildKey
    Dim sValue
    Dim sNewKey
    Dim vers As Variant

'Determine the location/path of the user's MyDocuments folder
'*******************************************************************************
    Set oRegistry = GetObject("winmgmts:\\.\root\default:StdRegProv")
    bAllowSubFolders = True
    bAlreadyExists = False

    vers = Application.Version

    sParentKey = "Software\Microsoft\Office\" & vers & "\Excel\Security\Trusted Locations"

    iLocCounter = 0
    oRegistry.EnumKey HKEY_CURRENT_USER, sParentKey, arrChildKeys
    For Each sChildKey In arrChildKeys
        oRegistry.GetStringValue HKEY_CURRENT_USER, sParentKey & "\" & sChildKey, "Path", sValue
        If sValue = spath Then bAlreadyExists = True

        If CInt(Mid(sChildKey, 9)) > iLocCounter Then
                iLocCounter = CInt(Mid(sChildKey, 9))
            End If
    Next

'Uncomment the following 4 linesif your wish to enable network locations as Trusted
'   Locations
   bAllowNetworkLocations = True
   If bAllowNetworkLocations Then
           oRegistry.SetDWORDValue HKEY_CURRENT_USER, sParentKey, "AllowNetworkLocations", 1
   End If

    If bAlreadyExists = False Then
        sNewKey = sParentKey & "\Location" & CStr(iLocCounter + 1)

        oRegistry.CreateKey HKEY_CURRENT_USER, sNewKey
        oRegistry.SetStringValue HKEY_CURRENT_USER, sNewKey, "Path", path_to_be_added
        oRegistry.SetStringValue HKEY_CURRENT_USER, sNewKey, "Description", description_of_path

        If bAllowSubFolders Then
            oRegistry.SetDWORDValue HKEY_CURRENT_USER, sNewKey, "AllowSubFolders", 1
        End If
    End If
End Sub

This code can add trusted locations.....if you want to add network locations, please check with your administator about the security regarding network locations — Sivaprasath Vadivel, Feb 03 '17 at 06:35

score 0 · Answer 3 · answered Sep 17 '14 at 12:05

0

You can try turning off protected view settings in the Trust Center

http://office.microsoft.com/en-us/excel-help/what-is-protected-view-HA010355931.aspx#BM5

http://www.howtogeek.com/60310/enable-editing-for-all-office-2010-documents-by-disabling-protected-view/

This may be harmful.

Additionally you should set trusted locations.

answered Sep 17 '14 at 12:05

Kasim Husaini

392
3
14

2

This would really be a last resort. If I can do it programmatically, for just these few instances, it would be much preferred. – Andrew Martin Sep 17 '14 at 12:06

score 0 · Answer 4 · answered Feb 03 '17 at 06:28

Sub fileBlock(value As Long) Const HKEY_CURRENT_USER = &H80000001

Dim oRegistry
Dim sParentKey
Dim vers As Variant
Dim item As String: item = filetype_to_change_fileblock

'Determine the location/path of the user's MyDocuments folder '******************************************************************************* Set oRegistry = GetObject("winmgmts:\.\root\default:StdRegProv")

vers = Application.Version

sParentKey = "Software\Microsoft\Office\" & vers & "\Excel\Security\FileBlock"

oRegistry.SetDWORDValue HKEY_CURRENT_USER, sParentKey, item, value


End Sub

this code can change file block setting....the value gives the security level. no security =0 security for open =1 security for open and save=2 — Sivaprasath Vadivel, Feb 03 '17 at 06:29

Remove protected view from Excel sheet opened programmatically in Access

4 Answers4

Linked