0

Basically, I have a list of folder path in the excel worksheet as shown in figure below: List of Folder Path

I want to loop through all the subfolders(up to 3, 4 layers) to find the folder named "Report" , every folder path which listed in the figure contain the "Report" folder.

Sub SearchReport()

    Dim FileSystem As Object

    counter = 2
    Do While Range("A" & counter).Value <> ""   'do when Range A is not empty (folder path)

        If Range("C" & counter).Value = "Yes" Then  'check Range C, do when Range C = Yes
            HostFolder = Range("A" & counter).Value & "\"
            Set FileSystem = CreateObject("Scripting.FileSystemObject")
            Report FileSystem.getFolder(HostFolder) 'HostFolder is the folder path from Range A
            counter = counter + 1
        Else
            counter = counter + 1
        End If
    Loop
End Sub

Sub Report(Folder)
    Dim SubFolder
    Dim subfld As Folder
    Dim subfldr As Folder
    Dim File As File
    Dim MyPath As String
    Dim Wksht As Worksheet
    Dim wbk As Workbook

           For Each SubFolder In Folder.SubFolders  'loop through subfolders in the  first folder path
            Do While SubFolder.Name = "Report"  'look for a folder named "Report"

                MyPath = SubFolder.Path & "\"
                Filename = Dir(MyPath & "*al.dat")  'look for files which is ended with "al.dat" in the "Report" folder

                  Do While Len(Filename) > 0    'open the files
                    Set Wksht = Worksheets.Add
                    Wksht.Name = Left(Filename, Len(Filename) - 10)

                    Set wbk = Workbooks.Open(MyPath & Filename)
                    Set Wrksht = wbk.Worksheets(1)

                    Wrksht.Cells.Copy Wksht.Cells
                    wbk.Close True
                    Filename = Dir
                  Loop
            Loop
              Report SubFolder
           Next
End Sub

I am able to loop through the subfolder to search for the "Report" folder after I modified the code obtained from the net, and also import all the files in the folder. The problem is, it repeat and repeat to loop in the same folder and unable to proceed to the next folder path as in figure above. I have tried to debug for few days but still fail, so I decide to seek for help from the community.

I know there are many questions about looping through the folders and subfolders have been answered but it does not help since my situation is I need the coding to loop through all the listed folder path.
Any help or suggestion is appreciated, Thanks!

Community
  • 1
  • 1
Irene94
  • 29
  • 7
  • Thanks for your time to reply, but what I have understand from the link provided is that trenccan just need to loop through all the folders and subfolders to search for a file from a Fixed Path, but for my situation, I have many paths as shown in the figure, and there is a folder named "Report" inside every folder listed. My problem is, I am able to perform what I want but it only works for the first folder path, the code does not continue to work on the other paths....the requirement is similar but I think is not duplicated since the situation and the problem is different. – Irene94 Jul 31 '17 at 08:56
  • Well your question is about looping through folder and subfolders. As that part of your code works, then no need to include it in your question. Your question seems to be about looping through your workbook. What you should read is about how to define workbooks and make explicit references. This will make sure your code will run, as at first glance there appears to be nothing wrong with it. However when you use Range() in your code, without mentioning from what workbook it will select the latest open workbook, so it will probably look in the report workbook when running the first code for the – Luuklag Jul 31 '17 at 09:06
  • 2nd time your first loop runs. Your second sub is done correctly, using DIM wkb as workbook etc. Do the same for your first sub. – Luuklag Jul 31 '17 at 09:06

0 Answers0