Export Excel Data into Access via button

[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
________
COLORADO MEDICAL MARIJUANA

 

[John Big Booty]

Why not go the other way and import the Excel file into Access?

 

[Bilbo_Baggins_Esq]

I think your code looks like it is trying to import, rather than export.

acImport, from the perspective of the running Excel code, is incoming, not out going.

I also wonder why you would not be importing the excel file with code from within Access.

 

[mosh]

I think your code looks like it is trying to import, rather than export.

acImport, from the perspective of the running Excel code, is incoming, not out going.

I also wonder why you would not be importing the excel file with code from within Access.

I've changed it to acExport, but still getting the same problem. I Guess I could do it through Access, but it would be a little more hassle doing it that way.

Any ideas?

Thanks
________
Infant Avandia

 

[Bilbo_Baggins_Esq]

Do you have Access Objects and Office Objects referenced.

I've not tried to specifically do what you're trying, but if I'm not mistaken "ac" refers to Access objects and "xl" refers to Excel objects.

 

[mosh]

Do you have Access Objects and Office Objects referenced.

I've not tried to specifically do what you're trying, but if I'm not mistaken "ac" refers to Access objects and "xl" refers to Excel objects.

I might need help as to which I should reference, I'm using Office 2003.
________
Eve

 

[chergh]

Wouldn't you have to open access to use docmd.transferspreadsheet ?

If you create a named range for what you want to import you could use a sql string to import the data without actually having to open access though.

 

[mosh]

Wouldn't you have to open access to use docmd.transferspreadsheet ?

If you create a named range for what you want to import you could use a sql string to import the data without actually having to open access though.

sounds interesting, can you please help me?
________
The Cliff Condominiums Cosy Beach

 

[Bilbo_Baggins_Esq]

As I mentioned, I've never tried to export from Excel to Access. I still believe you would be better off doing it reverse, importing to Access from Excel.

However, here is some VB code I use to export from a VB app to Access using ADODB

        Set adoRecordset = New ADODB.Recordset
    With adoRecordset
        .Open "SELECT Page_Number,Page_Time,Update_Date,Update_Time FROM Stage_1_Log", adoConnection, adOpenKeyset, adLockOptimistic
        .AddNew
        .Fields("Page_Number").Value = Search_Page_Counter
        .Fields("Page_Time").Value = Page_Time
        .Fields("Update_Date").Value = Page_Updated_Date
        .Fields("Update_Time").Value = Page_Updated_Time
        .Update
        .Close
    End With
    Set adoRecordset = Nothing

I have no idea if this code will work in VBA in Excel.
Basically the values are asigned to variables and then transferred to the database via ADODB one record at a time.
If you're wanting to send whole columns of a data table, something similar may be applicable, but as I mentioned, I've never tried it.