Updating SQL server from an Excel sheet 
Author Message
 Updating SQL server from an Excel sheet

Does anyone have an example of the code or a macro to send an update or
insert to SQL server from Microsoft Excel.

More specifically - I will have data in 1 or more rows.  I want to write a
macro that will:
(i) if there is data in the first row of the sheet, insert the data into a
given table SQL server and then look to see if there is data in the next
row,
(ii) if there is data in the next row, it insert this in the and then look
to see if there is data in the next row.

The macro will repeat line (ii) until a row without data is found,

Jonathan Palmer
Tel:    + (45) 46 40 95 00



Fri, 29 Oct 1999 03:00:00 GMT  
 Updating SQL server from an Excel sheet

Quote:

>Does anyone have an example of the code or a macro to send an update or
>insert to SQL server from Microsoft Excel.
>More specifically - I will have data in 1 or more rows.  I want to write a
>macro that will:
>(i) if there is data in the first row of the sheet, insert the data into a
>given table SQL server and then look to see if there is data in the next
>row,
>(ii) if there is data in the next row, it insert this in the and then look
>to see if there is data in the next row.
>The macro will repeat line (ii) until a row without data is found,

The best technique will depend on the version(s) of Excel that you will be using, please specify.

The procedure would be something like

Sub SendDataToSQLServer()
  Dim rRow As Range
  For Each rRow In Range("A1").CurrentRegion.Rows
    ' send data from rRow to the database
  Next rRow
End Sub

If you are using Excel 5 then you will need to use the ODBC functions (SQLOpen, SQLExecQuery and SQLClose) to send the
data
If you are using Excel 7 or 95 you can use Data Access Objects (DAO).

The query might resemble

"INSERT MyTable (MyNumField1, MyTextField2) Values ( " & rRow.Cells(1).Value & ", '" & rRow.Cells(2).Value & "');"

--
Bill Manville
Oxford, England
Microsoft MVP - Excel



Sat, 30 Oct 1999 03:00:00 GMT  
 Updating SQL server from an Excel sheet

Hi Jonathan,

In addition to Bill Manville's comments, one issue you will face is data
validation..  With Excel (particularly 5/7), you don't have absolute
control over what a user may enter into a cell, thus you may have text in a
"number" column.  You may have to do quite a bit of validation before
writing back to SQL Server..

Tim Tow, CPA MCSD
Microsoft Excel MVP
Vice President, Consulting
LEX Software Systems, Inc.



Quote:
> Does anyone have an example of the code or a macro to send an update or
> insert to SQL server from Microsoft Excel.

> More specifically - I will have data in 1 or more rows.  I want to write
a
> macro that will:
> (i) if there is data in the first row of the sheet, insert the data into
a
> given table SQL server and then look to see if there is data in the next
> row,
> (ii) if there is data in the next row, it insert this in the and then
look
> to see if there is data in the next row.

> The macro will repeat line (ii) until a row without data is found,

> Jonathan Palmer
> Tel:       + (45) 46 40 95 00




Sat, 30 Oct 1999 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Problem in transfering data from Excel sheet to SQL Server table

2. Sending a Insert or Update command to SQL-Server from Excel

3. Write SQL from One sheet to another (Excel, VB and Oracle)

4. SQL in Excel sheet via Datacontrol

5. Convert data from an Excel sheet to SQL tabel

6. How to use SQL query with Excel Sheets?

7. How to select an excel sheet into a SQL FROM clause

8. 2nd SQL Server update after update text field corrupts row - using RDO

9. doing updating from grid to sql server with stored procedure sql 2000

10. Using RDO to do SQL updates on SQL Server 6.0

11. Problem on SQL-Update Statement with a Data-Control in VB 5 and SQL Server 7.0

12. Update SQL Syntax to Access and SQL Server

 

 
Powered by phpBB® Forum Software