Is it possible to put the output from the SQL query (written in Excel VBA) directly into a comma separate string variable, rather than having to add it to a Worksheet as in the below code and THEN add it to an array? I just need the SQL output (id's) added directly in a comma separate variable and bypass the worksheet. The ID's are all numeric and there can be 50K of them
Const sqlconnection = "Provider=sqloledb;"
conn.ConnectionString = sqlconnection
conn.Open
Dim arrData As Variant
Dim rs As Recordset, output As String
Dim v As String
v = "SELECT id FROM table"
Set rs = conn.Execute(v)
With wb.ActiveSheet.QueryTables.Add(Connection:=rs, Destination:=Range("A1"))
.Refresh
End With
arrData = wb.ActiveSheet.Range("A2").CurrentRegion.Columns(1).Value
Set Dic = CreateObject("Scripting.Dictionary")
For idx = 2 To UBound(arrData, 1)
If arrData(idx, 1) <> "" Then
Dic(arrData(idx, 1)) = ""
End If
Next idx
output = Join(Dic.Keys, ",")
Debug.Print output
There is an easy method GetRows
for a ADODB recordset that reads all data from a recordset into a 2-dimensional array (note that even if you read only one column, the result is 2-dimensional). Only thing you need to know is that the first dimension is the field index and the second dimension the row number, which I found always a little bit counterintuitive. Both dimensions are 0-based.
Unfortunately the Join
-command doesn't work on 2-dimensional arrays, so you need to create a 1-dimensional array out of it manually. Of course you can also use a dictionary as you do in your code.
v = "SELECT id FROM table"
Set rs = conn.Execute(v)
' Read all the data into 2-dimensional array
Dim mydata
mydata = rs.GetRows
' Create a 1-dimensional array
Dim i As Long
ReDim a(LBound(mydata, 2) To UBound(mydata, 2))
For i = LBound(mydata, 2) To UBound(mydata, 2)
a(i) = mydata(0, i)
Next
Dim output As String
output = Join(a, ",")
Debug.Print output