How to store the reference of Workbooks in an array.

[Store a reference of a excel workbook]

I have opend four workbooks. Now i wanted to store reference of each workbook
in an array so that i can use them later. Please see the below code, it is
throwing an error. Here i have opened a workbooks and stored it reference in
wb, now when i try to store wb in an array, it throws error.

Set arrWorkbooks(i)= wb

 

[Per Jessen]

Hi

Maybe this will help you:

Sub test()
Dim WbArr()
Dim i As Long
ReDim WbArr(Application.Workbooks.Count)
For Each wb In Application.Workbooks
WbArr(i) = wb.Name
i = i + 1
Next
End Sub

Regards,
Per

"Store a reference of a excel workbook"

 

[Store a reference of a excel workbook]

Hi Per Jessen, Thanks for your reply. In the below code you are storing the
name of the workbook in the array not references to the workbooks. How can i
reference a particular workbook with this array and copy the cells from that
workbook to another workbook?

 

[Per Jessen]

Hi

Activate the destination workbook and use this:

Workbooks(WbArr(i)).Sheets("Sheet1").Range("A1:A10").Copy _
Destination:= Sheets("Sheet2").Range("A1")

Regards,
Per

"Store a reference of a excel workbook"

 

[Tom Hutchins]

Or, we could use an object variable array and store references to the
workbooks themselves:

Sub test()
Dim WbArr() As Workbook, wb
Dim i As Long
ReDim WbArr(Application.Workbooks.Count)
For Each wb In Application.Workbooks
Set WbArr(i) = wb
MsgBox WbArr(i).FullName
i = i + 1
Next
End Sub

Be sure to free the memory later with
Set WbArr() = Nothing

Hope this helps,

Hutch