Copy data from macro enabled workbook

Question

I'm trying to copy data from a macro enabled workbook selected through file explorer into another workbook using VBA. The code I have so far is:

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

The code works fine for non_macro enabled workbooks, but when the enable macros pop up box opens when opening a macro enabled workbook and selection exits the macro. Not sure how to resolve!

Pᴇʜ Pᴇʜ · Accepted Answer · 2020-03-16T10:28:45

Did you try Application.DisplayAlerts = False right before the Workbooks.Open line? Don't forget to enable it afterwards (reset it to True).

Alternatively if the above doesn't work you can disable macros before Workbooks.Open and re-enable it afterwards:

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

Note that any macros in the opened file will not be able to run then.

Copy data from macro enabled workbook

1 Answers