How to copy column data from closed workbook with VBA

A user wants to copy columns of data from one workbook into another active workbook without opening the workbook. First we create an instance of the Excel application, open the workbook using the path of the workbook, copying the relevant data, closing this instance and finally pasting the copied data into our active workbook. Watch the training video and then study the VBA code:

Watch this video on YouTube.

The complete VBA code:

Sub CopyDataFromClosedWbk()
‘copy data from closed workbook to active workbook
Dim xlApp As Application
Dim xlBook As Workbook
Dim Sh As Object
Set xlApp = CreateObject(“Excel.Application”)
‘Path source workbook
Set xlBook = xlApp.Workbooks.Open(“C:\Users\takyar\Desktop\test-workbook.xlsx”)
xlBook.Sheets(1).Range(“B1:B19”).Copy
xlApp.DisplayAlerts = False
xlBook.Close
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
Set xlBook = ActiveWorkbook
Set Sh = xlBook.Sheets(“Sheet1”)
‘Sh.Activate
Range(“B1”).Select
Sh.Paste
Range(“A1”).Select

End Sub