Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

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
0
SteveL13
Asked:
SteveL13
  • 2
2 Solutions
 
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
 
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now