Run query1 on auto_open and query2 on demand

[Koppelgaard]

I have a query "Q_loadFiles" that loads files from sharepoint and puts the output in table "FilesData". 

Output from "Q_loadFiles"  is used in Q_filterdata, where filtering of the "FilesData" is done.

The filter parameters are set in a MSForms.ListBox by the user.

To save run time I want to run the "Q_loadFiles" only when the workbook opens and the "Q_filterdata" every time the user makes new choices in the form.

My question is 

1) how do I run the "Q_loadFiles" from auto_open (the Q_loadFiles is a connection)?

2) how do I run “Q_filterdata” without running “Q_loadFiles” ?

[Koppelgaard]

Thank you very much Adamboer for you exellent code.😊

In order to let the code wait until after update I changed

Dim conn As WorkbookConnection
For Each conn In ThisWorkbook.Connections
If conn.Name = "Q_filterdata" Then
conn.Refresh
Exit For
End If
Next conn

to this

For Each objConnection In ThisWorkbook.Connections
    If objConnection.Name = "Query - Q_FilterData" Then
         'Get current background-refresh value
         bBackground = objConnection.OLEDBConnection.BackgroundQuery
         'Temporarily disable background-refresh
         objConnection.OLEDBConnection.BackgroundQuery = False
        'Refresh this connection
         objConnection.Refresh

'Set background-refresh value back to original value
objConnection.OLEDBConnection.BackgroundQuery = bBackground
Exit For
End If
Next

I have a question. If  

Sub auto_open()
    ThisWorkbook.RefreshAll
End Sub

havent finished before the user runs the  "Query - Q_FilterData" refresh will it then fail?

Michael

[Adamboer]

1. To run the "Q_loadFiles" from auto_open, you can use the following VBA code:

Sub auto_open()
ThisWorkbook.RefreshAll
End Sub

This will refresh all connections in the workbook, including the "Q_loadFiles" connection.

1. To run "Q_filterdata" without running "Q_loadFiles", you can use the following VBA code:

Sub runQ_filterdata()
Dim conn As WorkbookConnection
For Each conn In ThisWorkbook.Connections
If conn.Name = "Q_filterdata" Then
conn.Refresh
Exit For
End If
Next conn
End Sub

This code loops through all the connections in the workbook and refreshes the "Q_filterdata" connection only. This way, the data from "Q_loadFiles" is not refreshed unnecessarily. You can assign this macro to a button or a shortcut key to make it easy for the user to run "Q_filterdata" whenever they want.

[Koppelgaard]

additinal info:

Q_LoadFiles:

let
Source = SharePoint.Files("https://aarhusuniversitet.sharepoint.com/sites/WETHorizons", [ApiVersion = 15]),
#"Select files" = Table.SelectRows(Source, each Text.Contains([Folder Path], "/Task 2.1/") and Text.Contains([Extension], ".xlsx") ),
#"Add fullpath" = Table.AddColumn(#"Select files", "FullPath", each [Folder Path]& [Name]),
#"Removed Other Columns" = Table.SelectColumns(#"Add fullpath",{"FullPath", "Content"}),
#"Add content column" = Table.AddColumn(#"Removed Other Columns", "ExcelData", each Excel.Workbook([Content])),
#"Add Sheetname and TableData" = Table.ExpandTableColumn(#"Add content column", "ExcelData", {"Name", "Data"}, {"Sheetname", "TableData"}),
#"Extract SheetData from TableData" = Table.AddColumn(#"Add Sheetname and TableData", "Sheetdata", each Table.PromoteHeaders([TableData])),
#"Remove content tableData" = Table.RemoveColumns(#"Extract SheetData from TableData",{"Content","TableData"}),
#"Expand Sheetdata" = Table.ExpandTableColumn(#"Remove content tableData", "Sheetdata", {"Wetland Type", "Classification", "Country", "Land-use", "Method", "Water table depth", ...
#"Edit column Names" = Table.TransformColumnNames(#"Expand Sheetdata",each Text.Replace(_,"Sheetdata.","")),
FilesData = #"Edit column Names"
in
FilesData

Q_FilterData:

let
Source = Q_LoadFiles,
List_type=List.Buffer(L_Type),
List_Classification=List.Buffer(L_Classification),
List_All_classification=List.Buffer(L_All_classification),
List_Country=List.Buffer(L_Country),
List_Country_others=List.Buffer(L_All_contries),
List_LandUse=List.Buffer(L_LandUse),
List_Method=List.Buffer(L_Method),
#"Filter on type" = if List.Contains(List_type, "All")=true then Table.SelectRows(Source, each (true)) else Table.SelectRows(Source, each (List.Contains(List_type, [Wetland Type] )=true)),
#"Filter on classification" = if List.Contains(List_Classification, "All")=true then Table.SelectRows(#"Filter on type", each (true)) else if List.Contains(List_Classification, "Other")=true then Table.SelectRows(#"Filter on type", each (List.Contains(List_All_classification, [Classification] )=false)) else Table.SelectRows(#"Filter on type", each (List.Contains( List_Classification, [Classification] )=true)),
#"Filter on country" = if List.Contains(List_Country,"All")=true then Table.SelectRows(#"Filter on classification", each (true)) else if List.Contains(List_Country,"Others")=true then Table.SelectRows(#"Filter on classification", each (List.Contains (L_All_contries,[Country])=false)) else Table.SelectRows(#"Filter on classification", each (List.Contains (L_Country,[Country])=true)),
#"Filter on landUse" = if List.Contains(List_LandUse,"All")=true then Table.SelectRows(#"Filter on country", each (true)) else Table.SelectRows(#"Filter on country", each (List.Contains(List_LandUse ,[#"Land-use"])=true)),
#"Filter on method" = if List.Contains(List_Method,"All")=true then Table.SelectRows(#"Filter on landUse", each (true)) else Table.SelectRows(#"Filter on landUse", each (List.Contains(List_Method ,[#"Method"])=true)),
IsNumeric = Table.AddColumn(#"Filter on method", "numbers", each Value.Is(Value.FromText([Water table depth]), type number)=true),
Output = IsNumeric
in
Output