vba 从在 Access 中以编程方式打开的 Excel 工作表中删除受保护的视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25889742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Remove protected view from Excel sheet opened programmatically in Access
提问by Andrew Martin
I have a spreadsheet I open programmatically using the VBA in Access:
我有一个电子表格，我使用 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
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.
我已经在子中的“If”语句中添加了，因为我希望它会删除“受保护的视图 - 由于信任中心中的文件阻止设置，不建议编辑此文件类型”消息。我想要实现的是删除“启用编辑”按钮，以便此宏可以按计划启用编辑和运行。
Currently, the code falls at the "Set wb" line. What is the proper way to achieve what I'm after?
目前，代码位于“Set wb”行。实现我所追求的正确方法是什么？
回答by Bobort
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.
一种可能性是在打开 Excel 工作簿之前以编程方式将宏安全设置更改为最低。操作数据后，重新启用宏安全的先前设置。
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:
这是我在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.
作为旁注，VBA 将 Integer 实现为 Long，因此声明 Integer 变量实际上可能会稍微降低性能，因为它必须重新解释 Integer 关键字。当我了解到这一点时，我开始将 Integer 声明为 Long。我实际上是在一些 Microsoft 文档中读到的，但几年前我失去了它的链接。
回答by Sivaprasath Vadivel
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
回答by Kasim Husaini
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://office.microsoft.com/en-us/excel-help/what-is-protected-view-HA010355931.aspx#BM5
This may be harmful.
这可能是有害的。
Additionally you should set trusted locations.
此外，您应该设置受信任的位置。
回答by Sivaprasath Vadivel
Sub fileBlock(value As Long) Const HKEY_CURRENT_USER = &H80000001
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")
'确定用户的 MyDocuments 文件夹的位置/路径 '**************************************** ***************************************** 设置 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
