Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Keep data in fields if no update while updating | Excel Worksheet Functions | |||
Can I pivot multiple data fields and display them in a column? | Excel Discussion (Misc queries) | |||
Splitting Column Data into 2 fields | Excel Worksheet Functions | |||
function to show if two fields in a column have the same data. | Excel Worksheet Functions | |||
How to get data from pivot table with multiple column fields? | Excel Programming |