3 Replies Latest reply: Mar 16, 2011 3:02 PM by Johan Adolfsson RSS

    Problem with "for each" loop

      Hi,

      I have run into a problem with my "for each" loop. I found most of my code here.
      I am looping through directories and loading any excel files that i find.
      The problem is that I start my load one level too deep.

      Folder structure:

      App (qvw)
      +Folder 1
      +A
      +1A
      +1B
      +B
      +C
      +Folder 2
      +D
      +E
      +F

      Now I start loading files in folder A .... then B...therefore I miss any excel files that might be directly under "Folder 1", not in subfolder.
      I don't want to load the files in Folder 2.

      CODE:

      for each vDirectory in dirlist (vRoot&'\*')

      LET vFolderName = subfield('$(vDirectory)', '', -1);

      for each vFile in filelist (vDirectory&'\*.xls')

      List:

      LOAD *,

      '$(vFolderName)' as Folder,

      FileDir('$(vDirectory)') as Path,

      FileName('$(vFile)') as File

      FROM [$(vFile)] (biff, embedded labels, header is 1 lines, table is Blad1$);

      next vFile

      next vDirectory



       

      Thanks for any help


       

        • Problem with "for each" loop
          Upendra Mulumudi

          Hi

          This is an extract of my VB 6 code. This should work for you:

           



          Sub ProcessReports(TargetPath As String)
          Dim objFSO' AsScripting.FileSystemObject
          Dim objParFolder' As Folder
          Dim objFolder' As Folder
          Dim objFile' As File
          Set objFSO =CreateObject("Scripting.FileSystemObject")
          Set objParFolder =objFSO.GetFolder(TargetPath)
          For Each objFileIn objParFolder.Files
          If UCase(Right(objFile.Name, 3)) = "XLS" Then
          'File name is objFile.Name
          'Load statement here
          End If
          Next
          'Process subfolders
          For Each objFolder InobjParFolder.SubFolders
          ProcessFilesInSubFolderobjFolder
          Next
          Set objFile = Nothing
          Set objFolder = Nothing
          Set objParFolder = Nothing
          Set objFSO = Nothing
          End Sub
          Sub ProcessFilesInSubFolder(SubFolder As Folder)
          Dim objFile' As File
          Dim objFolder' As Folder
          For Each objFile In SubFolder.Files
          If UCase(Right(objFile.Name, 3))= "XLS" Then
          'File name is objFile.Name
          'Load statement here
          End If
          Next
          Set objFile = Nothing
          For Each objFolder In SubFolder.SubFolders
          ProcessFilesInSubFolder objFolder
          Next
          Set objFolder = Nothing
          End Sub
          Sub cmdProcess_Click()
          ProcessReports vRootPath
          End Sub





            • Problem with "for each" loop

              Hi Mulumudi,

              Thanks for your reply.
              But I feel like my code is better, at least more compact with just ~10 lines of code.
              And it works to 99%...

              The problem is that my code doesn't scan for excel files at first loop. vRoot holds full path to app directory.
              I also tried :
              for each vDirectory in dirlist (if(vFlag, vRoot&'\*', vRoot))

              Using vRoot i get want at top level, using vRoot&'\*' i get all directories...i need to combine these two options.

              Thanks

                • Problem with "for each" loop

                  Hi,

                  I found a solution in the helpfile.

                   

                   

                  sub DoDir (Root)

                   

                  for each Ext in 'xls' //'qvw', 'qvo', 'qvs', 'qvt', 'qvd'

                   

                  for each File in filelist (Root&'\*.' &Ext)

                   

                  Load '$(File)' as Namn, FileSize( '$(File)' ) as Size, FileTime( '$(Fil)' ) as Filtid

                   

                  autogenerate 1;

                   

                  next File

                   

                  next Ext

                   

                  for each Dir in dirlist (Root&'\*' )

                   

                  call DoDir (Dir)

                   

                  next Dir

                   

                  end sub

                   

                  call DoDir ('C:\')