Importing multiple sheets from an excel file into

Hi there and thanks for your help. I'm putting together a seemingly simple Access data management solution for use in our office here and I'm having trouble as my background in vba is minimal at best.

What I have here are two related but disconnected Access 2007 applications and I need a system for the user to easily import and export this information. I've got a script working now to export all the tables from the application into a single excel file with each table as a different worksheet, the issue is that when I go to import it only seems to find the first sheet for import.

What I'm hoping to find is a method to iterate over each worksheet, grab the sheet name, and then merge that data into the table based on the sheet name.

To clarify:

Multiple copies of App A are sent to various departments
App A: Users enter information into the tables
App A: Users press command to run export macro
Excel file is created with each table as a sheet with matching names (e.g. tblCourse, tblStudent, tblFaculty, etc.)
Users of App B receive excel spreadsheet
App B: Users press command to run import script (This is the solution I'm looking for)
- User is prompted for file location
- Import script opens excel workbook
- Script iterates over each sheet, reads name, and imports data to table of matching name

Thanks in advance for any help you can provide, much appreciated.

Edit

Working script (Thanks greatly to grahamj42's help here):

Private Sub Command101_Click()

'Dim excelapp As New Excel.Application
Dim excelApp As Object
Set excelApp = CreateObject("Excel.Application")
'Dim excelbook As New Excel.Workbook
Dim excelbook As Object
Set excelbook = excelApp.Workbooks.Add
'Dim excelsheet As New Excel.Worksheet
'Dim excelsheet As Object
'Set excelsheet = excelbook.Sheets
Dim intNoOfSheets As Integer, intCounter As Integer
Dim strFilePath As String, strLastDataColumn As String
Dim strLastDataRow As String, strLastDataCell As String

strFilePath = "C:\Users\UserName\Documents\Export\DatabaseExport03-28-2013.xlsx"

Set excelbook = excelApp.Workbooks.Open(strFilePath)

 intNoOfSheets = excelbook.worksheets.Count

 Dim CurrSheetName As String

 For intCounter = 1 To intNoOfSheets
  excelbook.worksheets(intCounter).Activate
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, ActiveSheet.Name, _
   strFilePath, True, _
   excelbook.Worksheets(intCounter).Name & "!" & _
   Replace(excelbook.Worksheets(intCounter).UsedRange.Address, "$", "")
 Next

excelbook.Close
excelApp.Quit
Set excelApp = Nothing

End Sub

Note that in the DoCmd.TransferSpreadsheet command there is a HasFieldNames property that is set to 'True' here because my spreadsheets export with the field names as column headers.

Your use of Selection without selecting anything will refer to the selected cell in the worksheet when it was saved. While I don't see, in your case, why this should be outside the table, you could do better like this without selecting anything by using Worksheet.UsedRange:

For intCounter = 1 To intNoOfSheets

  DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, Activesheet.Name, _
    strFilePath, True, _
    excelbook.Worksheets(intCounter).Name & "!" & _
    Replace(excelbook.Worksheets(intcounter).UsedRange.Address, "$", "")

Next