none
Get the Query data in an VBA Array RRS feed

  • Question

  • Hi everyone;

    I want to execute a query from VBA to an Excel worksheet table and put the data into a variant array to manage the data.

    The query is already set, but I don't know how I've to input the data into an Array, in other words how I've to read it using a recordset method.

    This is the code for the query:

    'Initiate the Query
    ActiveWorkbook.Queries.Add Name:="EURAUDH4", _
    Formula:="let" & Chr(13) & "" & Chr(10) & _
    "Origen = Excel.Workbook(File.Contents(""X:\Dropbox\Hojas de cálculo\Trading\Datos H4\20140517 EURAUD 4H.xlsx""), null, true)," & Chr(13) & "" & Chr(10) & _
    "EURAUD_Table = Origen{[Item=""EURAUD"",Kind=""Table""]}[Data]," & Chr(13) & "" & Chr(10) & _
    "#""Tipo cambiado"" = Table.TransformColumnTypes(EURAUD_Table,{{""FE"", type date}, {""HO"", type time}, {""AP"", type number}, {""MAX"", type number}, {""MI" & "N"", type number}, {""CIE"", type number}, {""TR"", type number}})," & Chr(13) & "" & Chr(10) & _
    "#""Columnas quitadas"" = Table.RemoveColumns(#""Tipo cambiado"",{""TR""})" & Chr(13) & "" & Chr(10) & _
    "in" & Chr(13) & "" & Chr(10) & "#""Columnas quitadas"""

    'This code does'n t work:

    Set RST = ActiveWorkbook.Queries("EURAUDH4").Recordset ???


    Wednesday, May 17, 2017 11:04 AM

Answers

  • Here is an example of how you can get the data into a Recordset (assuming there isn't anything that is complicated with respect to the data):

    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
       "Data Source=C:\Users\...\Documents\Database\Excel\Book2007.xlsx;Extended Properties=Excel 12.0;"
    
    rs.Open "Select * from [Sheet2$]", cnn, adOpenStatic, adLockOptimistic


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, May 20, 2017 7:26 PM

All replies

  • I don't see anything in the Query object model that allows you to export the data to an ADO Recordset or array. I'm not sure why you need to use an array but you could also just copy the Worksheet to an array directly. 

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, May 17, 2017 1:43 PM
  • Hi Paul;

    Thanks for the reply. i'll try to answer your question:

    1. The data is in other file. So I've to use a query to this file. 
    2. I don't need all the data in the table of the file, so -again- I need a query. Obviously.
    3. Once I have the query I process the information in with VBA.
    4. Now I'd doing a two steps process, firstly I'm doing the query to the source, and set the data in a sheet, secondly, I'm reading into a variant array.
    5. So, if could reduce this two-steps process to one, everything would be better?
    6. The code of the query has been generated by the application. So my question here is this one, may I'm needing a Recordset to put the data to an array? Or am I wrong?
    Friday, May 19, 2017 8:09 AM
  • It looks like you are using a power query and I don't see how you can get that data into an ADO Recordset. A standard SELECT query would work fine though.

    You don't need a Recordset to put the data into an array - you can copy the data from a Worksheet into an array directly:

    http://stackoverflow.com/questions/25367925/copying-worksheets-using-dynamic-array


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, May 20, 2017 3:26 PM
  • I'm using a power query, anyway if this case works fine with a standard SELECT query, it should be a solution.

    But remember that, I need a range FROM ANOTHER FILE filtered, so:

    1. Can I do that with a standard query?
    2. The source is in ANOTHER file, So I can't copy the data.

    Remember Paul, tha data is IN ANOTHER FILE

    Thanks a lot, Paul.

    Saturday, May 20, 2017 6:56 PM
  • Here is an example of how you can get the data into a Recordset (assuming there isn't anything that is complicated with respect to the data):

    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
       "Data Source=C:\Users\...\Documents\Database\Excel\Book2007.xlsx;Extended Properties=Excel 12.0;"
    
    rs.Open "Select * from [Sheet2$]", cnn, adOpenStatic, adLockOptimistic


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Saturday, May 20, 2017 7:26 PM
  • Hi Paul, thanks a lot. I'll try this way
    Sunday, May 21, 2017 3:32 PM