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

Подписаться 568 тыс.

Просмотров 79 тыс.

50% 1

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

Опубликовано:

5 июл 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 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!

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

@khalidmajeed2886 2 года назад

great sir, I was looking for it.

@shivabernstein Год назад

This is awesome!

@keithers7029 2 года назад

Good job! 👏

@kvishwajit 3 года назад

Great stuff

@ShimadaConsultor 2 года назад

Thank you!!!!

@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.

@AjaySingh-ll5qw 4 года назад

Really nice...

@ahmedmedhat3488 4 года назад

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

@phongnguyenhuu7045 2 года назад

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

@hervemercier-gl6bp 2 месяца назад

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

@ExcelCampus 2 месяца назад

You're welcome! 😀

@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.

@sisirakumara726 3 года назад

Hi. Thanks million

@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.

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

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

@wendywang2533 2 года назад

Very good！I will t r y

@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.

@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. 😀

@talhaakram3621 9 месяцев назад

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

@ExcelCampus 9 месяцев назад

Glad to hear that @talhaakram3621 😀

@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?

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

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

@sabirberoual4951 4 года назад

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

@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.

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

@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??

@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~

@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

@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

@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.

@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.

@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

@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. ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-soVOGldB-Mo.html I hope that helps. Thanks again and have a nice day! 🙂

@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?

@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.

@thegodofnewwold1743 2 года назад

How to open pq connect all table

@JessicaDean12 4 месяца назад

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?

@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.

@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?

@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

@thegodofnewwold1743 2 года назад

how to open menu my macros

@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 RU-vid Playlist you will find a video on this.