Vba Macro To Create Power Query Connections For Any Table In Excel!

Super useful and well explained. Thanks Jon. 20 tables (some 40,000+ rows) not added to data table 52 seconds.

Can you do a video of the reverse of this? How to loop through a bunch of PQs, adding each one to a new table in a new sheet ….. super helpful though. Thank you!

Thanks Bro.... You made my day 😍😍😍

After running the macro, I tried to append, after appending, the close and load To is not showing . How do I view the appended files in the work book?

359 seconds to create 268 connections.

382 connections in 292 sec

This is awesome!

hi, I don't see the file in your blog. Can you please link it here?

Hi Jon. I am trying to get some data from Azure DB using just a select statement. Can you help me on this please??

Hi Jon, could you direct me to where I could find this Macro code? I am not finding it in the download. I thought it was just me but I see a few other comments asking the same thing? This is exactly what I need and I would really appreciate the code, thanks!

Hi Jon, when I try to run the code on Excel 2013 it gives me a compile error: User-defined type not defined and highlights the row Dim wq As Workbook. Any clue why it is happening? Thank you.

great sir, I was looking for it.

This is super useful especially when I have over 200 worksheets to create connections. However I couldn't find the Macro code on the other link, would you be able to send the Macro code?

Hi Jon. Thank you for the tutorial. It was a great explanación on how Power Queries work.

Wow!!! This tutorial is what I’ve been looking for, thanks to you sir.

By the way, I have a question, I hope you don't mind. What If, I want to exclude one table from the rest of all the table. What code do I need to change or add?

Once again thank you very much.

Good job! 👏

Thank you so much! It saves valuable time. I am getting an error when trying to add new connections:

"A query ti the name 'Append1' already exists."

- DEBUG:
'Add query

wb.Queries.Add Name:=sName, _
Formula:="let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""" & sName & """]}[Content]" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " Source"


Please advise!

How to open pq connect all table

how to open menu my macros

Hi Jon, I am wondering how to use VBA code to delete the Power Query cpnnections and Queries~ I tried to record the Macro but it fails. Thanks a lot~

Hi Jon.thanks for the demo.When I run the macro, I met some error “user-defined type not defined”.Could you help me to resolve the problem please? Thanks.

Hi Jon. How to increase speed load data by power query in vba? Thank you very much

Jon thank you so much! You have saved me weeks lost from my life!

Very good！I will t r y

Thank you!!!!

So helpful! 7 connections, added to Data Model in 58.3 seconds. Over 500,000 rows of data.

Hello Jon, great video!!! Could you please help me with a code that deletes all queries in a Workbook. Thank you very much.

4.2 seconds!

Thanks!

What if you wanted to insert multiple connections to a workbook? Is that possible? E.g. Connect to two DBs and for sheet one use DB1 and for sheet 2 use DB2? I'm struggling to find that option

Thanks in advance :)

52 tables 5.3 seconds.

Thanks a lot for the example, - as I was not able to find anything in relation! :)
I was mostly interested in conncection.add2 option.
The code looks almost the same as I was working on.
In demo file I found some nice ideas which will need to test, however, the example/demo file actually does not work on my Excel 2016, - no error notification is produced...
"0 connections have been created in xx sec", - even if I remove all connections before running macros.
So I am quite pessimistic on my future tests :(
P.S. I want to create simple pivot table via VBA, with Data Model as I need to count unique values (distinct count) in the projects.... The main problem, - source filename always changes and I cannot produce the proper connection string.

Hi Jon, I downloaded the excel file.. I deleted the template and move other sheet from other file.. its not functioning.. kindly correct my procedure..do I need to edit Marco code?

Hi Jon, many thanks for the video. My process was much slower than yours. 32 connections are created in 256 seconds. Any idea why it is very slow? Thanks and regards.

Hello Jon...we are expecting Containers that are carrying many parts and are arriving on various days to our plant..and i have a task of analyzing if quantity in In-Transit Containers, would be sufficient for production, if yes how long i can produce before expecting next container arrival...so we do this activity every day to ensure plant continues with production. if there is delayed arrival of containers ,we opt for Air freighting the parts to fill production gap...i have daily production rate, stock on hand (daily opening stock), Intransit container arrival date, qty....I would like to get your help...is there a way i can create dashboard through power query which can displays every part numbers and by when plant run out of parts considering above factors

Hi Jon,

I hope you are doing good. I work in VBA &
I need some help with a unique problem,
I want to extract data from a closed excel workbook in my Macro without opening it.

Problem with opening workbook is my files are heavy >90 MB & I need to open and close 8 files of that size. So for saving time, I think power query could help extract data faster.

Let me know your thoughts on this.

Thanks!

Hi Jon, thanks for this great piece of code. I want to create a large appended table with these new connections. Is there a way to automate this through either vba or PQ?

Hi Jon,
Very instructive video, thank you.
I do have a question for a problem that I can't seem to find a solution. Maybe it is something simple for you and you might be able to help me.
I have to retrieve data from an ODBC connection into an Excel tool based on userform parameters.
The number of records can easily reach over 1M where the Excel sheet can't hold the recordset result. I am using ADODB recordset within my Excel VBA.
I thought that maybe I could create a power query in the workbook from the recordset directly.
For you information, if I create an ODBC via Data > Get Data and load to the query and data model, it takes over 3 x longer than my ADODB recordset to the ODBC DSN. Users might be ok to wait for 20 seconds, but definitely not 60 seconds each time they want to get data.
I appreciate your help if you can.
Thank you

Hi. Thanks million

Is this for only ms Excel 2016 and above. I want to use in excel 13 but it doesn't work because queries property is no available

10 seconds - 9 connections

Thank you, Jon. Great help!

Hi John...great video...is there a way to use macro to edit existing connection...means the box where we define data source should come up..so that I can update the new link.

Kindly help

Thanks and regards
Kishore

Great stuff

I can not find the macro ? how can I download it?

Hi Jon.. great video and clever solution for saving a lot of time loading tables into Power Query. Looking forward to studying the code further. Thanks for sharing. Thumbs up!!

Thanks for your video, but I couldn't find the macro in the link.

Hi Jon, great videos. Could you please organise a playlist for users at a beginner/intermediate level with all the videos about all the basic stuff and knowledge a beginner/intermediate should watch first?
Thank you, keep up the good work

could you pleas helping me for this problem -graphs disappear after a save or save As in my excel 2016-

Hi Jon, great job. When is the seminar going to be conducted?