Importing multiple sheets from an excel file into multiple tables by sheet name

Question

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.

Solution

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

Comments

[Miguet Schwab]

Thanks for your answer grahamj42, it looks like you've gotten me just a bit closer to being done here. I'm still getting an error message but it definitely seems to be getting the correct range of fields now. The error was "The Microsoft Access Database engine could not find the objecct '$A$1:$D$1'...." Any idea where this is coming from? Thanks again.

[grahamj42]

Sorry, I forgot to put the name of the worksheet in the formula. Edited answer.

[Miguet Schwab]

Thanks for your edit. I'm still getting the same error as before though, just with the table name included now. "could not find the object 'tblName$$A$1:$D$1'" Looks like there could be an extra '$' in there?

[Miguet Schwab]

Here's a straight copy-paste: DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, ActiveSheet.Name, _ strFilePath, False, _ excelbook.Worksheets(intCounter).Name & "!" & _ excelbook.Worksheets(intCounter).UsedRange.Address

[grahamj42]

Access doesn't understand the $ in an Excel range, so I used Replace to strip them out. Answer edited. Sorry it took a while to work this out. The error message was misleading!

[Miguet Schwab]

Editing this comment, turns out I had the hasFieldNames property to false in my original code, but the first row is the column headers. With that fix I managed to get it to run properly, though I'm now working on an unrelated error. Thanks greatly for your help grahamj42!

[grahamj42]

I'm sorry to hear that we're going in circles. If you look at the spreadsheet in Excel on the problem sheet and hit Ctrl-End, does that include column F?

[Miguet Schwab]

No worries grahamj42, we got it figured out. It was my issue for missing the HasFieldNames parameter as my column headers are included in that first row. Script works now and I've updated my original post. Thanks again!

[grahamj42]

Glad to know you sorted it out. I've updated my answer to correspond with your updated question!