How import data from multi-tab Excel file

I have a multi-tab (worksheet) Excel file.  The file has 79 worksheets total.  Each worksheet has the exact same headers, and they are all named exactly the same.  I could manually import or append all 79 of them into one table but that would take a long time.

What I really want to do is click a command button on a form that will loop through all of the worksheets in the Excel file AFTER is allows me to navigate to the Excel file, and import the data into a single table.

Is this even possible?  If so I have no idea how to begin writing the code.

--Steve
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I would approach it the other way - how can you combine the 79 worksheets into one sheet in Excel, so it can then be imported.

Have a look at this page: http://www.extendoffice.com/documents/excel/1184-excel-merge-multiple-worksheets-into-one.html

It gives a solution in VBA code if you are OK with that, and a product to do the job for you if you are not.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
If you are using Excel 2007 or later, I would change the line

Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)

Change it from 65536 to 1000000
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GozrehCommented:
This code will import you all sheets to access, then you can write code to append to data to your table and delete the imported table.
Sub ImportExcel()
   Dim excelApp As Object 'New Excel.Application
   Set excelApp = CreateObject("Excel.Application")
   Dim excelbook As Object 'New Excel.Workbook
   Set excelbook = excelApp.Workbooks.Add
   Dim intNoOfSheets As Integer, intCounter As Integer
   Dim strFilePath As String
   
   Dim dlg As FileDialog
   Set dlg = Application.FileDialog(msoFileDialogFilePicker)

   With dlg
      .Title = "Select the Excel file to import"
      .AllowMultiSelect = False
      .Filters.Clear
      .Filters.Add "Excel Files", "*.xls", 1
      .Filters.Add "All Files", "*.*", 2
      
      If .Show = -1 Then
         strFilePath = .SelectedItems(1)
         
         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, excelbook.Activesheet.Name, strFilePath, True, _
            excelbook.Worksheets(intCounter).Name & "!" & _
            Replace(excelbook.Worksheets(intCounter).UsedRange.Address, "$", "")
            
            'Appent excelbook.Worksheets(intCounter).Name to your table
            'Delete excelbook.Worksheets(intCounter).Name
         Next
      
         excelbook.Close
         excelApp.Quit
      End If
   End With

   Set excelApp = Nothing
End Sub

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.