Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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:\')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
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
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:\')