Loop through excel spreadsheet using macro

Question

I'm trying to loop through excel cells to get value from sql server table. I've written a macro that will take the jobnumber from the excel and query sql server tale to get customer details and dump it on the spreadsheet. So far I'm only able to get data for single cell. How can I loop through the excel cells for more than one jobnumber. I'm new to VBA. Thanks for your help. Here is my macro:

Sub ConnectSqlServer()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim newrow As String



newrow = Worksheets("Sheet1").Cells(1, "A").Value

' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=0.0.0.0;" & _
              "Initial Catalog=asset;" & _
              "User ID=Temp;Password=test123;"

' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

' Open the connection and execute.
conn.Open sConnString
Set rs = conn.Execute("SELECT customer FROM job_tab where jobnum2='" & Trim(newrow) & "';")

' Check we have data.
If Not rs.EOF Then
    ' Transfer result.
    Sheets(1).Range("B1").CopyFromRecordset rs
' Close the recordset
    rs.Close
Else
    MsgBox "Error: No records returned.", vbCritical
End If

' Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing

End Sub

Ichixgo Ichixgo · Accepted Answer · 2016-04-05T15:14:53

You can do so, by using the IN SQL statement:

SELECT customer FROM job_tab WHERE jobnum2 IN ( 'Value1', 'Value2', ... );

Therefore you have to loop through all the customers in column A of your table and alter the SQL request to use the above mentioned statement:

Sub ConnectSqlServer()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim newrow As String


'MODIFIED: create the search string for the IN-Statement
newrow = "("
For i = 1 To Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, "A").End(xlUp).Row
    newrow = newrow & "'" & Trim(Worksheets("Sheet1").Cells(i, "A").value) & "',"
Next i
newrow = Left(newrow, Len(newrow) - 1)
newrow = newrow & ")"

' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=0.0.0.0;" & _
              "Initial Catalog=asset;" & _
              "User ID=Temp;Password=test123;"

' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

' Open the connection and execute.
conn.Open sConnString
'MODIFIED: altered the SQL statement to use the search string with IN
Set rs = conn.Execute("SELECT customer FROM job_tab where jobnum2 IN " & newrow & "';")

' Check we have data.
If Not rs.EOF Then
    ' Transfer result.
    Sheets(1).Range("B1").CopyFromRecordset rs
' Close the recordset
    rs.Close
Else
    MsgBox "Error: No records returned.", vbCritical
End If

' Clean up
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing
End Sub

Loop through excel spreadsheet using macro

1 Answers