从Access中以编程方式打开的Excel工作表中删除受保护的视图

Question

我有一个电子表格，我使用Access中的VBA以编程方式打开：

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

我在子中添加了"如果"语句，因为我希望它能删除"受保护的视图-由于信任中心中的文件块设置，不建议编辑此文件类型"消息。我试图实现的是删除"启用编辑"按钮，这样这个宏就可以启用编辑并按计划运行。

目前，代码位于"Set wb"行。实现我追求的目标的正确方法是什么？

Answer 1

一种可能性是在打开Excel工作簿之前以编程方式将宏安全设置更改为最低设置。操作数据后，请重新启用以前的宏安全设置。

这是我在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

作为附带说明，VBA将Integer实现为Long，因此声明Integer变量实际上可能会稍微降低性能，因为它必须重新解释Integer关键字。当我了解到这一点时，我开始将Integer声明为Long。事实上，我在一些微软文档中读到了这一点，但几年前我就丢失了它的链接。

Answer 2

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:\.rootdefault:StdRegProv")
    bAllowSubFolders = True
    bAlreadyExists = False
    vers = Application.Version
    sParentKey = "SoftwareMicrosoftOffice" & vers & "ExcelSecurityTrusted 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

Answer 3

您可以尝试关闭信任中心中的受保护视图设置

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/

这可能是有害的。

此外，您应该设置受信任的位置。

Answer 4

子文件块（值为长）Const HKEY_CURRENT_USER=&H80000001

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

'确定用户的MyDocuments文件夹的位置/路径'*******************************************************************************设置oRegistry=GetObject（"winmgmts:\.\root\default:StdRegProv"）

vers = Application.Version
sParentKey = "SoftwareMicrosoftOffice" & vers & "ExcelSecurityFileBlock"
oRegistry.SetDWORDValue HKEY_CURRENT_USER, sParentKey, item, value

End Sub