Connect Listobject to QueryTable and To Power Query

[jaryszek]

Hi Guys,

I have about 250 tables in Power Query and Excel started to have delay when inputting data. Which i have to somehow workaround.

Model is working like:
1. Downloading all tables from my organization internal sever.
2. When table is already in worbook - just refresh data.
3. If not, add new worksheet and new table and create new workbook connection to power query.

So i was thinking to delete not necessarry queries.
So it could work like :
1. If query not necessarry in workbook -> delete just power query.
2. While importing data check if listobject created previously exists, if yes add query table and connect to power query.

In attachement i added example query.
Now delete power query. How to recreate connection between listobject created by power query and power query?

It is possible via VBA?
Best,
Jacek

[jaryszek]

I am wondering if i will not use:

ADODB.connection and
ADODB.recordset as a replacement for Power Query.

Best,
Jacek

[jaryszek]

Hi Guys,

so it is not possible? I have to create once again listobject and query table with connection ?

Best,
Jacek

[jaryszek]

Ok Guys,

it is not possible, but there is workorund.

Let me know if this works for you:

Change CommandText for Workbook.OldeDBConnection to for example "Select * From 1" and disable query refresh with Code,
now you can delete power query.
When you will recreate power query you can change CommandText to "Select * From" & [QueryName] and enable query refreshing. And it is working!

So you do not have to keep all 250 power queries in your workbook - it will be terrible slow! You can just save Power M language code for each your query and recreate them when needed...

Hope i help.

Best,
Jacek