Updating several web connections with queries in blocks with dependent data

B

Behelith

New Member
Joined
Aug 9, 2019
Messages
10
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.
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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Sort by date Sort by votes
Something happened when I pasted the first code block.

Code: 
Sub Refresh_ESI()
    '
    ' Refresh_ESI connections Macro
    '

    ' Show a message (form) that tells the user that the connections are updating.
    ' (The 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

    ' Start updating the connections in blocks
    ' The T_ESI_CostIndexes table can be refreshed independently of the others that follow.
    ActiveWorkbook.Connections("Query - T_ESI_CostIndexes").Refresh

    ' The T_ESI_Status table contains the date and time of the last server data update
    ActiveWorkbook.Connections("Query - T_ESI_Status").Refresh

    ' In a named cell called "ServerStartDate" the value from the "start_time" record in the T_ESI_Status table is truncated and converted from text format to date format.
    ' So "2019-08-08T11:03:47Z" is converted to "8/08/2019". This date will be used later in the five other web queries.
    ' The web connection of T_ESI_Status needs to be refreshed, the query results have to be completed and the "ServerStartDate" value has to be calculated before the code below can start to execute.
    ' (Updating the connection takes about 5 seconds but parsing the data takes an additional 10-20 seconds depending on server load)

    -> Code required to wait

    ' When ServerStartTime has been updated and "ServerStartDate" has been calculated, 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 depending on server load and amount of data.
    ' Each query receives market data of the last 365 days for about 800 items.
    ' The data is filtered on "ServerStartDate" -1 for table nr1, -2 for table nr2 and so on.
    ' Since the amount of data that can be received from the server is limited to 1000 lines the connections have to be updated separately, one query for each day.

    -> Code required to wait

    ' When all the connections and queries have been updated, the form "Refreshing connection" can be unloaded.
    ' At this point all the tables contain the updated values
    Unload Form_Refresh

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Power query - stopping refresh if no data available
Replies
2
Views
517
Kariba
K
R
  • Question Question
VBA to add to data model when creating Power Query tables`
Replies
3
Views
643
Raddle
R
S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
675
Domenic
D
G
  • Question Question
Power Query: Source Data with Variable Column Header
Replies
8
Views
4K
sandy666
sandy666
J
  • Question Question
VBA - Loading Connected Data to Worksheet and then back to Connection only
Replies
0
Views
703
johnny51981
J

Forum statistics

Threads
1,224,817
Messages
6,181,147
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top