Getting a range from a closed workbook into an array

Question

How do I get a range of data from a closed workbook into an array in VBA? Preferably without opening the workbook.

Thanks

Answer 1

If you're only interested in the data values in the closed workbook, you can use an empty range of your open workbook as a scratch area and then fill that range with an array formula:

ScratchWorksheet.Range("B1", "B3").FormulaArray = "='PathToClosedBook\[ClosedBook.xlsx]Sheet1'!$B$1:$B$3"

After you're finished you can delete the FormulaArray so you don't leave external links in the open workbook.

Answer 2

Not sure if you can do it without opening the workbook. I built a function like below.

Private Function GetValue(path, file, sheet, ref)
Dim wb As Workbook
Application.ScreenUpdating = False
Set wb = Workbooks.Open(path & Application.PathSeparator & file)
Worksheets(sheet).Activate
GetValue = Range(ref)
wb.Close savechanges:=False
Application.ScreenUpdating = True
End Function

Then use it like

Dim path As String
Dim file As String
Dim sht As String
Dim rng As String
path = ThisWorkbook.path
file = "book.XLSX"
sht = "Sheet1"
rng = "A1:D300"

ActiveSheet.Range("A1:D300").Value = GetValue(path, file, sht, rng)