(Solved) – Excel Vba Search recordset for cell value


I’m using the below code to compare individual cells in the A column against an sql db which I need to retrieve some value for for comparison but it seems an expensive way to do this as pressently I’m running a separate sql query for each cell in the range.. It works ok but I was wondering if there is a better method to do this by storing the data in a recordset.

' Open connection to the SQL Server database
adoDbConn.Open "Provider=SQLOLEDB;Data Source=xxxxxxxxx;Initial Catalog=xxxx;User Id=xxxx; 

Dim varname1 As String
varname1 = ""

 Dim FoundCell As Range

Dim rng As Range, cell As Range
Set rng = Range("A:A")
For Each cell In rng
If Not IsEmpty(cell.Value) Then
  varname1 = varname1 & "select itemno, price " & vbCrLf
  varname1 = varname1 & "from Stock where itemno = '"   cell.Value   "'"

' Execute the select query
   selectCmd.ActiveConnection = adoDbConn
   selectCmd.CommandText = varname1

   Set adoDbRs = selectCmd.Execute(, , adCmdText)
   'make recordcount work..
   adoDbRs.CursorType = adOpenKeyset

   If adoDbRs.RecordCount > 0 Then
   ws.Cells(cell.Row, 6) = adoDbRs.Fields.Item("price").Value
   ws.Cells(cell.Row, 6) = "N/A"
   End If
   varname1 = ""
 End If

Next cell

Leave a Reply

Your email address will not be published. Required fields are marked *