Vlookup dynamically for multiple columns data from another closed workbook without opening it

Question

First off: On file1 > sheet1 - I have Ids of data on column A.

On source file - I have a huge data with multiple columns with same column of Ids on column A in sheet1.

I trying vlookup to get data for multiple columns from another closed workbook but result is coming only for one column. Also i don't want to open a source file as file size is bit heavy (approx. 600mb).

below are the code which i am using for above scenario. i know this code not is correct and need more correction. So can someone help me into this.

Sub MyMacro()
Dim rw As Long, x As Range, lastrow As Long, lastcol As Long
Dim book1 As Workbook, twb As Workbook

Set twb = ThisWorkbook

Set book1 = Workbooks.Open("C:\Users\Charles Paul\Desktop\VBA\12-Oct\Record.xlsx")
Set x = book1.Worksheets("Sheet1").Range("A:A")
   
With twb.Sheets("Sheet1")
  lastrow = x.cells(x.Rows.Count, x.Column).End(xlUp).Row
  lastcol = x.cells(x.Row, x.Columns.Count).End(xlToRight).Column

  For rw = 1 To .cells(Rows.Count, 1).End(xlUp).Row
    .cells(rw, 2) = Application.VLookup(.cells(rw, 1).Value2, x, 1, False)
  Next rw
End With

book1.Close savechanges:=False
End Sub

"as file size is bit heavy (approx. 600mb)." - best understatement of the day. — BigBen
I'd suggest moving the data into a database. Use MS Access if necessary - it will do just fine for a single table. They you can write a simple SQL query to return all the columns necessary and do away with VLookup and all its limitations entirely. — FreeMan

Thomas J. Thomas J. · Accepted Answer · 2020-10-15T13:49:48

For large data sets, you might want to look into power query.

It is accessible from here:

I will not get into details, as setting up a query is a separate thing, but you can manage it with relevant VBA code.

Vlookup dynamically for multiple columns data from another closed workbook without opening it

1 Answers