Using an array or dictionary as from clause in sql in excel vba

Question

Is it possible to use an array or dictionary as the table in a SQL statement.

e.g. strSQL = "SELECT * FROM " & myArray & ""

Thanks in advance.

Not in VBA. VB.NET has Linq which is like SQL. SQL is for querying databases, not local variables. What would you expect the output to be? If you already have an array what do you need the SQL for? — D Stanley
– D Stanley, Commented Jun 16, 2016 at 13:48
Paste the array to an empty sheet and then use ADO to query the sheet. Here is a solution with sample code for your consideration: stackoverflow.com/questions/3955061/… — Ralph
– Ralph, Commented Jun 16, 2016 at 13:51
That was only a rough example the actually sql would have WHERE and GROUP BY clauses, possibly with multiple sql statements being run across the data. — user4637238
– user4637238, Commented Jun 16, 2016 at 13:52
What do you want to do at all? What would be the desired query? Normally you should use Join to link tables. If you would prepend the tables comma separated you would create a cartesian product of all possible combinations of rows between all tables. — Tim Schmelter
– Tim Schmelter, Commented Jun 16, 2016 at 13:53
Thanks Ralph but this is being deployed as an XLA so i wanted to keep writing to a sheet to a minimum — user4637238
– user4637238, Commented Jun 16, 2016 at 13:54

Ralph · Accepted Answer · 2016-06-16 15:26:12Z

2

Expanding upon the idea provided by @Nathan_Sav the following code should to it:

Dim a(3) As String

a(0) = "1 as C1"
a(1) = 2
a(2) = 3
a(3) = 4

Debug.Print "select * from (SELECT " & Join(a, " UNION ALL SELECT ") & ") as tmp"

Update:

Here is a short sub to manually concatenate / construct the necessary string:

Option Explicit

Sub tmpTest()

Dim strSQL As String
Dim varArray As Variant
Dim lngRow As Long, lngColumn As Long

varArray = ThisWorkbook.Worksheets(1).Range("A1:G12")

strSQL = "select * from "
strSQL = strSQL & "(select "
For lngRow = LBound(varArray, 1) + 1 To UBound(varArray, 1)
    For lngColumn = LBound(varArray, 2) To UBound(varArray, 2)
        'First row must contain column names
        If lngRow = LBound(varArray, 1) + 1 Then
            strSQL = strSQL & varArray(lngRow, lngColumn) & _
                " as [" & varArray(lngRow - 1, lngColumn) & "], "
        Else
            strSQL = strSQL & varArray(lngRow, lngColumn) & ", "
        End If
        'Beyond the last column a UNION ALL SELECT must be added
        '  except for the last row
        If lngColumn = UBound(varArray, 2) And _
            lngRow < UBound(varArray, 1) Then
                strSQL = Mid(strSQL, 1, Len(strSQL) - 2) & _
                    " union all select "
        End If
    Next lngColumn
Next lngRow
strSQL = Mid(strSQL, 1, Len(strSQL) - 2) & ") as tmp"
Debug.Print strSQL

End Sub

The above code assumes the data to be on sheet1 in the range A1:G12 where the first row contains the columns headers.

edited Jun 16, 2016 at 15:26

answered Jun 16, 2016 at 14:28

Ralph

9,4644 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user4637238 Over a year ago

thx ralph, just to complicate matters the array is 2 dimensional, so i get a subscript out of range error when i try. Is it possible for this to work on a 2 dimensional array. thx for all your help so far (everyone).

user4637238 Over a year ago

Sorry Ralph i'm kinda new at this and don't know the etiquette, happy to be pointed in the right direction on that.

Igoranze · Accepted Answer · 2016-06-16 13:51:31Z

2

SQL array like search function:

SELECT TABLEARRAY.*
FROM (
    SELECT P.*
    FROM Person P
    WHERE P.id IN (1, 2, 3, 4, 18) --This would be your array with ID's
     ) AS TABLEARRAY

answered Jun 16, 2016 at 13:51

Igoranze

1,48613 silver badges36 bronze badges

Comments

Nathan_Sav · Accepted Answer · 2016-06-16 13:54:36Z

2

Dim a(3) As Variant

a(0) = 1
a(1) = 2
a(2) = 3
a(3) = 4

Debug.Print "select * from xyz where id in (" & Join(a, ",") & ")"

answered Jun 16, 2016 at 13:54

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

4 Comments

Ralph Over a year ago

The array is the table and not a where-clause. If anything you will have to construct the table with UNION ALL. So, maybe something like this: Debug.Print "select * from (" & Join(a, " UNION ALL ") & ") as tmp"

user4637238 Over a year ago

What do you mean Ralph

Ralph Over a year ago

Debug.Print "select * from (SELECT " & Join(a, " UNION ALL SELECT ") & ") as tmp" is almost what you want. But it is still missing a column name.

user4637238 Over a year ago

Ok, So say the columns are "A", "B", "C", these are defined in the first "row" of the array.

Collectives™ on Stack Overflow

Using an array or dictionary as from clause in sql in excel vba

3 Answers 3

Update:

2 Comments

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Update:

2 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related