June 9, 2005 at 12:40 pm
Does anyone know if it is possible to pull data from an Excel sheet to update data in SQL tables?
I need to update two columns in a table called procure_det in SQL. We have an Excel spreedsheet that has the correct numbers to be entered. I would think I could do an Update query and tell it to match this part number from Excel to the same part number in the SQL table and then insert XX in column 1 and insert xx in column 2. Would I have to import the Excel file into Access and go from there?
June 9, 2005 at 2:29 pm
Here is an example with an excel macro that connect to a sql server without a odbc definition. Uses a querytable
Public qt As QueryTable
Sub qq()
sqlstring = "use yourdatabase UPDATE ....." & "'" & _
Worksheets("Datos").Range("C4").Value & "', '" & _
Worksheets("Datos").Range("C6").Value & "', '" & _
Worksheets("Datos").Range("L4").Value & "', '" & _
Worksheets("Datos").Range("C5").Value & "', '" & _
Worksheets("Datos").Range("C9").Value & "'"
connstring = "odbc;DRIVER=SQL Server;SERVER=yoursarver;UID=youruserid;PWD=youruserpassword;DATABASE=yourdatabase"
Set qt = Worksheets("Result").QueryTables.Add(Connection:=connstring, _
Destination:=Worksheets("Result").Range("A1"), Sql:=sqlstring)
With qt
.FillAdjacentFormulas = True
.PreserveColumnInfo = True
.PreserveFormatting = True
.AdjustColumnWidth = False
.RefreshStyle = xlOverwriteCells
.Refresh BackgroundQuery:=False
End With
End Sub
sqlstring = here is the sql command for select, update or a store procedure
Destination = put the destination cell even if you get a result or not
June 14, 2005 at 12:20 pm
Another way to do this is to use DTS. Essentially, in the dts, you need to import the excel file into a temp table. Then use the SQL Task to update the data in the desired table from the temp table.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply