SQL: Insert Into Excel
Post date: May 2, 2015 12:47:36 PM
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
Tables
Used Rows
ADO cannot access password protected workbooks.
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)"
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
