0 votes
in VBA by Beginner (5 points)
I would like to add a progress bar to an exsisting Macro starts by using "Get & Transform" to process a 3rd party's downloaded file.

I am comfortable using a progress bar with 'for loops', but I do not have a clue regarding what and how to time/measure something in the "Get & Transfer" procedure.

1 Answer

+1 vote
by Skilled (330 points)
selected by
 
Best answer


Hi DKV!!

You don't do anything inside of the Get and Transform User Interface. Instead, in VBA, set up your progress bar as usual (with DoEvents).  Start the progress bar. To connect to the data source and refresh it, use the following code:

' Activate the worksheet where you load the Get & Transform Table output
' The next line will select cell A2 within that table.

    Range("A2").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Ken Puls has some other methods if the above is not usable. For example, if you do NOT wish to refresh the data, but only want to see how long it takes to connect, then read this post and the comments:

https://www.excelguru.ca/blog/2014/10/22/refresh-power-query-with-vba/

Cheers,

 

Mitch

 

by Beginner (5 points)

yes Mitch, thanks for the help. Just what I needed.smiley

by Skilled (330 points)

You're welcome! laugh

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...