The “Conclusion” worksheet is the final link where the results of calculation operations that were performed in other worksheets are shown. The cell range “A2: A86” contains in each of its cells structure functions: = ‘Sheet1’B4! ; = ‘Sheet2’B4! ; = ‘Sheet6’B4! ; etc. In summary, the data entry in these cells is through functions, manual entry is not used.
My workbook gets its data from 5 web pages with similar structure in "JSON" format for a total of 20 queries. The “Power Query” tool transforms all of them into query tables; This allows me to transfer the data obtained in the tables to other worksheets.
I need to shorten as much as possible the time it takes to refresh all the queries since the web pages are constantly being updated.
The range "A2: A86" contains the final results of all the operations in my workbook. I would like to attribute to “A2: A86” the task of self-refreshing the queries considering some parameters:
- If the values that show the functions of the cells of the range “A2: A86” are “<5”, then the self-refreshing of all the queries would be continuous and in a loop. That is, once the last query has been refreshed, the self-refreshing process will start again, without the need to wait for any time. Anyway, a loop. In addition, the refreshing of all queries should occur as stated at the end of the last paragraph.If one of the cells that make up the range "A2: A86" is "> = 5" then the autofresh would be only for some queries. The queries to refresh have influence on the cell that fulfills the condition "> = 5". Each cell in the range should control the refresh of a query triplet. For example: If "A2"> = 5 then only queries "Query - 2" would be refreshed; "Query - 3" and "Query - 4"; if "A3"> = 5 then only queries "Query - 3" would be refreshed; "Query - 5" and "Query - 6". Similar should occur with the remaining cells of the said range. I do not show all the relationships between cells and queries due to the large volume they represent.
- If 2 or more cells in the range meet the condition "> = 5" then I must create a decision parameter. Let's give this new task to a random cell, for example: "AC1". This will be responsible for deciding which triplet of queries will be refreshed if there are 2 or more cells in the range that meet the condition "> = 5". To achieve this, the range of cells "A2: A86" must be classified into 5 groups. Each of the groups will be identified with a number or letter, for example: 1; 2; 3; 4 and 5; or A; B; C; D and E. For example: (Assuming we will take the letters as a management element): Group A = "A2: A18"; group B = "A19: A35"; group C = "A36: A52", group D = "A53: A69"; and group E = "A70: A86". When one of these letters is entered manually in cell “AC1” then this will be the group that has priority and the cells of the other groups will be ignored. For example: I manually enter in "AC1" = "D", then "A60"> = 5 then the queries that I have would be refreshed. In case the condition> = 5 is met in another cell outside of group D, then all the queries of the looped workbook would simply be refreshed as I explained in subsection 1.
Below I will show a VBA code that was created by Cris, a friend of the forum.
I made some changes to it but it still needs to be added in its content to achieve what you want. Also, there is no relationship between cells. My goal is to show the code to guide the one who is reading the post.
The disadvantages of the following code are the following:
- The data entry is considered to be done manually in this code and in my project the data of "A2: A86" should enter automatically as they are functions.
- This code does not yet state what corresponds to cell "AC1".
- This code is about an inequality of consecutive cells and in my case it is about inequality with a number. I hope someone can help me. Sorry if there are errors in the writing of the article, my official language is Spanish. Thanks in advance.
Sub set_refreshPeriod() Dim wks As Worksheet Dim rng1 As Range, rng2 As Range Dim lngPeriod As Long Dim i As Integer Dim internalQuerys As Variant 'The 20 Querys which update all 2 Minutes Dim externalQuerys As Variant 'The 3 Querys which update if B1>B2 Set wks = ActiveWorkbook.Worksheets("Conclusion") Set rng1 = wks.Range("B1") Set rng2 = wks.Range("B2") 'The name of your Query always starts wiht "Query - " in english or "Abfrage - " in German 'You can add here more Querys internalQuerys = Array("Abfrage - Tabelle1", _ "Abfrage - Tabelle2", _ "Abfrage - Tabelle3") externalQuerys = Array("Abfrage - File1", _ "Abfrage - File2", _ "Abfrage - File3") 'if B1 > B2 then do not refresh the internalQuerys every 2 Minutes else do it If rng1 > rng2 Then lngPeriod = 0 'do not refresh internalQuerys 'Refresh the the external Querys For i = 0 To UBound(externalQuerys) ThisWorkbook.Connections(externalQuerys(i)).OLEDBConnection.Refresh Next Else: lngPeriod = 2 'Time 2 Minutes for internal Querys End If 'Set the RefreshPeriod for the internal Querys For i = 0 To UBound(internalQuerys) ThisWorkbook.Connections(internalQuerys(i)).OLEDBConnection.RefreshPeriod = lngPeriod Next End Sub