The code below is meant to import data from a defined table in another workbook to process in this workbook, after which it will clear the contents of the table in the source workbook.
It gives me the following error:
Object variable not set (Error 91)
I tried the following code to import the data into the array. I would expect to be able to interact with the data in the array after the other workbook has been closed.
Sub Test()
Dim arrPC As Variant
Dim PCwb As Workbook
Dim ws As Worksheet
Dim PCws As Worksheet
Set ws = ThisWorkbook.Sheets("MainSheet")
Set PCwb = Workbooks.Open("C:\Path\OtherWorkbook")
Set PCws = PCwb.Sheets("ImportSheet")
arrPC = PCws.ListObjects("ImportTable").DataBodyRange.Value
If not isnull(arrPC) Then
PCws.ListObjects("ImportTable").DataBodyRange.ClearContents
End If
PCwb.Close
End Sub
Forgive my inability to add this as in-code comment, but the code breaks on this line:
arrPC = PCws.ListObjects("ImportTable").DataBodyRange.Value
Import Data From Table in a Closed Workbook