Insert into Excel 2007 using ADO in VBA

[ccckidd]

Not sure what is going on. Trying to insert records into new worksheet using ADO command object, it runs fine, creates the new sheet and header row, but no data?? I have tried a few things, can get Select statements to run against the connection, can even update the data in the workbook using a "Update [test$] set [col1]='a' where [col1]='1'" " statement, but no luck with this. My operating system is vista and this code resides in an Excel 2007 vba module. Thanks

Code:

cnn_xls = "Provider=Micro soft.ACE.OLEDB. 12.0;Data Source=C:\Users \Owner\Document s\Data.xlsm;" & _
"Extended Properties=""Ex cel 12.0 Macro;HDR=YES;" ""

cnn.Open cnn_xls
cnn.Execute "Select * Into [test] from [data$]"
cnn.Close
Set cnn = Nothing
End Sub

[ccckidd]

Never mind, I solved it myself, although I would like to know why this is: I was trying to "Select Into" in one worksheet in the workbook from another sheet in the same workbook. When I used the same data but in a different workbook:

cnn.Open "Provider=Micro soft.ACE.OLEDB. 12.0;Data Source=C:\Users \Owner\Document s\Test.xlsm;" & _
"Extended Properties=""Ex cel 12.0 Macro;HDR=YES;" ""


cnn.Execute "Select * Into [sheet1] from [C:\Users\Owner\ Documents\Data. xlsm].[tbl$]"

this works.