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

Рет қаралды 79,237

Күн бұрын

Sign up for our Excel webinar, times added weekly: www.excelcampus.com/blueprint...
This video explains how to use a macro that I created to speed up the process of creating connections for all tables in a workbook. This is helpful when you are trying to merge or append tables in Power Query and don’t want to create connections one by one. This macro is free for you to copy and use.
The VBA code for the macro can be found in the corresponding blog post for this video on my website. You can find it here: www.excelcampus.com/vba/power...
connection-only-all-tables
Related Videos:
How to Combine Excel Tables or Worksheets with Power Query:
• How To Combine Excel T...
Power Query Overview - Automate Data Tasks in Excel & Power BI:
• How To Automate Data T...
How to Install Power Query in Excel 2010 or 2013 for Windows:
• How To Install Power Q...
How to Create a Personal Macro Workbook in Excel and Why You Need It:
• How to Create a Person...
How to Create Yes/No Message Boxes for VBA Macros:
• How to Add a Yes No Po...
How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar:
• How to Add Macro Butto...
Webinar: Free Excel Training on The Modern Power Tools
excelcampus.easywebinar.live/...
#MsExcel #ExcelCampus
00:00 Introduction
00:51 Run the Macro
02:20 VBA Editor

Пікірлер: 77

@lmulpagano 3 жыл бұрын

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

@wayneedmondson1065 4 жыл бұрын

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!!

@jorgeflores1045 2 жыл бұрын

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

@bhakthasingh8198 3 жыл бұрын

Thank you, Jon. Great help!

@shivabernstein Жыл бұрын

This is awesome!

@khalidmajeed2886 2 жыл бұрын

great sir, I was looking for it.

@keithers7029 2 жыл бұрын

Good job! 👏

@mtstans 2 жыл бұрын

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

@mtstans 2 жыл бұрын

Added your script to my web scraper and ran 239 connections in 48.8 seconds. 22,448 cells total (appended) So sick man!

@kvishwajit 4 жыл бұрын

Great stuff

@hervemercier-gl6bp 3 ай бұрын

Hello Jon thank you so much for sharing your amazing skills.

@ExcelCampus 3 ай бұрын

You're welcome! 😀

@ShimadaConsultor 2 жыл бұрын

Thank you!!!!

@AjaySingh-ll5qw 4 жыл бұрын

Really nice...

@bennicholls8384 7 ай бұрын

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

@ExcelCampus 7 ай бұрын

Thanks! 😀

@Gbv95 4 жыл бұрын

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

@ExcelCampus 4 жыл бұрын

Great suggestion! We'll try to work on this soon. 😀

@sisirakumara726 3 жыл бұрын

Hi. Thanks million

@talhaakram3621 9 ай бұрын

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

@ExcelCampus 9 ай бұрын

Glad to hear that @talhaakram3621 😀

@wendywang2533 2 жыл бұрын

Very good！I will t r y

@user-ci3og4bc1l 2 жыл бұрын

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.

@currentpeace 3 жыл бұрын

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

@bernardcadelina1301 2 жыл бұрын

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.

@phongnguyenhuu7045 2 жыл бұрын

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

@RolandK 3 жыл бұрын

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.

@marcq877 3 жыл бұрын

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

@FrancescoGrosso1094 3 жыл бұрын

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 :)

@ahmedmedhat3488 4 жыл бұрын

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

@tonyhoch3252 3 жыл бұрын

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?

@LC-sp9ox 3 жыл бұрын

Hi tony, did you figure out how to do this?

@sabirberoual4951 4 жыл бұрын

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

@anikmashudah1031 6 ай бұрын

Hi John. How to custom ribbon my macros like in your video?

@bkmohhota2662 3 жыл бұрын

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!

@Katnenis 2 жыл бұрын

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

@raddleriddle7752 8 ай бұрын

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!

@enricogalli3744 4 жыл бұрын

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! :-)

@ExcelCampus 4 жыл бұрын

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! 🙂

@enricogalli3744 4 жыл бұрын

@@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 :)

@rameshmanni3434 3 жыл бұрын

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

@zhuohanglin3664 2 жыл бұрын

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~

@attaulaleem3558 4 жыл бұрын

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

@ExcelCampus 4 жыл бұрын

Hi Atta, it will happen this week. Here's the link to register to the webinar excelcampus.easywebinar.live/registration-blueprint.

@faa9999 Жыл бұрын

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

@ExcelCampus Жыл бұрын

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

@alvinc5563 3 жыл бұрын

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?

@mivanows 2 жыл бұрын

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!

@Superdupertyty Жыл бұрын

Did you already get an answer to this? This is the error I'm getting as well

@prkhan56 2 жыл бұрын

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.

@soundsdreams 3 жыл бұрын

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.

@excelemployeeleavetracker1274 3 жыл бұрын

Try changing the source file from Excel files to CSB files... for some reason PQ works much faster with CSV files.

@mohamed.montaser 4 жыл бұрын

i saw you have a macro for getting unique values, care to make a video about it?

@ExcelCampus 4 жыл бұрын

Hi Mohamed, Sure thing! We actually a video on that macro. Here is the link. kzfaq.info/get/bejne/qdWGgqqdyaiRfaM.html I hope that helps. Thanks again and have a nice day! 🙂

@georginawu9546 2 жыл бұрын

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?

@stephanierempel1289 2 жыл бұрын

Did you find the Macro code? I could not find it either.

@JessicaDean12 5 ай бұрын

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?

@tonmoyhossain3902 3 жыл бұрын

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

@carloszerpazerpa2693 2 жыл бұрын

Hola t Hola Tonmoy. Lograstes conseguir la solucion? Yo estoy tratando de hacer lo mismo pero no me aparece esa propiedad

@stephanierempel1289 2 жыл бұрын

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!

@stephanierempel1289 2 жыл бұрын

I tried just typing it based off the video but the whole code is not visible in certain sections.

@stephanierempel1289 2 жыл бұрын

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.

@uchennaeric8290 4 жыл бұрын

Can we have a one-on-one tutorial class on data mining and scraping

@ExcelCampus 4 жыл бұрын

Hi Uchenna, I'm sorry, we don't do one-on-one training at this time.

@bharathig3959 Жыл бұрын

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?

@krishnakishorepeddisetti4387 3 жыл бұрын

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

@excelemployeeleavetracker1274 3 жыл бұрын

You should make a dynamic Path to change connections. If you search John's KZfaq Playlist you will find a video on this.