CODEWITHSUNDEEP
excelvba
Gayatri
Gayatri

Reputation: 73

Pulling multiple excel values for VBA SQL query and using IN

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

Upvotes: 0

Views: 126

Answers (1)

Scott Craner
Scott Craner

Reputation: 152525

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

Upvotes: 1

Related Questions