Power Platform Community Forum Thread Details

I'm rebuilding some old reports that have gotten slow, and am using queries rather than multiple connections to the same server and database this time.

In the original and new versions, each report has a bunch of tabs, and each tab has two buttons that run macros: one to refresh the data in that specific tab, and one to clear the data. This time I'd love to create only one piece of VBA code to refresh the query for the active worksheet, and pass a variable into it that identifies which query that should be. Doing that to clear the data is simple, but I haven't figured out a way to refresh.

For each query I've used Get Data > From Database > SQL, pasted in my query, hit Transform, and given it a specific name. Of course, hitting Close & Load the first time creates the tab for me and gives it the name of the query. It's simple enough to write a worksheet's name into a variable (I named mine the very original "sheetNm",) but how can I pass that variable into the refresh command?

This is an example what I'm working with:

ActiveWorkbook.Connections("Query - Fourth_Tab-L").Refresh

Naturally, I can't replace the name of the query with the name of the variable, that just triggers a subscript out of range error. Have any of you ever done this successfully?

Thanks for your time!

Categories:

Power Query