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 (489 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 (489 points)

You're welcome! laugh

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

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

...