I'm trying to update several connections in 2 blocks, the connection of the second block required data from the first block as input.
To update the connections the user had to click on a button which starts the web data update process.
During the update process a message is displayed (with a userform) to the user "Refreshing data connections, Please wait" until all the data is successfully updated.
Since the web queries of the second block depend on a date that is retrieved in the first block and by default all the queries are updated at the same time, the second block is not updated correctly.
Also the message/userform is unloaded long before the data has been retrieved.
I would like to refresh the query "T_ESI_Status", when that is completed and data in the sheet is updated the five queries "T_ESI_Markets_History_1" to "T_ESI_Markets_History_5" should update, when that data is completely updated on the sheet unload the userform.
I can't really use a timer because the time required for the update process varies much.
If I Disable Background Refresh in the properties of the query "T_ESI_Status" and Enable for the others the userform is unloaded long before the "T_ESI_Markets_History_X" tables have been updated.
I can Disable Background refresh only for the last query "T_ESI_Markets_History_5", but the other queries can take more time to load as well.
I believe a solution can be created with "Public WithEvents qt As QueryTable" and "Private Sub qt_AfterRefresh(ByVal Success As Boolean)", but I haven't been able to figure it out.
I've been working on a solution for quite a bit of time (over a period of several months) and learned a lot in the process by reading countless treads on several forums, which also helped me solve many other problems.
But now I'd like to get a solution to this problem and hope someone can help me. It is really the one problem I haven't been able to crack.
The procedures are explained in detail in the code blocks below.
This is the code of the macro that is executed when the "Refresh data" button is pressed.
The tables look like this:
<tbody>
[TD="class: xl65, colspan: 2"]T_ESI_Status[/TD]
[TD="class: xl68"]Name[/TD]
[TD="class: xl68"]Value[/TD]
[TD="class: xl66"]players[/TD]
[TD="class: xl66, align: right"]23494[/TD]
[TD="class: xl66"]server_version[/TD]
[TD="class: xl67"]1547102[/TD]
[TD="class: xl66"]start_time[/TD]
[TD="class: xl67"]2019-08-09T11:02:26Z[/TD]
</tbody>
<tbody>
[TD="class: xl65, colspan: 2"]Server Start Date[/TD]
[TD="class: xl66"]Date[/TD]
[TD="class: xl67"]6/08/2019[/TD]
</tbody>
<tbody>
[TD="class: xl65, colspan: 9"]T_ESI_Markets_History_1[/TD]
[TD="class: xl66"]TypeID[/TD]
[TD="class: xl66"]ProductTypeName[/TD]
[TD="class: xl66"]URL[/TD]
[TD="class: xl66"]average[/TD]
[TD="class: xl66"]date[/TD]
[TD="class: xl66"]highest[/TD]
[TD="class: xl66"]lowest[/TD]
[TD="class: xl66"]order_count[/TD]
[TD="class: xl66"]volume[/TD]
[TD="class: xl66, align: right"]380[/TD]
[TD="class: xl66"]Small Shield Extender II[/TD]
[TD="class: xl66"]https://esi.evetech.net/latest/markets/10000002/history/?datasource=tranquility&type_id=380[/TD]
[TD="class: xl67, align: right"]387.975,97[/TD]
[TD="class: xl68, align: right"]5/08/2019[/TD]
[TD="class: xl67, align: right"]387.975,97[/TD]
[TD="class: xl67, align: right"]387.975,96[/TD]
[TD="class: xl69, align: right"]315[/TD]
[TD="class: xl69, align: right"]1.181[/TD]
[TD="class: xl66, align: right"]394[/TD]
[TD="class: xl66"]Shield Recharger II[/TD]
[TD="class: xl66"]https://esi.evetech.net/latest/markets/10000002/history/?datasource=tranquility&type_id=394[/TD]
[TD="class: xl67, align: right"]218.988,75[/TD]
[TD="class: xl68, align: right"]5/08/2019[/TD]
[TD="class: xl67, align: right"]218.988,76[/TD]
[TD="class: xl67, align: right"]218.988,74[/TD]
[TD="class: xl69, align: right"]85[/TD]
[TD="class: xl69, align: right"]182[/TD]
[TD="class: xl66, align: right"]400[/TD]
[TD="class: xl66"]Small Shield Booster II[/TD]
[TD="class: xl66"]https://esi.evetech.net/latest/markets/10000002/history/?datasource=tranquility&type_id=400[/TD]
[TD="class: xl67, align: right"]344.765,80[/TD]
[TD="class: xl68, align: right"]5/08/2019[/TD]
[TD="class: xl67, align: right"]344.765,98[/TD]
[TD="class: xl67, align: right"]344.765,79[/TD]
[TD="class: xl69, align: right"]133[/TD]
[TD="class: xl69, align: right"]162[/TD]
[TD="class: xl66, align: right"]438[/TD]
[TD="class: xl66"]1MN Afterburner II[/TD]
[TD="class: xl66"]https://esi.evetech.net/latest/markets/10000002/history/?datasource=tranquility&type_id=438[/TD]
[TD="class: xl67, align: right"]3.499.996,69[/TD]
[TD="class: xl68, align: right"]5/08/2019[/TD]
[TD="class: xl67, align: right"]3.499.997,98[/TD]
[TD="class: xl67, align: right"]3.250.000,29[/TD]
[TD="class: xl69, align: right"]392[/TD]
[TD="class: xl69, align: right"]842[/TD]
[TD="class: xl66, align: right"]440[/TD]
[TD="class: xl66"]5MN Microwarpdrive II[/TD]
[TD="class: xl66"]https://esi.evetech.net/latest/markets/10000002/history/?datasource=tranquility&type_id=440[/TD]
[TD="class: xl67, align: right"]4.092.499,97[/TD]
[TD="class: xl68, align: right"]5/08/2019[/TD]
[TD="class: xl67, align: right"]4.097.869,90[/TD]
[TD="class: xl67, align: right"]4.092.498,76[/TD]
[TD="class: xl69, align: right"]283[/TD]
[TD="class: xl69, align: right"]586[/TD]
</tbody>
This is the code for the queries and functions used in the queries.
I've seen many discussions about this issue but haven't found one that could solve it completely.
Therefore I believe it could also help many others out there.
Have fun solving the puzzle!
Regards,
Steven
To update the connections the user had to click on a button which starts the web data update process.
During the update process a message is displayed (with a userform) to the user "Refreshing data connections, Please wait" until all the data is successfully updated.
Since the web queries of the second block depend on a date that is retrieved in the first block and by default all the queries are updated at the same time, the second block is not updated correctly.
Also the message/userform is unloaded long before the data has been retrieved.
I would like to refresh the query "T_ESI_Status", when that is completed and data in the sheet is updated the five queries "T_ESI_Markets_History_1" to "T_ESI_Markets_History_5" should update, when that data is completely updated on the sheet unload the userform.
I can't really use a timer because the time required for the update process varies much.
If I Disable Background Refresh in the properties of the query "T_ESI_Status" and Enable for the others the userform is unloaded long before the "T_ESI_Markets_History_X" tables have been updated.
I can Disable Background refresh only for the last query "T_ESI_Markets_History_5", but the other queries can take more time to load as well.
I believe a solution can be created with "Public WithEvents qt As QueryTable" and "Private Sub qt_AfterRefresh(ByVal Success As Boolean)", but I haven't been able to figure it out.
I've been working on a solution for quite a bit of time (over a period of several months) and learned a lot in the process by reading countless treads on several forums, which also helped me solve many other problems.
But now I'd like to get a solution to this problem and hope someone can help me. It is really the one problem I haven't been able to crack.
The procedures are explained in detail in the code blocks below.
This is the code of the macro that is executed when the "Refresh data" button is pressed.
Code:
Sub Refresh_ESI()
'
' Refresh_ESI connections Macro
'
' Show a message (form) that tells the user that the connections are updating ->
' (Showmodal property of the form is set to true so that the connections can refresh while the message is displayed)
Form_Refresh.Label1.Caption = "Refreshing EVE ESI data connections" & vbNewLine & "Please wait"
Form_Refresh.Show
' Then starty updating the connections in blocks
ActiveWorkbook.Connections("Query - T_ESI_CostIndexes").Refresh
' The T_ESI_CostIndexes table can be refreshed independently of the others that follow
ActiveWorkbook.Connections("Query - T_ESI_Markets_Prices").Refresh
' Wait until T_ESI_Markets_Prices table has refreshed the web connection and calculated the query results
' From the start_time record in the table I calculate/convert the date from text format to date format in a named cell called "ServerStartTime"
' 2019-08-08T11:03:47Z is converted to 8/08/2019, this date will be used later to upate the five other web queries
' Wait until T_ESI_Markets_Prices table has refreshed the web connection, calculated the query results and calculated the "ServerStartTime" value
' Updating the connection takes about 5 seconds but parsing the data takes an additional 10-20 seconds depensing on server load
-> Code required to wait
' When ServerStartTime is updated, refresh the next five connections and queries
ActiveWorkbook.Connections("Query - T_ESI_Markets_History_1").Refresh
ActiveWorkbook.Connections("Query - T_ESI_Markets_History_2").Refresh
ActiveWorkbook.Connections("Query - T_ESI_Markets_History_3").Refresh
ActiveWorkbook.Connections("Query - T_ESI_Markets_History_4").Refresh
ActiveWorkbook.Connections("Query - T_ESI_Markets_History_5").Refresh
' Updating the connection takes about 10-20 seconds and parsing the data takes an additional 30-60 seconds depensing on server load and amount of data
' Each query received market data of the last 365 days of about 800 items
' The data is filtered to only get data of ServerStartTime minus 1, 2, 3, 4 and 5 dates (yesterday, the day before, etc.)
' Since the amount of data is limited to 1000 lines the connections are updated separately for each day
-> Code required to wait
' When all the connections and queries have been updated, the form "Refreshing connection" is unloaded
' At this point all the tables contain the updated values
Unload Form_Refresh
End Sub
The tables look like this:
<tbody>
[TD="class: xl65, colspan: 2"]T_ESI_Status[/TD]
[TD="class: xl68"]Name[/TD]
[TD="class: xl68"]Value[/TD]
[TD="class: xl66"]players[/TD]
[TD="class: xl66, align: right"]23494[/TD]
[TD="class: xl66"]server_version[/TD]
[TD="class: xl67"]1547102[/TD]
[TD="class: xl66"]start_time[/TD]
[TD="class: xl67"]2019-08-09T11:02:26Z[/TD]
</tbody>
<tbody>
[TD="class: xl65, colspan: 2"]Server Start Date[/TD]
[TD="class: xl66"]Date[/TD]
[TD="class: xl67"]6/08/2019[/TD]
</tbody>
<tbody>
[TD="class: xl65, colspan: 9"]T_ESI_Markets_History_1[/TD]
[TD="class: xl66"]TypeID[/TD]
[TD="class: xl66"]ProductTypeName[/TD]
[TD="class: xl66"]URL[/TD]
[TD="class: xl66"]average[/TD]
[TD="class: xl66"]date[/TD]
[TD="class: xl66"]highest[/TD]
[TD="class: xl66"]lowest[/TD]
[TD="class: xl66"]order_count[/TD]
[TD="class: xl66"]volume[/TD]
[TD="class: xl66, align: right"]380[/TD]
[TD="class: xl66"]Small Shield Extender II[/TD]
[TD="class: xl66"]https://esi.evetech.net/latest/markets/10000002/history/?datasource=tranquility&type_id=380[/TD]
[TD="class: xl67, align: right"]387.975,97[/TD]
[TD="class: xl68, align: right"]5/08/2019[/TD]
[TD="class: xl67, align: right"]387.975,97[/TD]
[TD="class: xl67, align: right"]387.975,96[/TD]
[TD="class: xl69, align: right"]315[/TD]
[TD="class: xl69, align: right"]1.181[/TD]
[TD="class: xl66, align: right"]394[/TD]
[TD="class: xl66"]Shield Recharger II[/TD]
[TD="class: xl66"]https://esi.evetech.net/latest/markets/10000002/history/?datasource=tranquility&type_id=394[/TD]
[TD="class: xl67, align: right"]218.988,75[/TD]
[TD="class: xl68, align: right"]5/08/2019[/TD]
[TD="class: xl67, align: right"]218.988,76[/TD]
[TD="class: xl67, align: right"]218.988,74[/TD]
[TD="class: xl69, align: right"]85[/TD]
[TD="class: xl69, align: right"]182[/TD]
[TD="class: xl66, align: right"]400[/TD]
[TD="class: xl66"]Small Shield Booster II[/TD]
[TD="class: xl66"]https://esi.evetech.net/latest/markets/10000002/history/?datasource=tranquility&type_id=400[/TD]
[TD="class: xl67, align: right"]344.765,80[/TD]
[TD="class: xl68, align: right"]5/08/2019[/TD]
[TD="class: xl67, align: right"]344.765,98[/TD]
[TD="class: xl67, align: right"]344.765,79[/TD]
[TD="class: xl69, align: right"]133[/TD]
[TD="class: xl69, align: right"]162[/TD]
[TD="class: xl66, align: right"]438[/TD]
[TD="class: xl66"]1MN Afterburner II[/TD]
[TD="class: xl66"]https://esi.evetech.net/latest/markets/10000002/history/?datasource=tranquility&type_id=438[/TD]
[TD="class: xl67, align: right"]3.499.996,69[/TD]
[TD="class: xl68, align: right"]5/08/2019[/TD]
[TD="class: xl67, align: right"]3.499.997,98[/TD]
[TD="class: xl67, align: right"]3.250.000,29[/TD]
[TD="class: xl69, align: right"]392[/TD]
[TD="class: xl69, align: right"]842[/TD]
[TD="class: xl66, align: right"]440[/TD]
[TD="class: xl66"]5MN Microwarpdrive II[/TD]
[TD="class: xl66"]https://esi.evetech.net/latest/markets/10000002/history/?datasource=tranquility&type_id=440[/TD]
[TD="class: xl67, align: right"]4.092.499,97[/TD]
[TD="class: xl68, align: right"]5/08/2019[/TD]
[TD="class: xl67, align: right"]4.097.869,90[/TD]
[TD="class: xl67, align: right"]4.092.498,76[/TD]
[TD="class: xl69, align: right"]283[/TD]
[TD="class: xl69, align: right"]586[/TD]
</tbody>
This is the code for the queries and functions used in the queries.
Code:
' fGetESIResults (Power Query function used in T_ESI_Markets_Prices_X querries)
let GetResults=(URL) =>
let
Source = Json.Document(Web.Contents(URL)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#"Converted to Table"
in GetResults
--------------------------------------------------------------------------------------------------------------
' GetValue (Power Query function used in T_ESI_Markets_Prices_X querries)
let GetValue=(rangeName) =>
let
name = Excel.CurrentWorkbook(){[Name=rangeName]}[Content],
value = name{0}[Column1]
in
value
in GetValue
--------------------------------------------------------------------------------------------------------------
' T_ESI_Status (Power querry that retrieves the server_start value)
let
Source = Json.Document(Web.Contents("https://esi.evetech.net/latest/status/?datasource=tranquility"))
in
Source
--------------------------------------------------------------------------------------------------------------
' T_ESI_Markets_Prices_X (Power querry that retrieves the server_start value)
' X is the number of each of the five queries,
' -1 at the end changes to -2, -3, -4 or -5 for the other queries
let
Source = Excel.CurrentWorkbook(){[Name="T_ESI_Markets_History_URL"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"TypeID", Int64.Type}, {"ProductTypeName", type text}, {"URL", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Result Data", each if fGetESIResults([URL]) <> null then fGetESIResults([URL]) else null),
#"Expanded Result Data" = Table.ExpandTableColumn(#"Added Custom", "Result Data", {"Column1"}, {"Column1"}),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Expanded Result Data", "Column1", {"average", "date", "highest", "lowest", "order_count", "volume"}, {"average", "date", "highest", "lowest", "order_count", "volume"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Column1",{{"average", type number}, {"date", type date}, {"highest", type number}, {"lowest", type number}, {"order_count", Int64.Type}, {"volume", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([date] = Date.AddDays(Date.From(GetValue("ServerStartTime")),-1)))
in
#"Filtered Rows"
I've seen many discussions about this issue but haven't found one that could solve it completely.
Therefore I believe it could also help many others out there.
Have fun solving the puzzle!
Regards,
Steven