Nick Burns
Member
- Joined
- May 24, 2017
- Messages
- 162
- Reaction score
- 0
- Points
- 16
- Excel Version(s)
- Office 365
I'm using code to refresh/create a table from Power Query from here: https://gallery.technet.microsoft.com/office/VBA-to-automate-Power-956a52d1
Particularly this bit of code:
With a simple loop, I can pass each of the queries to that routine and give the user an indication in my progress bar when each query has STARTED.
What I'm trying to do is in the routine above is to check the status of the ..Refreshing Property and if it is True, loop and check it again. This will allow me to give the user an indication that a query is currently RUNNING. I'm hiding the Excel UI, so the use of the Status Bar isn't an option.
The problem lies in that loop - it's preventing the query from refreshing (even with DoEvents)... as soon as I break into the code, the worksheet populates with the data and the .Refreshing property is set to False.
Any thoughts?
Particularly this bit of code:
Code:
[COLOR=#3A3AFF][B]Sub[/B][/COLOR][COLOR=#000000] LoadToWorksheetOnly(query [/COLOR][COLOR=#3A3AFF][B]As[/B][/COLOR][COLOR=#000000] WorkbookQuery, currentSheet [/COLOR][COLOR=#3A3AFF][B]As[/B][/COLOR][COLOR=#000000] Worksheet) [/COLOR] [COLOR=green]' The usual VBA code to create ListObject with a Query Table[/COLOR]
[COLOR=green]' The interface is not new, but looks how simple is the conneciton string of Power Query:[/COLOR]
[COLOR=green]' "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & query.Name[/COLOR]
[COLOR=#3A3AFF][B]With[/B][/COLOR] currentSheet.ListObjects.Add(SourceType:=[COLOR=red]0[/COLOR], Source:= _
[COLOR=maroon]"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location="[/COLOR] & query.Name _
, Destination:=Range([COLOR=maroon]"$A$1"[/COLOR])).QueryTable
.CommandType = xlCmdDefault
.CommandText = Array([COLOR=maroon]"SELECT * FROM ["[/COLOR] & query.Name & [COLOR=maroon]"]"[/COLOR])
.RowNumbers = [COLOR=#3A3AFF][B]False[/B][/COLOR]
.FillAdjacentFormulas = [COLOR=#3A3AFF][B]False[/B][/COLOR]
.PreserveFormatting = [COLOR=#3A3AFF][B]True[/B][/COLOR]
.RefreshOnFileOpen = [COLOR=#3A3AFF][B]False[/B][/COLOR]
.BackgroundQuery = [COLOR=#3A3AFF][B]True[/B][/COLOR]
.RefreshStyle = xlInsertDeleteCells
.SavePassword = [COLOR=#3A3AFF][B]False[/B][/COLOR]
.SaveData = [COLOR=#3A3AFF][B]True[/B][/COLOR]
.AdjustColumnWidth = [COLOR=#3A3AFF][B]True[/B][/COLOR]
.RefreshPeriod = [COLOR=red]0[/COLOR]
.PreserveColumnInfo = [COLOR=#3A3AFF][B]False[/B][/COLOR]
.Refresh BackgroundQuery:=[COLOR=#3A3AFF][B]False[/B][/COLOR]
[COLOR=#3A3AFF][B]End[/B][/COLOR] [COLOR=#3A3AFF][B]With[/B][/COLOR]
[COLOR=#3A3AFF][B]End[/B][/COLOR] [COLOR=#3A3AFF][B]Sub[/B][/COLOR]With a simple loop, I can pass each of the queries to that routine and give the user an indication in my progress bar when each query has STARTED.
What I'm trying to do is in the routine above is to check the status of the ..Refreshing Property and if it is True, loop and check it again. This will allow me to give the user an indication that a query is currently RUNNING. I'm hiding the Excel UI, so the use of the Status Bar isn't an option.
The problem lies in that loop - it's preventing the query from refreshing (even with DoEvents)... as soon as I break into the code, the worksheet populates with the data and the .Refreshing property is set to False.
Any thoughts?