PDA

View Full Version : How to use the SQL Insert Into Statement with\Excel (Reposting)



gmaxey
11-12-2013, 08:06 AM
Aftaloon,

For some reason I can't veiw your reply to my post as it returns and error page. However, I did see it in the email notification. Thanks.

I did some more nosing around last nigth and using your tip, I did manage to get something working:

I do have a very simply test spreadsheet with three columns. This is what I've put together. It works as it. It will work if I unstet the second SQL stateement line. However, if I unstet the SQL line that you provided then is errors with "The INSERT INTO statement contains the following unknown field name ..."

Any idea why? Thanks.

Also can you offer any guidance on the .Execute statement? What does it mean: Call CN.Execute(strSQL, , 1 Or 128)


Sub Test5()
WriteToWorksheet "D:\Testing.xlsx", "Sheet1", "one', 'two', 'three"
End Sub
Public Function WriteToWorksheet(strWorkbook As String, _
strRange As String, _
strValues As String)
Dim ConnectionString As String
Dim strSQL As String
Dim CN As Object
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strWorkbook & ";" & _
"Extended Properties=""Excel 12.0 Xml;HDR=YES;"";"
strSQL = "INSERT INTO [" & strRange & "$] VALUES('" & strValues & "')"
'strSQL = "INSERT INTO [" & strRange & "$] VALUES('X','Y','Z')"
'strSQL = "INSERT INTO [Sheet1$] ([F1],[F2],[F3]) VALUES ('X','Y','Z');"
Set CN = CreateObject("ADODB.Connection")
Call CN.Open(ConnectionString)
Call CN.Execute(strSQL, , 1 Or 128)
CN.Close
Set CN = Nothing
End Function