Put result of ActiveWorkbook.Queries in Array instead of in a Worksheet

[helivw]

I have a query:

  Bron = Excel.CurrentWorkbook(){[Name=""Finost_OI_1""]}[Content]," & Chr(13) & "" & Chr(10) & _
"    #""Type gewijzigd"" = Table.TransformColumnTypes(Bron,{{""Date"", type date}, {""ArtNr"", type text}})," & Chr(13) & "" & Chr(10) & _
"    #""Rijen gefilterd"" = Table.SelectRows(#""Type gewijzigd"", each Text.Contains([ArtNr], ""DEP"") or Text.Contains([ArtNr], ""H0O""))," & Chr(13) & "" & Chr(10) & _
"    #""Kolommen verwijderd"" = Table.RemoveColumns(#""Rijen gefilterd"",{""AcNr"", ""AcName"", ""Discs"", ""Remarks""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Kolommen verwijderd"""

Then, I put the result in a worksheet

Worksheets.Add(After:=Worksheets("Print2")).Name = "Temp"
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=QueryTab;Extended Properties=""""", Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [QueryTab]")
    .ListObject.DisplayName = QT
    .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Queries(QT).Delete
End Sub

Instead of pasting it in a worksheet, I would like to put the result directly in an array without loosing the time to passing via a worksheet

Many thanks for helping me out

 

[sijpie]

helivw, can you make your example simpler. It's about the method, not the details of the query. Your post is quite unreadable.

 

[xenou]

As far as I know an Excel Query Table doesn't have a result set that can be accessed independently - you can only output to a worksheet. You would use ADO if you want to create queries that return records sets that you can manipulate in code.

 

[helivw]

My point is, the query sends the result as a listobject to a worksheet
Writing in a worksheet is time consuming
You have to read the worksheet data... for further calculations in the worksheet
I tried to solve it to work with arrays
The advantage is you are working in the RAM so fast
The disadvantage:
In a worksheet you can work with < WorksheetFunction.SumIfs(.... >
In an array you have to loop the array with several < If Then's >

 

[sijpie]

https://sitestory.dk/excel_vba/worksheet-functions-arrays.htm

 

[helivw]

Thanks. Really interesting links. Very helpful

Do you have any idea if SumIfs works also with Arrays
I tried << a = WorksheetFunction.SumIfs(WorksheetFunction.Index(Arr, , 16), WorksheetFunction.Index(Arr, , 5), ">=" & 400000,...) without succes