none
Link Multiple Excel doc to access, loop through SubFolders RRS feed

  • Question

  • I have a need to create/delete and recreate links to Excel documents that are stored in folders and subfolder locations. I do have some basic VBA code that will create these links as long as I am pointing at a single folder location. What I need is for this code or a similar code that can do the job but also loop through sub folders. Here is the code I am using.

    Thanks for your time.

    SubPlanner


    Sub LinkExcel() 'introspective to files. Creates links to excel documents found in the file location. Dim iFile As String 'Filename Dim iFileList() As String 'File Array Dim intFile As Integer 'File Number Dim iPath As String iPath = "Z:\DataFiles\Excel\" 'Directory Path 'Loop through the folder & build file list iFile = Dir(iPath & "*.xlsx", vbDirectory) 'Wildcard Excel While iFile <> "" 'add files to the list intFile = intFile + 1 ReDim Preserve iFileList(1 To intFile) iFileList(intFile) = iFile iFile = Dir() Wend 'see if any files were found If intFile = 0 Then MsgBox "No files found" Exit Sub End If 'cycle through the list of files & link to Access For intFile = 1 To UBound(iFileList) DoCmd.TransferSpreadsheet acLink, , _ iFileList(intFile), iPath & iFileList(intFile), True ', "TabName!A1:F125" '"ExampleTab!A1:C4" 'Set your range here if needed. Next 'MsgBox UBound(iFileList) & " Files were Linked" End Sub

    1. 
    Wednesday, November 30, 2016 3:15 PM

Answers

All replies

  • Hi SubPlanner,

    use the code mentioned below to loop through sub folders.

    Sub demo5()
    
    
    Dim FileSystem As Object
    Dim HostFolder As String
    
    HostFolder = "C:\Users\v-padee\Desktop\"
    
    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder)
    End Sub
    
    Sub DoFolder(Folder)
        Dim SubFolder
        For Each SubFolder In Folder.SubFolders
            DoFolder SubFolder
        Next
        Dim File
        For Each File In Folder.Files
            ' Operate on each file
            MsgBox (File)
        Next
    End Sub

    you need to implement this code with your code to perform your desired task.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 1, 2016 2:17 AM
    Moderator
  • Thanks for your reply Deepak.

    The problem is that I am a bit of a hack when it comes to writing or merging codes together.

    I am learning. And I appreciate your code.

    I am having trouble putting the two code module together.

    I am attempting to create an "TransferSpreadsheet acLink" to each Excel document in the folder and subfolder locations.

    Thanks again for any additional help you can offer.

    SubPlanner

    Thursday, December 1, 2016 11:50 AM
  • Hi Sub,

    Did you manage to resolve this using the same technique you used at UtterAccess?

    Just curious...

    Thursday, December 1, 2016 5:53 PM
  • Yes I did, Looks like we get around.

    Thanks again.

    • Marked as answer by SubPlanner Thursday, December 1, 2016 6:24 PM
    Thursday, December 1, 2016 6:24 PM
  • You're welcome, again. Good luck with your project.
    Thursday, December 1, 2016 6:25 PM