Export Excel Data Into Access Via DBO

[mosh]

All,

I am trying to export excel data into an access table via a command button.

This is the code I have so far in excel;

Private Sub CommandButton1_Click()

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\temp\test.mdb;"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Table1", "C:\sr.xls", True, "A1:B3"
End Sub



But i get a run time error 424, Object required. I am running office 2003, i think the reason why it's not working is because I haven't referenced the correct ADO library?

Help please.

Thanks
________
Avandia lawsuit

 

[wjburke2]

I am facing a simular problem. This may help follow this link.
http://blogs.techrepublic.com.com/msoffice/?p=376