VBA query on external worksheet to an array

[Peanuts890]

Can the results of a Microsoft query on an external worksheet be routed to an array instead of a worksheet. I have already tried doing a query on an external worksheet, selecting 8 out od 10 columns and filtering the rows by a specific value in one of the cells. This was successful with the results going to a worksheet. Noted however that the query didn't recognise the header row in the external worksheet and the results had a first row like a filter.
Hope to be able to develop a macro to do the query, then do further manipulation on the array before displaying results in a worksheet.
Any help on this would be gratefully appreciated - bit of a novice on macros/VBA -many thanks.

 

[John_w]

No, I don't think you can assign the results of a query directly to an array; the Destination property must be a range. You could copy the range results to an array, like this:

    Dim theArray As Variant
    Dim query As QueryTable
    Dim resRange As Range
    
    Set resRange = query.ResultRange
       
    'The whole result range
    theArray = resRange.Value
    
    'With row 1 omitted
    theArray = resRange.Offset(1, 0).Resize(resRange.Rows.Count - 1, resRange.Columns.Count).Value

The query variable is your Microsoft query, however you've set that up.