PDA

View Full Version : How to use the SQL Insert Into Statement w/Excel



gmaxey
11-11-2013, 09:56 PM
I'm practically virgin in this fields, so be gentle.

I'm trying to figure out if it is possible to write a new record to an Excel database using ADO. The following is what I've cobbled together, but I know that I am simply missing a critical piece or maybe even the whole point.

Thanks.


Sub Test()
Dim oConn As Object
Dim strSQL As String
Set oConn = GetExcelConnection("D:\Testing.xlsx", False)
strSQL = "INSERT INTO [Sheet1$] (column1,column2,column3) VALUES (value1,value2,value3);"
oConn.Execute strSQL
If oConn.State = 1 Then oConn.Close
Set oConn = Nothing
End Sub
Private Function GetExcelConnection(ByVal Path As String, _
Optional ByVal Headers As Boolean = True) As Object
Dim strConn As String
Dim oConn As Object
Set oConn = CreateObject("ADODB.Connection")
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & Path & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=NO"";"
oConn.Open ConnectionString:=strConn
Set GetExcelConnection = oConn
End Function

Aflatoon
11-12-2013, 07:06 AM
As long as you have at least three columns of data present already, you can use:
strSQL = "INSERT INTO [Sheet1$] ([F1],[F2],[F3]) VALUES ('value1','value2','value3');"