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

    Problem with "for each" loop

    Johan Adolfsson


      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
      +Folder 2

      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.


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

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

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


      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


          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
          'Process subfolders
          For Each objFolder InobjParFolder.SubFolders
          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
          Set objFile = Nothing
          For Each objFolder In SubFolder.SubFolders
          ProcessFilesInSubFolder objFolder
          Set objFolder = Nothing
          End Sub
          Sub cmdProcess_Click()
          ProcessReports vRootPath
          End Sub

            • Problem with "for each" loop
              Johan Adolfsson

              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.


                • Problem with "for each" loop
                  Johan Adolfsson


                  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:\')