Updating several web connections with queries in blocks with dependent data

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:

T_ESI_Status
NameValue
players23494
server_version1547102
start_time2019-08-09T11:02:26Z

<tbody>
</tbody>

Server Start Date
Date6/08/2019

<tbody>
</tbody>

T_ESI_Markets_History_1
TypeIDProductTypeNameURLaveragedatehighestlowestorder_countvolume
380Small Shield Extender IIhttps://esi.evetech.net/latest/markets/10000002/history/?datasource=tranquility&type_id=380387.975,975/08/2019387.975,97387.975,963151.181
394Shield Recharger IIhttps://esi.evetech.net/latest/markets/10000002/history/?datasource=tranquility&type_id=394218.988,755/08/2019218.988,76218.988,7485182
400Small Shield Booster IIhttps://esi.evetech.net/latest/markets/10000002/history/?datasource=tranquility&type_id=400344.765,805/08/2019344.765,98344.765,79133162
4381MN Afterburner IIhttps://esi.evetech.net/latest/markets/10000002/history/?datasource=tranquility&type_id=4383.499.996,695/08/20193.499.997,983.250.000,29392842
4405MN Microwarpdrive IIhttps://esi.evetech.net/latest/markets/10000002/history/?datasource=tranquility&type_id=4404.092.499,975/08/20194.097.869,904.092.498,76283586

<tbody>
</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
 

Behelith

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

Forum statistics

Threads
1,082,601
Messages
5,366,571
Members
400,902
Latest member
fathima

Some videos you may like

This Week's Hot Topics

Top