I'm not sure if you have a lot of product numbers, if this represents
everything in the table or only a subset, or if you can easily define
the subset in a query.
It would be more straightforward to overwrite the speadsheet with a
straight dump of the table or a dump of some sql statement that limits
the products to only the ones you want to see. However, I'll assume
that you only have a few hundred products in the sheet and this is a
small subset of the table, without the ability to easily write a sql
statement to find only the ones you want. Maybe this will give you a
start.
You need a reference to Microsoft ActiveX Data Objects for the code
below to work
Public Const SQLConnection As String = "Data Source=[server
name];Initial Catalog=[database name];Network=[windows nt
network];User Id=[username];Password=[password]"
Public Sub UpdateInformation()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String, sResult As String
Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.Provider = "SQLOLEDB"
cn.Open SQLConnection
Set rs = New ADODB.Recordset
cn.CommandTimeout = 600
Dim sht As Worksheet, lRow As Long
Set sht = ActiveSheet
For lRow = 2 To sht.UsedRange.Rows.Count
sql = "select description, cost from productinfo where
productnum='" & sht.Cells(lRow, 1).Value & "'"
rs.Open sql, cn, adOpenStatic, adLockReadOnly
If Not rs.EOF Then
sht.Cells(lRow, 2).Value = rs("description").Value
sht.Cells(lRow, 3).Value = rs("cost").Value
End If
rs.Close
Next lRow
cn.Close
Set rs = Nothing
Set cn = Nothing
Set sht = Nothing
End Sub
