Update Excel Column Data from SQL Fields
 

I have a spreadsheet that contains part numbers (Col. A), description (Col.
B), and cost (Col. C). I would like to click a button and the descriptions
and costs of each part number will be updated automatically from the SQL
Database. What would be the best way to go about doing this?

Thanks in advance!
--
Cheers,
Ryan

Update Excel Column Data from SQL Fields
 

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

Update Excel Column Data from SQL Fields
 

hi
i would suggest setting up a MS Query and tie the refresh to your button.
or there is a refresh icon on the external data toolbar that could be used
also.
on the menu bar....
dataimport external datanew database query.
follow the wizard.

regards
FSt1

"Ryan H" wrote:

I have a spreadsheet that contains part numbers (Col. A), description (Col.
B), and cost (Col. C). I would like to click a button and the descriptions
and costs of each part number will be updated automatically from the SQL
Database. What would be the best way to go about doing this?

Thanks in advance!
--
Cheers,
Ryan