Add Records to Excel

SQL: Insert Into Excel

SQL can add records to Excel worksheets even if the workbooks they are in are closed or worksheets protected.

The basic method for writing to Excel worksheets is the same as normal databases.

Create a Connection Object
Create an SQL "Insert Statement"
Execute the SQL Insert Statement using the Connection Object.
When finished with all updates, close the Connection Object

Example:

Below is an empty workbook where Sheet1 contains three column headings: ID, Name, and Amt. I protected the worksheet (just for demonstration), saved the workbook to C:\Temp\Protected.xlsx and closed it.

I opened another workbook and added the following code to a normal code module then called it from the immediate window.

Sub Write2XL()

Dim oCn As Object

Set oCn = CreateObject("ADODB.Connection")

oCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=C:\Temp\Protected.xlsx;" & _

"Extended Properties=Excel 12.0;"

oCn.Execute "Insert Into [Sheet1$] (ID,Name,Amt) Values(1,'Bob',3.10)"

oCn.Close

End Sub

I then opened Protected.xlsx and this is what it contained.

Restrictions!

Workbook Protection

ADO cannot access password protected workbooks.

Tables

ADO cannot access XL's Table objects (aka Lists) nor can it access range names that directly or indirectly reference the table's name or structured references. ADO can access named ranges over tables that reference the table's cell range. Example: Name MyTable Refers to "=$B$2:$D$3". In this case our Insert statement could change to:

oCn.Execute "Insert Into MyTable (ID,Name,Amt) Values(1,'Bob',3.10)"

Used Rows

ADO will insert below the last used row. NOTE! The last used row can be empty.

References:

Connection Objects: http://msdn.microsoft.com/en-us/library/ms807027.aspx

Connection Strings: http://www.connectionstrings.com/textfile

SQL Insert Statment: http://www.w3schools.com/sql/sql_insert.asp

Page updated

Google Sites

Report abuse