How to refresh queries & connections data one at a time using VBA

[matrix]

Hello. Excel Supporters


I have a concern. I have program excel, when I open the workbook to refresh about 70 individual tables in queries & connections.


During that time my computer freezes about an hour because of the connections which are fleshing at the same time.


How can I allow excel to fresh data one at a time using VBA?
Please view file below.


Any other recommendations is accepted, if you are willing to support...


Thanks in advance


Cheers
https://drive.google.com/file/d/1Lyv...ew?usp=sharing

[NormS]

Instead of creating all of those queries, you can make a single query with all of the data from the web site and load that to Excel's "data model". You can then create a pivot table and use slicers to display data for the country or countries that you want. Take a look at this example which nicely shows how Power Query can clean up and manipulate data. For example,

Some of the changes listed in the table had both plus and minus, as in "+-0.37", which produced an error when changing the data type to decimal. Using replace values to remove the "+"'s in this column fixed the error without changing the numbers.

I wanted to split the bond types (e.g., "10Y", "6M") into length (10, 6) and period (Y, M). PQ provides a couple of options for splitting. I first tried splitting by one character from the right, only to discover that there was a bond type "Overnight" which got split into "Overnigh" and "t"! I then tried the "Digit to non-Digit" option which nicely split 10Y and 6M but left Overnight in the first column instead of moving it to the second. Finally solved this by using Transform -> Format to add a prefix of 0 to the column before splitting. This split Overnight properly and didn't affect the length values when they got converted to numbers.

Finally, if you want to refresh queries in VBA you can use this syntax:

ThisWorkbook.Connections("Query - Argentina").Refresh

Norm

[matrix]

I did not know that you can make a single query as you highlighted. I have viewed the file. I'm speechless. You did a lot more than what I have requested. I am not sure if you are on YouTube.
If you are willing and is open in helping me out.
Can you direct me to learn the above method? Especially on how to merge queries. Would like to learn what you have done.

Thanks in advance and your support.

[NormS]

Glad to have helped out. No, I'm not on YouTube, I just enjoy working with Excel and after nearly 35 years have gotten reasonably proficient. If you'd like to learn Power Query the book by our host Ken Puls, M is for Data Monkey is a good introduction.

https://www.excelguru.ca/content.php...-(Data)-Monkey

It's a few years old, though, so the menus in the book may look different from what you see on your screen since there have been a lot of changes to the Power Query user interface. Another thing I found helpful is this series of blog posts

https://datachant.com/2017/01/06/10-...bi-powerquery/

The Power Query forum on this web site is also great for seeking help. The people who respond there are very knowledgeable, I'm always learning something new.

[matrix]

Hello NormS

I read the the second link, on "10 Common Mistakes You Do In #PowerBI #PowerQuery – And How To Avoid Pitfalls".

The first lesson have given light to query weaknesses I recently discovered, but did not really understand how changing column names from external website can break query...

Thanks again for your time and support you have given to all in excel world...

Cheers