Macro to copy several excel-sheets into one access table

[janvan]

Dear all,

my problem was the following: i had to make a Pivot Table in MS Excel, but my data reached over the 65536 rows...

So one solution was to use the consolidaded pivot tables in Excel, but I was not very satisfied with it, as it doesn't gives you all the information a normal pivot table gives you...

The other solution was to export the different sheets into a MS Access table, and to build a pivot table in MS Excel using the option "External Source" (being the MS Access-table). This is definetly the best solution to me...

Now I would like to make a macro which automatically copies the different Excel-sheets into one MS-Access table...
Could anyone help me with this?

Thanks in advance!!!!!

Jan

 

[Mile-O]

Look up the TransferSpreadsheet method in a macro.

 

[RaunLGoode]

Close to what I need

This sounds close to what I want to do.
I use Access 2000, Office 2000, and Win2000

I have taken the data I need from "sheet1" in an existing Excel file and moved it to "sheet2" where I cleaned it up for my Access table. I can go to Access and, using Get External Data>Import, directly update a table.
I really want to do this process from Excel, including defining the path to the Access table.
Is the TransferSpreadsheet method something I should be able to find from "help" in the Visual Basic Editor ? I didn't see it in Access or Excel.