Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SunnyDee
Yak Posting Veteran
79 Posts |
 Posted - 2007-01-16 : 16:44:49
|
| Hello:I've been struggling with this for a few days, and it's driving me crazy. I need to UPDATE SQL server from Excel. I am able to connect to the Excel spreadsheet and insert the data from Excel to SQL using slightly modified code from microsoft web site:Dim cn As ADODB.Connection Dim strSQL As String Dim lngRecsAff As Long Set cn = New ADODB.Connection cn.Open "provider=sqloledb;data source=(local)\bird;initial catalog=cat;user id=fish;password=monkey;database=cat" 'Import by using OPENDATASOURCE strSQL = "SELECT * INTO XLImport8 FROM " & _ "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _ "'Data Source=C:\test.xls;" & _ "Extended Properties=Excel 8.0')...[Sheet1$]" Debug.Print strSQL cn.Execute strSQL, lngRecsAff, adExecuteNoRecords Debug.Print "Records affected: " & lngRecsAff However, I'm not sure of the syntax to update sql from excel. I've been searching for a few days and haven't come across it yet. Can someone please help me out? I especially need help with the visual basic syntax that updates a row in sql based on the values in Excel. I'm assuming I will need to create a recordset with the Excel data and start at the BOF and read each cell, but I need help. Thanks in advance. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-16 : 17:29:52
|
| The path to the Excel file is relative to the SQL Server you use, not from where the query is fired.Peter LarssonHelsingborg, Sweden |
 |
|
|
SunnyDee
Yak Posting Veteran
79 Posts |
Posted - 2007-01-17 : 08:38:24
|
| I am not sure I understand your reply. I am already to connect to SQL from Excel. This is a distributed query.I'm looking for any information on syntax on updating from excel to SQL. Will the excel file behave as a record set? How does that work? |
|
|
|
|
|
|
inserting from excel to SQL with visual basic