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

excel, ms-access, vba

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

Problem

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.

Original source