Clever Geek Handbook

Remove protected view from Excel worksheet programmatically opened in Access

I have a spreadsheet that I open programmatically using 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 added in the "If" statement in the sub as I hoped it would remove the "Protected View - Editing this file type is not recommended due to your Fileblock settings in the Trust Center post." What I am trying to achieve is to remove the Allow Editing button so that this macro can allow editing and execute as planned.

Currently, the code ends up on the "Install wb" line. What's the correct way to achieve what I'm after?

+3


source to share


4 answers


One possibility is to programmatically change the macro protection settings to the lowest level before opening an Excel workbook. After processing the data, re-enable the previous macro protection setting.

Here are some corrected codes 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 might actually be slightly more demeaning to declare Integer variables as it has to reinterpret the Integer keyword. When I found out about this, I started declaring Integer as Long instead. I actually read this in some Microsoft docs, but I lost reference to it a few years ago.

+5


source


You can try disabling protected browsing settings in 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 can be harmful.

In addition, you must set up trusted locations.

0


source


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

'Specify the location / path to the user's MyDocuments folder "*************************************** ********* ***************************** Install 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
0


source 


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 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
0


source






More articles:


All Articles