Reading CSV into listobject query table

Hi Guys,

i want to do 2 things:
1) Import CSV table in format like:

"Col1","Col2","Col3"
"1","2",""
"","3","4"

into Excel activesheet.

2) If listobject not exists just create new listobject.

3) If listobject exists just refresh whole table to get the same result like in CSV (for example data added, column deleted and so on).

I tried with ADODB object :

Please Login or Register  to view this content.

but it failed for me - the varCsvData variant array is empty.

and i tried this code:

Please Login or Register  to view this content.

but i am getting:

Screenshot_106.png

Maybe i could use somehow power query formula through VBA to change it dynamically and use query?
I do not know,
please help,
Best Wishes,
Jacek

Thank you Rory!

I did what you seggested and have code like here:

Please Login or Register  to view this content.

it worked!

But i have issues with adding this query to listobject. How can i do this?
Tried code from here:

Please Login or Register  to view this content.

but it is not working for me.
Plus code .Name = "Contact List" didnt work for me (is is Connection instead of "Contact List") (see attachemnt).

And now checking if QT exists can be with code below?:

Please Login or Register  to view this content.

Best wishes,
Jacek

Yes, so maybe i should create listobject first?

Thank you.

I was thinking about it but it seems to be more work - check if queries exists if not delete, refresh all connections plus i will have dynamic paths...

In my code only listobject add query table and check if they exists.
Can you pelase help with listobject query table add method ?

Jacek

This will be a part of source data for me and i will be refering to tables from formulas using index,match.

Jacek

Ok i think i figured this out:

Please Login or Register  to view this content.

Ok but i can not close the topic yet.

What i faced is to refresh existing QryTable:

Please Login or Register  to view this content.

This code: qt.Refresh BackgroundQuery:=False doesnt work.
I mean it is not refreshable query. Why ?

Thank you Rory,

sorry - now i remember.

But still need to know few things (the 2nd not working as i want to)

Please Login or Register  to view this content.

1) refreshable query with link to source - it is created only via Power Query or MS Query or do we have other possibilities?

2) After running code i can no open csv file when Excel is opened - for sure some not released object causing it. I tried to set recordset as nothing but didnt work. How cna i do this?

Please help,
Best wishes for you in New Year!
Jacek
Jace