Exporting data form Excel lto Access

[coughnut]

I am trying to export some data that I have in an excell spread sheet, to a table in access. I am trying to do this in VBA in Access.... I am totally lost, can any one please help me. Thanks...

 

[Remou]

Have you looked at DoCmd.TransferSpreadsheet?

 

[coughnut]

NO I have not, but how does this function works. I have never done any VBA before.

 

[Remou]

You could use it in a module, or as a command for a button:

Sub cmdExport_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Tablename", "C:\Data\TekTips.xls"
End Sub

Just type DoCmd.TransferSpreadsheet and select suitable options from those offered by intellisence.
You could also create a macro: choose TransferSpreadsheet from the drop down list of actions and fill in the required information.

 

[coughnut]

Thanks alot I will give that a try...

 

[coughnut]

What about if I have a spreadsheet with multiple work books. Let say I want to import Sheet2. Here is how I am doing it: DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9 _
, "theTable", "C:\temp\Test.xls", , "Sheet2"

and I ge the error "Could not find the object 'Sheet2'

 

[coughnut]

never mind got it...