Solved

How import data from multi-tab Excel file

Posted on 2014-11-18
3
364 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now