Hello Fatima, the file is currently zip you can find it under "Download the Excel File with Macro". You can find it here: www.excelcampus.com/vba/power-query- connection-only-all-tables
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 :)
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
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.
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, 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
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!!
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.
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!
Ah I found my answer! The link in description takes you to the wrong article. If you search the article on the Excel Campus Website with the same name as this video, you can find the download that includes the macro code.
Hi Jon, great demo and thanks for the code, which I will use extensively :) By the way: I noticed you stripped the data model chunk from the xlsm file you provided; I've tried to replicate it but couldn't get it to work properly: although the syntax is correct (I've compared my code to yours and to the output of the macro recorder), the query wouldn't be added to the data model. Even running again the code produced by the macro recorder doesn't work: no errors but the query stays "connection only". What am I missing? Thanks again! :-)
Hi Enrico, Sorry about that. That was an old version of the file. I added the data model functionality later on. I just uploaded the latest version that contains the data model option. Here is the link to the post where you can download it. www.excelcampus.com/vba/power-query-connection-only-all-tables/ Thanks again and have a nice day! 🙂
@@ExcelCampus Thanks a lot. There's still something wrong, which I can't quite point my finger on yet. Tables are added to the data model indeed, but it's the tables themselves, not the queries which point to each table. Those are still "connection only", and can't be manually refreshed. I'd like to be able to mimic the behaviour of "close and load to -> connection only (add to data model)", so that if I run further transformations inside my queries, I will find them in my data model. I'll do some further investigation, but you gave me a hell of a head start! Thanks again, have a nice day! BTW your channel is truly awesome :)
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!
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
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!
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.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, 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?
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.
Hi Mohamed, Sure thing! We actually a video on that macro. Here is the link. ua-cam.com/video/soVOGldB-Mo/v-deo.html I hope that helps. Thanks again and have a nice day! 🙂
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.
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
hi, I don't see the file in your blog. Can you please link it here?
Hello Fatima, the file is currently zip you can find it under "Download the Excel File with Macro". You can find it here: www.excelcampus.com/vba/power-query-
connection-only-all-tables
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!
Did you already get an answer to this? This is the error I'm getting as well
359 seconds to create 268 connections.
So helpful! 7 connections, added to Data Model in 58.3 seconds. Over 500,000 rows of data.
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 :)
Hello Jon, great video!!! Could you please help me with a code that deletes all queries in a Workbook. Thank you very much.
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
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.
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?
Did you find the Macro code? I could not find it either.
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?
Hi Jon. I am trying to get some data from Azure DB using just a select statement. Can you help me on this please??
I keep trying to download the workbooks to get the macro, unfortunately there are no modules in the file when I do so. Any suggestions?
Thanks for your video, but I couldn't find the macro in the link.
Hi Jon. Thank you for the tutorial. It was a great explanación on how Power Queries work.
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
Great suggestion! We'll try to work on this soon. 😀
I am unable to get the code from the excel file in your website. Is there any other way to find the code
Hi John. How to custom ribbon my macros like in your video?
Super useful and well explained. Thanks Jon. 20 tables (some 40,000+ rows) not added to data table 52 seconds.
Thanks! 😀
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!!
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.
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!
I tried just typing it based off the video but the whole code is not visible in certain sections.
Ah I found my answer! The link in description takes you to the wrong article. If you search the article on the Excel Campus Website with the same name as this video, you can find the download that includes the macro code.
great sir, I was looking for it.
Hi Jon, great demo and thanks for the code, which I will use extensively :) By the way: I noticed you stripped the data model chunk from the xlsm file you provided; I've tried to replicate it but couldn't get it to work properly: although the syntax is correct (I've compared my code to yours and to the output of the macro recorder), the query wouldn't be added to the data model. Even running again the code produced by the macro recorder doesn't work: no errors but the query stays "connection only". What am I missing? Thanks again! :-)
Hi Enrico,
Sorry about that. That was an old version of the file. I added the data model functionality later on. I just uploaded the latest version that contains the data model option. Here is the link to the post where you can download it.
www.excelcampus.com/vba/power-query-connection-only-all-tables/
Thanks again and have a nice day! 🙂
@@ExcelCampus Thanks a lot. There's still something wrong, which I can't quite point my finger on yet. Tables are added to the data model indeed, but it's the tables themselves, not the queries which point to each table. Those are still "connection only", and can't be manually refreshed. I'd like to be able to mimic the behaviour of "close and load to -> connection only (add to data model)", so that if I run further transformations inside my queries, I will find them in my data model. I'll do some further investigation, but you gave me a hell of a head start! Thanks again, have a nice day! BTW your channel is truly awesome :)
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!
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
Very good！I will t r y
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~
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!
PQ can combine all your tables from all sheets automatically. You don't need a macro for that.
how can we do so....?
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 tony, did you figure out how to do this?
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, 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?
Thank you, Jon. Great help!
Hi Jon. How to increase speed load data by power query in vba? Thank you very much
Full link as it is cut off in the description: www.excelcampus.com/vba/power-query-connection-only-all-tables
Really nice...
Hi. Thanks million
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.
Jon thank you so much! You have saved me weeks lost from my life!
Added your script to my web scraper and ran 239 connections in 48.8 seconds. 22,448 cells total (appended) So sick man!
This is awesome!
382 connections in 292 sec
i saw you have a macro for getting unique values, care to make a video about it?
Hi Mohamed,
Sure thing! We actually a video on that macro. Here is the link. ua-cam.com/video/soVOGldB-Mo/v-deo.html
I hope that helps. Thanks again and have a nice day! 🙂
Thank you!!!!
Thanks Bro.... You made my day 😍😍😍
Glad to hear that @talhaakram3621 😀
Good job! 👏
could you pleas helping me for this problem --graphs disappear after a save or save As in my excel 2016--
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
Hola t
Hola Tonmoy. Lograstes conseguir la solucion? Yo estoy tratando de hacer lo mismo pero no me aparece esa propiedad
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.
Try changing the source file from Excel files to CSB files... for some reason PQ works much faster with CSV files.
Can we have a one-on-one tutorial class on data mining and scraping
Hi Uchenna,
I'm sorry, we don't do one-on-one training at this time.
Great stuff
Hello Jon thank you so much for sharing your amazing skills.
You're welcome! 😀
How to open pq connect all table
how to open menu my macros
52 tables 5.3 seconds.
4.2 seconds!
10 seconds - 9 connections
Hi Jon, great job. When is the seminar going to be conducted?
Hi Atta, it will happen this week. Here's the link to register to the webinar excelcampus.easywebinar.live/registration-blueprint.
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
You should make a dynamic Path to change connections. If you search John's UA-cam Playlist you will find a video on this.