Solved

How import data from multi-tab Excel file

Posted on 2014-11-18
3
373 Views
Last Modified: 2014-11-18
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
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40450889
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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 250 total points
ID: 40450895
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
 
LVL 10

Assisted Solution

by:Gozreh
Gozreh earned 250 total points
ID: 40450957
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

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question