Using Excel VBA to insert multiple values into a connection query

Question

I have a working Excel spreadsheet which uses VBA to change a parameter in the connection query depending on what is entered into a single cell. It cannot use an Excel Parameter to pick up the value because the variable is in the join not in the where part of the query. So I know this works in principle, at least for one cell of data.

I now need to create a new spreadsheet where I need to put a range of data into the query.

The query looks something like this:

Select * FROM TABLE_A Where ID in ('A', 'B', 'C')

The VBA picks up the values A, B and C from a column in the spreadsheet, and currently looks like this:

Dim ID_Range As Range
Sheets("Data").Select
Set ID_Range = Sheets("Data").Range("A1:A10")
With ActiveWorkbook.Connections("Query from Database_A").ODBCConnection
    .BackgroundQuery = True
    .CommandText = Array( _
    "Select * FROM Table_A A WHERE A.ID in " "(" + ID_Range + ")")
    .CommandType = xlCmdSql
    .Connection = Array(Array( _
    ODBC;Description= ****
    .RefreshOnFileOpen = False
    .SavePassword = False
    .SourceConnectionFile = ""
    .SourceDataFile = ""
    .ServerCredentialsMethod = xlCredentialsMethodIntegrated
    .AlwaysUseConnectionFile = False
End With

I hope I haven't removed any important code when removing the company specific information.
When this is run it comes up with the error: Subscript out of Range.

What do I need to do to get this to work?

"Select * FROM Table_A A WHERE ..." Surplus A after Table_A? — , Sep 28 '17 at 12:45
Your `.Connection` uses arrays whose parenthesis are not closed — iDevlop, Sep 28 '17 at 13:08
Do you HAVE to use an ODBC connection object ? Or does another solution that quickly pulls the data from your backend suit as well ? — iDevlop, Sep 28 '17 at 13:16
@User91504 - Yes, tested that method and that works fine, thank you! peakpeak and Patrik Honorez - I chopped up my actual query to remove actual database and table names. Looks like I did a messy job. The query works, I was just trying to show the bit that didn't. Patrick Honorez ODBC question - I'm an old dog, ODBC is how I've always connected to SQL databases from Excel, Access etc. Is there a better solution these days? — Matt Hollands, Sep 29 '17 at 07:25
@MattHollands glad it worked, I find it's a very easy method to add many parameters into a query linked through excel cells — PeterH, Sep 29 '17 at 07:42

Dy.Lee · Answer 1 · 2017-09-28T13:45:56.770

Try like this.

Dim ID_Range As Range
Dim vR() As String
Dim n As Integer
Dim strRange  As String
Sheets("Data").Select
Set ID_Range = Sheets("Data").Range("A1:A10")
For Each Rng In ID_Range
    n = n + 1
    ReDim Preserve vR(1 To n)
    vR(n) = "'" & Rng & "'"
Next Rng
strRange = Join(vR, ",")

With ActiveWorkbook.Connections("Query from Database_A").ODBCConnection
    .BackgroundQuery = True
    .CommandText = Array("Select * FROM Table_A A WHERE A.ID in (" & strRange & ")")

score 0 · Answer 2 · answered Sep 28 '17 at 13:22

If you don't have a specific need of the ODBCConnection object (and I don't see its advantage in your situation) you can do this quite simply using ADODB or even good old DAO!

' Create a recordset object.Dim rsPubs As ADODB.Recordset
Set rsPubs = New ADODB.Recordset

With rsPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open "SELECT * FROM Authors"
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset rsPubs

' Tidy up
.Close
End With

cnPubs.Close
Set rsPubs = Nothing
Set cnPubs = Nothing

(code sample pasted from https://support.microsoft.com/fr-fr/help/306125/how-to-import-data-from-microsoft-sql-server-into-microsoft-excel)

score 0 · Answer 3 · answered Sep 28 '17 at 13:23

0

The best way would be to create a function to process the range

Function getCommaSeparatedList(Source As Range) As String
    Dim cell As Range
    Dim results As String
    For Each cell In Source
        results = results & "'" & cell.Value & "',"
    Next
    getCommaSeparatedList = Left(results, Len(results) - 1)
End Function

answered Sep 28 '17 at 13:23

Thanks Thomas, That's part of the solution used in the link that User91504 sent in the previous comment. – Matt Hollands Sep 29 '17 at 07:28

Using Excel VBA to insert multiple values into a connection query

3 Answers3