Results 1 to 7 of 7

Import multiple xls from a few subfolders

  1. #1
    artec is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    11

    Import multiple xls from a few subfolders

    Hi, I'm lookingo for the way how to iport multiple xls files fro a few subfolders. I have a lot of files with the same names in a different subdir's. For example: Dir name - OFFICE , Subdir's - AA (with 2012.xls, 2013 xls...), AB (with 2012.xls, 2013 xls...), BA (with 2012.xls, 2013 xls ...).
    All xls files are with the same structure (the same header but with different value of records)
    This code import all files to the table TEMPORARY only from one subdir. I can add next subdir manually but I need to check "Import all files from head dir Y:\OFFICE\ .For red text color i see " No file in dir". Is it possible to modify this code (MS OFFICE 2010) ?. Thanks for interesting.

    Code:
    Private Sub Polecenie13_Click()
    'Directory Path
    Dim strPath As String
    Dim plik As String
    Dim katalog() As String
    Dim i As Integer
    If Me.Lista0 = "ALL" Then
      strPath = "Y:\OFFICE\"
    End If
    If Me.Lista0 = "AA" Then
      strPath = "Y:\OFFICE\AA\"
    End If
    If Me.Lista0 = "AB" Then
      strPath = "Y:\OFFICE\AB\"
    End If
    'Filename
    Dim strFile As String
    'File Array
    Dim strFileList() As String
    'File Number
    Dim intFile As Integer
    DoCmd.SetWarnings False
    strFile = Dir(strPath & "*.xls")
    Do Until strFile = ""
      intFile = intFile + 1
      ReDim Preserve strFileList(1 To intFile)
      strFileList(intFile) = strFile
      strFile = Dir()
    Loop
    If intFile = 0 Then
      MsgBox "No file in dir"
      Exit Sub
    End If
    For intFile = 1 To UBound(strFileList)
      DoCmd.TransferSpreadsheet acImport, , _
      "TEMPORARY", strPath & strFileList(intFile), True, "!A1:BG339"
      DoCmd.RunSQL "UPDATE TEMPORARY SET [INITIAL] = '" & Me.Lista0.Value & "' WHERE [INITIAL] is null ;"
    Next
    DoCmd.SetWarnings True
    MsgBox UBound(strFileList) & " files imported"
    End Sub
    Last edited by June7; 03-24-2012 at 12:17 PM. Reason: Mod edit: Add indentation for readability

  2. #2
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    27,159
    Exactly what is it you need? You want the code to automatically find all the subfolders in the given folder OFFICE and import all the Excel files from each subfolder?

    There are many examples of code to automate listing and working with contents of folders/subfolders/files. Google: vba list folders and files

    http://www.ozgrid.com/forum/showthread.php?t=61185

    http://www.mrexcel.com/forum/showthread.php?t=344559
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    artec is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    11
    Yes, there is exactly what I need. I found this site http://allenbrowne.com/ser-59.html and I did it after made module
    To show the files in a list box:
    1. Create a new form.
    2. Add a list box, and set these properties:
      Name lstFileList
      Row Source Type Value List
    3. Set the On Load property of the form to:
      [Event Procedure]
    4. Click the Build button (...) beside this. Access opens the code window. Set up the event procedure like this:
      Private Sub Form_Load()
      Call ListFiles("Y:\OFFICE\", , , Me.lstFileList)
      End Sub
    In result I have a list filee with path's and names of xls files but still I don't know how to automate read (import) it to a table by using DoCmd.TransferSpreadsheet acImport.

  4. #4
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    27,159
    Here is simple code that list files from a known file structure but allows dynamic referencing of the folders. Instead of the debug, do the import code. If files other than Excel will be present, test for the file extension in a conditional If. Adjust for however many levels in your file structure.
    Code:
    Sub ListFiles()
    Dim fso As Object
    Dim SourceFolder As Object
    Dim SFolder As Object
    Dim SSFolder As Object
    Dim SSSFolder As Object
    Dim EFile As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = fso.GetFolder("<drive>:\<path>\<startfolder>\")
    For Each SFolder In SourceFolder.subfolders
        For Each SSFolder In SFolder.subfolders
            For Each EFile In SSFolder.Files
                Debug.Print EFile.Name
            Next
        Next
    Next
    Set SFolder = Nothing
    Set SSFolder = Nothing
    Set SSSFolder = Nothing
    Set SourceFolder = Nothing
    Set fso = Nothing
    End Sub
    Allen's code is more sophisticated and designed for generic application. My code is geared to a specific limited use.

    If you list files to listbox or table, then need code to loop through the table or listbox and do import but unless you also save the full file path into the table or listbox, back where you started.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    artec is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    11
    I modified this code with your suggestions and it works fine as I need. I have only one question. If I update field WORKER with Folder's name I have full path in a record for example Y:\OFFICE\Folder name. I need only "Folder name" but the folder's names can be different. Is it possible do this ?
    Code:
    Dim fso As Object
    Dim SourceFolder As Object
    Dim SFolder As Object
    Dim SSFolder As Object
    Dim SSSFolder As Object
    Dim EFile As Object
    DoCmd.SetWarnings False
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set SourceFolder = fso.GetFolder("Y:\OFFICE\")
    For Each SFolder In SourceFolder.subfolders
        'For Each SSFolder In SFolder.subfolders'
            For Each EFile In SFolder.Files
                If EFile.Name Like "*.xls" Then DoCmd.TransferSpreadsheet acImport, , _
      "TEMPORARY", SFolder & "\" & EFile.Name, True, "!A1:BG339"
      DoCmd.RunSQL "UPDATE TEMPORARY SET [WORKER] = '" & SFolder & "' WHERE [WORKER] is null ;" 'Debug.Print EFile.Name
                'Debug.Print EFile.Name
                
            Next
        Next
    'Next
    Set SFolder = Nothing
    Set SSFolder = Nothing
    Set SSSFolder = Nothing
    Set SourceFolder = Nothing
    Set fso = Nothing

  6. #6
    June7's Avatar
    June7 is online now Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    27,159
    Try:

    Mid(SFolder,InstrRev(SFolder,"\")+1)
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #7
    artec is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    11
    Thanks, It works perfectly.

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

Similar Threads

  1. Import multiple txt tables
    By webisti in forum Access
    Replies: 3
    Last Post: 03-13-2012, 07:44 AM
  2. Import multiple files from one location to new tables
    By shmalex007 in forum Import/Export Data
    Replies: 1
    Last Post: 01-05-2012, 01:49 AM
  3. import txt file with multiple headers
    By vojinb in forum Import/Export Data
    Replies: 9
    Last Post: 07-25-2011, 09:37 AM
  4. Import multiple Text files with a Macro
    By ArchMap in forum Access
    Replies: 3
    Last Post: 07-01-2011, 03:56 PM
  5. Replies: 6
    Last Post: 09-30-2010, 10:12 AM

Tags for this Thread

Posting Permissions

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