Results 1 to 6 of 6
  1. #1
    DukeRollo is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    2

    Multiple Excel Files

    Access novice here. Thank you in advance for your attention and assistance.



    I want to set up a query to be able to match a dollar amount with other information (such as names and dates). That I know how to do for one table.

    However, what I'm up against is a high number of excel tables stored in different places on a networked drive. There are 5 separate excel files for each business day and this query needs to go back a full year. Each excel file has one tab with between a few hundred and a few thousand rows of information (about 10 columns on each)

    Also (but wait... there's more!), I'll need to be able to add in more excel sheets as time goes on (again, 5 a day).

    Can this even be done? My first thought was "throw everything into one ridiculously big Excel file with an obscene number of tabs" but I'd like to try to avoid that if at all possible.

    Thank you for taking the time to read and consider this. Any assistance you can give is greatly appreciated.

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Initial thought is to link those excel files into your database. Then create a Union query to combine all the linked excel tables into 1 big dataset. Of course you would need to make changes as you add more excel files but don't see a way around that.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    this may help, submit the folder where all the excel books are then it will import them all.
    This assumes all sheets in all workbooks are formated the same for import.
    Paste this code into a new module, Ctl-F11, new module, then paste, then save.

    you must inbed the REFERENCE to excel, VBE menu: tools , references, Microsoft Excel x.x object

    submit your folder to the routine below:
    usage:
    ImportAllSheetsAllFiles1Dir "c:\temp"


    Code:
    Public mXL As excel.Application
    
    '--------------
    Public Sub ImportAllSheetsAllFiles1Dir(ByVal pvDir)
    '--------------
    Dim vFil, vTargT
    Dim i As Integer
    Dim sTbl As String, sSql As String
    Dim FSO, vSht
    Dim oFolder, oFile
    Dim colSheets As New Collection
    
    
    On Error GoTo errImp
    Set mXL = CreateObject("excel.application")   'start excel instance
    
    If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"
    
    sTbl = "tData"
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = FSO.GetFolder(pvDir)
    
    
    For Each oFile In oFolder.Files
        If InStr(oFile.Name, ".xls") Then
            vFil = pvDir & oFile.Name
            
            Set colSheets = getSheetNames(vFil)    'get all sheets in workbook
        
                         'import all sheets        
            For Each vSht In colSheets
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, sTbl, vFil, True, vSht
               'debug.print vSht & vbTab & vFil
            Next
        End If
    Next
    mXL.Quit
    
    
    Set FSO = Nothing
    Set oFile = Nothing
    Set oFolder = Nothing
    Set mXL = Nothing
    Exit Sub
    
    
    errImp:
    MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
    Exit Sub
    Resume
    End Sub
    
    
    
    Public Function getSheetNames(ByVal pvFile) As Collection
    Dim col As New Collection
    Dim sht
    
    
    On Error GoTo errSht
    With mXL
      .Workbooks.Open pvFile
      For Each sht In .Worksheets
          col.Add sht.Name
      Next
    End With
    
    
    endit:
    mXL.ActiveWorkbook.Close False
    
    Set getSheetNames = col
    Exit Function
    errSht:
    MsgBox Err.Description, , "getSheetNames():" & Err
    return Endit
    End Function

  4. #4
    DukeRollo is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Nov 2017
    Posts
    2
    This is amazing. Thank you.

    For simplicity sake (as I might need to hand this process off to someone who's close to Access illiterate in the future...) is there a way to import a whole folder worth of info without using code?

    Thank you again.


    Quote Originally Posted by ranman256 View Post
    this may help, submit the folder where all the excel books are then it will import them all.
    This assumes all sheets in all workbooks are formated the same for import.
    Paste this code into a new module, Ctl-F11, new module, then paste, then save.

    you must inbed the REFERENCE to excel, VBE menu: tools , references, Microsoft Excel x.x object

    submit your folder to the routine below:
    usage:
    ImportAllSheetsAllFiles1Dir "c:\temp"


    Code:
    Public mXL As excel.Application
    
    '--------------
    Public Sub ImportAllSheetsAllFiles1Dir(ByVal pvDir)
    '--------------
    Dim vFil, vTargT
    Dim i As Integer
    Dim sTbl As String, sSql As String
    Dim FSO, vSht
    Dim oFolder, oFile
    Dim colSheets As New Collection
    
    
    On Error GoTo errImp
    Set mXL = CreateObject("excel.application")   'start excel instance
    
    If Right(pvDir, 1) <> "\" Then pvDir = pvDir & "\"
    
    sTbl = "tData"
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = FSO.GetFolder(pvDir)
    
    
    For Each oFile In oFolder.Files
        If InStr(oFile.Name, ".xls") Then
            vFil = pvDir & oFile.Name
            
            Set colSheets = getSheetNames(vFil)    'get all sheets in workbook
        
                         'import all sheets        
            For Each vSht In colSheets
                DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, sTbl, vFil, True, vSht
               'debug.print vSht & vbTab & vFil
            Next
        End If
    Next
    mXL.Quit
    
    
    Set FSO = Nothing
    Set oFile = Nothing
    Set oFolder = Nothing
    Set mXL = Nothing
    Exit Sub
    
    
    errImp:
    MsgBox Err.Description, vbCritical, "clsImport:ImportData()" & Err
    Exit Sub
    Resume
    End Sub
    
    
    
    Public Function getSheetNames(ByVal pvFile) As Collection
    Dim col As New Collection
    Dim sht
    
    
    On Error GoTo errSht
    With mXL
      .Workbooks.Open pvFile
      For Each sht In .Worksheets
          col.Add sht.Name
      Next
    End With
    
    
    endit:
    mXL.ActiveWorkbook.Close False
    
    Set getSheetNames = col
    Exit Function
    errSht:
    MsgBox Err.Description, , "getSheetNames():" & Err
    return Endit
    End Function

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    nope. each file must be done manually,
    or use code to get them all.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    However, what I'm up against is a high number of excel tables stored in different places on a networked drive. There are 5 separate excel files for each business day.
    You know the network places (directories)? Are the Excel workbooks in the same directories every month? How are the 5 workbooks named?


    and this query needs to go back a full year.
    A query can pull data for a year if the data is in the table(s).


    Each excel file has one tab with between a few hundred and a few thousand rows of information (about 10 columns on each)
    You can import the data into temp tables, then use queries/code to manipulate the data into the proper table(s).
    Does each Excel workbook in each of the 5 network locations have the same structure or would the 5 different workbooks go into 5 different tables?


    Examples of how to import using code: http://www.accessmvp.com/kdsnell/EXCEL_Import.htm
    The example code can be modified to import the 5 workbooks.........

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

Similar Threads

  1. Export one query to multiple Excel files
    By bliffer in forum Import/Export Data
    Replies: 3
    Last Post: 01-29-2014, 02:37 PM
  2. Replies: 2
    Last Post: 08-27-2013, 06:29 AM
  3. Replies: 7
    Last Post: 12-09-2012, 06:20 PM
  4. Linking multiple Excel files - PLEASE help!
    By studor63 in forum Import/Export Data
    Replies: 4
    Last Post: 09-11-2012, 01:24 PM
  5. Replies: 4
    Last Post: 06-14-2011, 07:19 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