Solved

How import data from multi-tab Excel file

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

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

726 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