Set ListObject - Table on non active worksheet 1

[MattGreer]

I have some code that parses through a table to insert values into a query to pull data from MS Access.

Using Office 365/Excel 365.

How do I set the table without having to use the ActiveSheet command?

Here's the bits of code in play, so to speak:

Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects("Data_Table")

Is there a way to set the table without using "ActiveSheet"?

I tried:

Set tbl = ThisWorkbook.ListObjects("Data_Table")
Set tbl = ListObjects("Data_Table")

which both returned errors.



Thanks!!


Matt

 

[MattGreer]

I suppose I could just include the command:

ActiveWorkbook.Worksheets("Worksheet Name").Activate

But there should be a way to read off data from a table not on the active worksheet, right??



Thanks!!


Matt

 

[combo]

In most cases in excel VBA you can access object without activating or selecting it. The full path for ListObject: Excel.Workbook.Worksheet.ListObject, depending on the starting point (automation, current workbook, other workbook) you can skip some first part.
For the listobject and workbook with calling code:
[tt]Set tbl = ThisWorkbook.Worksheets("SheetName").ListObjects("TableName")[/tt]
Forum dedicated to other than access VBA: forum707


combo

 

[MattGreer]

Ack. of course. I saw "VBA" and just started posting, heh.


Thanks!!


Matt