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=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Owner\Documents\Data.xlsm;" & _
"Extended Properties=""Excel 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=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Owner\Documents\Test.xlsm;" & _
"Extended Properties=""Excel 12.0 Macro;HDR=YES;"""


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

this works.