SQL Query with n number of WHERE-arguments in VBA

Question

I’m using ADO to run SQL query in VBA. I’ve done this quite a lot, and everything works properly.

However, I’m advancing to a more sophisticated query, where I need to input an unknown number of conditional strings. In short:

SELECT * FROM database.dbo.table
WHERE Col1 IN (‘val1’, ‘val2’, ..., ‘valn’)

I have a set of data on my worksheet, which changes every time. The data are of the same string format each time, but number of cells with values varies. I want to execute above query, using my n number of variables in the WHERE-statement.

Example of query with 5 variables from worksheet:

SELECT * FROM database.dbo.table
    WHERE Col1 IN (‘000165234’, ‘000165238’, ‘000165231’, ‘000165232’, ‘000165239’)

Any pointers to the right direction are greatly appreciated. My biggest issue is how to handle the unknown number of variables.

Constraints: will always be at least 1 cell with value, and never more than 60.

Notes: Data is also stored in an array, and does not necessarily needs to be printed on the worksheet.

Updated code

    Sub TEST()
    
        Dim ws1 As Worksheet
        Dim ws2 As Worksheet
        
        Set ws1 = ThisWorkbook.Worksheets("Sheet1")
        Set ws2 = ThisWorkbook.Worksheets("Sheet2")
        
        Dim fRow As Long
        Dim sRow As Integer
        Dim col As Integer
        Dim arr() As Variant
        Dim coll As New Collection
        
        col = 3
        sRow = 6
        
        With ws1
            fRow = .Cells(.Rows.Count, col).End(xlUp).Row
        End With
        
        With ws2
            fRow2 = .Cells(.Rows.Count, 12).End(xlUp).Row
        End With
        
        
        For i = sRow To fRow
            With ws1
                ele1= .Cells(i, 2).Value
                ele2= "000" & .Cells(i, 4).Value
                If ele1<> "" Then
                    coll.Add Array(ele2)
                End If
            End With
        Next
        
        On Error GoTo gotcha
        
        ReDim arr(1 To coll.Count, 1 To 2)
        
        For i = 1 To coll.Count
        
            arr(i, 1) = coll(i)(0)
    
        Next
        
    gotcha:
        Debug.Print Err.number
    
        If Err.number = 9 Then
            MsgBox "Error"
            Exit Sub
        End If
    
        ws2.Range("L29:M" & fRow2).ClearContents
        ws2.Range("L29").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
        
        Set conn = CreateObject("ADODB.Connection")
        
        Dim fRow3 As Long
    
        With ws2
            fRow3 = .Cells(.Rows.Count, 13).End(xlUp).Row
        End With
        
      
        Dim CONNECTION As String
        Dim QUERY As String
        
        Dim WHERE As String
       
        'Set connection and SELECT query
        CONNECTION = "Provider=*.1;Persist Security Info=True;User ID=*; Password=*; Data Source=*;Initial Catalog=*"
           
    
        selectpart = "SELECT *FROM database.dbo.table "
          


       '### The error occurs here ###            
       conditionpart = "WHERE [COL1] IN ('" & Join(arr, "','") & "')"

       GetBreakerQuantitiesQuery = selectpart & vbNewLine & conditionpart
        
       QUERY = GetBreakerQuantitiesQuery
    
        conn.Open CONNECTION
    
        Set rs = CreateObject("ADODB.Recordset")
         
        rs.ActiveConnection = conn
        rs.Open QUERY
        
        ws.Range("T6").CopyFromRecordset rs
        ws.Range("T6:AL6").Copy
        ws.Range("N7").PasteSpecial xlPasteValues, xlPasteSpecialOperationNone, True, True
        ws.Range("T6:AL6").ClearContents
        ws.Range("L6").Select
            
        rs.Close
        conn.Close
        
        Set conn = Nothing
        Set rs = Nothing
        
    End Sub

Answer 1

Dim sql as string, arr

arr = Array("000165231", "000165232", "000165239")

sql = "SELECT * FROM database.dbo.table WHERE Col1 IN ('" & Join(arr, "','") & "')"

'use sql variable for your query

Use a 1-d array:

For i = sRow To fRow
    With ws1
        If Len(.Cells(i, 2).Value) > 0 Then
            coll.Add "000" & .Cells(i, 4).Value
        End If
    End With
Next

On Error GoTo gotcha '??

ReDim arr(0 To coll.Count-1)

For i = 1 To coll.Count
    arr(i - 1) = coll(i)
Next

'....
ws2.Range("L29").Resize(UBound(arr) + 1, 1).Value = Application.Transpose(arr)