Pulling multiple excel values for VBA SQL query and using IN

Question

I've a below VBA code and I want to read four values a, b, c, d from an excel column (A1:A4) so those can be part of my SQL query IN statement when I connect to database. I build up the sWHEREclause to create ('a','b','c','d') string that but the final SQL query SQLStr seems to have some error and my code fails.

Can someone please help.

Thank you so much in advance.

Sub ADOExcelSQLServer()

    Dim Cn As ADODB.Connection 'connection variable
    Dim Server_Name As String ' text type
    Dim Database_Name As String
    Dim User_ID As String
    Dim Password As String
    Dim SQLStr As String
    Dim rs As ADODB.Recordset ' Database connection variable
    Dim rRange As Range
    Dim rCell As Range
    Dim sWHEREclause As String
    Set rs = New ADODB.Recordset
    
     
    Server_Name = "server name" 
    Database_Name = "Database name" 
    
    
    Set rRange = Sheet1.Range("A1:A4")

    For Each rCell In rRange
        sWHEREclause = sWHEREclause & "'" & rCell & "',"
    Next rCell
    
    
    'SQLStr = "Select * from MASTER_QUERY mq where mq.ModelNum in ('a','b','c','d')" 'commented line
    SQLStr = "Select * from MASTER_QUERY mq where mq.ModelNum  IN (" & sWHEREclause & ")"
      

    Set Cn = New ADODB.Connection 'set connection with databse
    Cn.Open "Provider=SQLOLEDB;Data Source=" & Server_Name & "; Initial Catalog=" & Database_Name & "; Integrated Security=SSPI;" 'connection parameter to the database

    rs.Open SQLStr, Cn, adOpenStatic
     ' Dump to spreadsheet
     
     
    With Worksheets("sheet1").Range("b2:z500") ' Enter your sheet name and range here
   ' Range(A1).Value = "ID)"

        .ClearContents ' clear content first in excel
        .CopyFromRecordset rs
    End With
     '            Tidy up
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
    
End Sub

Scott Craner · Accepted Answer · 2020-10-30 18:27:25Z

1

Your string ends with a ,

do your loop like this:

Dim delim as string
delim = ""
For Each rCell In rRange
    sWHEREclause = sWHEREclause & delim & "'" & rCell & "'"
    delim = ", "
Next rCell

edited Oct 30, 2020 at 18:27

answered Oct 30, 2020 at 17:56

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

Collectives™ on Stack Overflow

Pulling multiple excel values for VBA SQL query and using IN

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related