Results 1 to 4 of 4
  1. #1
    Johnny12 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2012
    Posts
    31

    VBA Import multiple Worksheets from Excel

    I found this code online (forgot where) and it works amazing when I need to combine multiple excel files. The only problem is, it only combines the first tab of each file. Many of the file groups I'd like to combine might have 2, 3 or more worksheets (tabs). How can I modify this code to combine Excel files that contain multiple worksheets. Note, each worksheet should be a separate table. Thank you in advance!



    Code:
    Private Sub btnImportSpreadsheet_Click()
    
    On Error Resume Next
     Dim strDir As String, strSQL As String
     Dim strFile As String
     Dim I As Long
     Dim Directory As String, TableName As String
     Directory = Me.txtFileName
     TableName = "MyExcelImport"
     
     I = 0
    
    
         strDir = Directory
         strFile = Dir(strDir & "*.XLSX*")
     
     While strFile <> ""
         I = I + 1
         strFile = strDir & strFile
         SysCmd acSysCmdInitMeter, "importing " & strFile
         DoCmd.TransferSpreadsheet acImport, , TableName, strFile, True 'has columnheaders
         
            ' Add column MyFileName for the source of data
            If DoesFieldExist("MyFileName", TableName) = False Then
            
            Dim strField As String
            Dim curDatabase As Object
            Dim tblTest As Object
            Dim fldNew As Object
            
            Set curDatabase = CurrentDb
            Set tblTest = curDatabase.TableDefs("MyExcelImport")
            
            strField = "MyFileName"
            Set fldNew = tblTest.CreateField(strField, dbText)
            tblTest.Fields.Append fldNew
            End If
         
                ' Run update query
                DoCmd.SetWarnings False
                strSQL = "UPDATE MyExcelImport SET MyExcelImport.MyFileName=" & Chr(34) & strFile & Chr(34) & "WHERE MyExcelImport.MyFileName IS NULL"
                DoCmd.RunSQL strSQL
                DoCmd.SetWarnings True
              
         SysCmd acSysCmdRemoveMeter
     
         strFile = Dir()
     Wend
     'importExcelSheets = I
    MsgBox I & " File(s) imported "
    End Sub

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You will not be able to use TransferSpreadsheet, you'll need to use Excel automation to set a reference to each workbook (=Excel file) and loop through its worksheets.
    See:
    https://stackoverflow.com/questions/...87353#21787353

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    rather that using transferspreadsheet, you can use sql - something like this

    Code:
    SELECT *
    FROM (SELECT * FROM [sheet1$] IN 'C:\pathtofile\filename.xlsx'[Excel 12.0;HDR=yes;IMEX=1;ACCDB=Yes])  AS XL
    change sheet1 to the name of the relevant worksheet and of course path and filename

    if you want to import, change the query from a select query to an update, insert or make table query

    if you need a specific range you can use something like

    sheet1$A to import all populated rows in those columns

    or

    sheet1$A2 to import all populated rows in those columns, starting from the second row (which is where the header is supposed to be)

    or

    sheet1$B6:G2 to import all rows in that range

    Note these will include blank rows but not those after the last populated row

    you can also do this in one hit rather than messing around adding a field

    Code:
    INSERT INTO myExcelImport
    SELECT *
    FROM (SELECT 'C:\pathtofile\filename.xlsx' AS myFileName, * FROM [sheet1$] IN 'C:\pathtofile\filename.xlsx'[Excel 12.0;HDR=yes;IMEX=1;ACCDB=Yes])  AS XL

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You might also check out Ken Snell's site: Importing from EXCEL Workbook Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-27-2016, 12:46 PM
  2. Import Excel Worksheets and Skip Certain Columns
    By vcs1161 in forum Import/Export Data
    Replies: 5
    Last Post: 07-20-2015, 04:36 PM
  3. Replies: 3
    Last Post: 03-16-2014, 08:09 PM
  4. Replies: 9
    Last Post: 10-31-2013, 06:51 AM
  5. Import Excel Worksheets into Access 2003
    By KramerJ in forum Programming
    Replies: 0
    Last Post: 03-18-2009, 04:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums