从Access中以编程方式打开的Excel工作表中删除受保护的视图 [英] Remove protected view from Excel sheet opened programmatically in Access

问题描述

我有一个电子表格，我可以通过Access中的VBA以编程方式打开该电子表格:

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

我已在子目录中的"If"语句中添加了它，因为我希望它可以删除受保护的视图-由于您在信任中心"中设置了文件阻止"，因此不建议编辑此文件类型.我要实现的目标是删除启用编辑"按钮，以便该宏可以启用编辑功能并按计划运行.

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.

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

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

推荐答案

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

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.

这是一些修改后的代码，我在 http://www.mrexcel.com/forum/excel-questions/631545-change-trust-center-settings-visual-basic-applications.html :

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

作为附带说明，VBA将Integer实施为Long，因此声明Integer变量实际上可能会稍微降低性能，因为它必须重新解释Integer关键字.当我了解到这一点后，我开始声明一个Integer为Long.我实际上在一些Microsoft文档中阅读了此内容，但几年前却丢失了指向它的链接.

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.

这篇关于从Access中以编程方式打开的Excel工作表中删除受保护的视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！