时间戳

首页 > 解决方案 > 从启用宏的工作簿复制数据

excel - 从启用宏的工作簿复制数据

问题描述

我正在尝试将通过文件资源管理器选择的启用宏的工作簿中的数据复制到使用 VBA 的另一个工作簿中。我到目前为止的代码是：

Dim wbThisWB    As Workbook
Dim wbImportWB  As Workbook
Dim strFullPath As String
Dim lngLastRow  As Long
Dim lngLastCol  As Long

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Please select a file to open:"
    .Show
    On Error Resume Next 'In case the user has clicked the <Cancel> button
        strFullPath = .SelectedItems(1)
        If Err.Number <> 0 Then
            Exit Sub 'Error has occurred so quit
        End If
    On Error GoTo 0
End With

Set wbImportWB = Workbooks.Open(strFullPath)
'code here to copy and paste tab from Import WB into the current workbook
    On Error Resume Next 'In case there's no data or tab doesn't exist
    With wbImportWB.Sheets("PSE Report")
        lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        lngLastCol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
        If lngLastRow > 0 And lngLastCol > 0 Then
            'If the 'lngLastRow' and 'lngLastCol' variable have been set there's data to be copied.
            'The following copies the entire range from tab 'PSE Data' in the import workbook to cell A1 in 'Sheet1' of this workbook.
            Range(.Cells(1, 1), .Cells(lngLastRow, lngLastCol)).Copy wbThisWB.Sheets("PSE Data").Cells(1, 1)
        End If
    End With
On Error GoTo 0

wbImportWB.Close False 'Close the Import WB without saving any changes.

Set wbThisWB = Nothing
Set wbImportWB = Nothing

Application.ScreenUpdating = True

该代码适用于非启用宏的工作簿，但是当打开启用宏的工作簿时打开启用宏弹出框并且选择退出宏时。不知道怎么解决！

标签： excelvbacopy

解决方案

你在排队Application.DisplayAlerts = False前试过吗？Workbooks.Open之后不要忘记启用它（将其重置为True）。

或者，如果上述方法不起作用，您可以先禁用宏，Workbooks.Open然后再重新启用：

Dim PreviousSecurity As Long
PreviousSecurity = Application.AutomationSecurity
Application.AutomationSecurity = msoAutomationSecurityForceDisable
' Your code Workbooks.Open
Application.AutomationSecurity = PreviousSecurity

请注意，打开文件中的任何宏都将无法运行。

推荐阅读