Importing Excel worksheets into Access 2003

[h20vrrq]

I have to set up an Access database that has to import data from several hundred Excel Workbooks.

In each Excel Workbook there will be three types of worksheets, a Base sheet, a budget sheet and a project sheet.
In each workbook there will always be one of each type of worksheet, but there could be anything up to 30 budget or project sheets. Obviously each worksheet has a unique name but these will be very similar, e.g. project(1), project(2) etc.
Within Access, each type of sheet has to go into a separate table, one each for Base, Budget & Project.
All of the Excel Workbooks will be stored in the same directory.

Is there anyway of bulk importing all of these worksheets in one go?

As a bit of background, I work for a UK Charity and we have a number of remote users who do not have Access only Excel, they will be completing these Workbooks and then returning them to Head Office for amalgamating into one database. Not a good solution I know, but with limited expertise and knowledge its as far as we have got.

Any help is very much appreciated.

 

[Remou]

Here is a start:

Sub GetExcel()
'Requires reference to the Microsoft Excel x.x Object Library

Dim strFileName As String
Dim objXL As Excel.Application 'Object
Dim wkb As Excel.Workbook
Dim wks As Object

Set objXL = CreateObject("Excel.Application")
objXL.Visible = True

strFileName = "C:\Docs\LTD.xls"
Set wkb = objXL.Workbooks.Open(strFileName)

For Each wks In wkb.Worksheets
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, wks.Name, strFileName, True, wks.Name & "$"
Next

End Sub

From:

http://wiki.lessthandot.com/index.php/Import_All_Excel_Worksheets_to_Separate_Tables_in_Access