Sheet to array

[gsmcconville]

Is it possible to copy data from a sheet in a different workbook, to an array?

I know it is possible to do it from within the normal workbook, such as

Dim Buffer() As Variant
Buffer = Range("A1:F10")

But the following does not

Dim Buffer() As Variant
Set WS = Workbooks("Debtors").Worksheets(1)
Buffer = WS.Range("A1:F10")

Is this possible without doing anything like copying one cell at a time, or copying to the current workbook first?

Just curious.

[jonh]

I think you need to include the ".xls" or whatever in the wb name. Both of these work for me..


a = Excel.Workbooks.Open(ActiveWorkbook.Path & "\data.xlsx").Sheets(1).Range("a1:b4")
For Each v In a
Debug.Print v
Next

b = Excel.Workbooks("data.xlsx").Sheets(1).Range("a1:b4")
For Each v In b
Debug.Print v
Next

[snb]

You don't need an array, nor to 'copy':


Workbooks("Debtors").Worksheets(1).Range("A1:F10")=Activeworkbook.Worksheets(1).Range("A1:F10").value

[gsmcconville]

Thanks, the problem was with the Dim statement.

If you remove that it works fine, or if you change it to

Dim Buffer as Variant

Not sure why, but as least I know what is possible