Excel Office 2003 - For Each looping issue
Hi there,
New to the forums - First time poster...
I've got an Excel VBA macro that loops thru a bunch of text files in a folder and updates them. I'm finding that it is unexpectedly exiting the loop after processing the 132nd item.
Have attempted processing the item in question using a smaller set of data (ie when the folder only contained 5 items, the item in question was in the middle of the 5 files) and the macro was able to process and continue to the next item successfully. So believe that it's not related to the content of the file itself.
Is there some kind of limit to the number of items a variable can hold in VBA? When I step thru the code, the loop dies as soon as I try to step thru the "Next f3" statement.
In this example my folder contains 527 files, but the "For Each f3 In fe" loop can only process up to the 132nd before it dies.
Here's my code, if anyone can spot what might be causing the issue then please let me know:
Dim fs, f, f1, f2, f3, fc, fd, fe As Object
[...snip...]
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder(QARunEnvPath_str)
Set fc = f.subfolders
[...snip...]
'
' Get a handle on Business Function subfolders and start loop
'
For Each f1 In fc
'
' Create a copy of the Test Data Folder using the DIR suffix string
'
NewFldr_str = f1.Path & cDIR_SUFFIX
fs.Copyfolder f1.Path, NewFldr_str
'
' Get handle on files within new folder, then start looping thru those
'
Set f4 = fs.getfolder(NewFldr_str)
Set fe = f4.Files
For Each f3 In fe
[Do stuff....]
Next f3 ' Next File
Alternatively I was thinking about writing a simple for loop to process the files that way but have so far been unable to grab a handle on each file using a numeric index + counter. Here's what I was trying - if anyone can help me out, would be much appreciated.. cheers
'
' Get handle on files within new folder, then start looping thru those
'
Set f4 = fs.getfolder(NewFldr_str)
Set fe = f4.Files
TotalFiles_int = f4.Files.Count
For x = 1 To TotalFiles_int
Set f3 = fe.Item(x) <== doesn't work, alternatively tried
Set f3 = f4.Files.Item(x).Path
[do stuff...]
Next x
Re: Excel Office 2003 - For Each looping issue
Quote:
Originally Posted by 0438snappy
Hi there,
New to the forums - First time poster...
I've got an Excel VBA macro that loops thru a bunch of text files in a folder and updates them. I'm finding that it is unexpectedly exiting the loop after processing the 132nd item.
any error messages?
Quote:
Code:
Dim fs, f, f1, f2, f3, fc, fd, fe As Object
' [...snip...]
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfolder(QARunEnvPath_str)
Set fc = f.subfolders
' [...snip...]
'
' Get a handle on Business Function subfolders and start loop
'
For Each f1 In fc
'
' Create a copy of the Test Data Folder using the DIR suffix string
'
NewFldr_str = f1.Path & cDIR_SUFFIX
fs.Copyfolder f1.Path, NewFldr_str
'
' Get handle on files within new folder, then start looping thru those
'
Set f4 = fs.getfolder(NewFldr_str)
Set fe = f4.Files
For Each f3 In fe
' [Do stuff....]
Next f3 ' Next File
No error handling/checking?
Perhaps a file is exclusive/readonly or something?
Quote:
Alternatively I was thinking about writing a simple for loop to process the files that way but have so far been unable to grab a handle on each file using a numeric index + counter. Here's what I was trying - if anyone can help me out, would be much appreciated.. cheers
Code:
'
' Get handle on files within new folder, then start looping thru those
'
Set f4 = fs.getfolder(NewFldr_str)
Set fe = f4.Files
TotalFiles_int = f4.Files.Count
For x = 1 To TotalFiles_int
' Set f3 = fe.Item(x) <== doesn't work, alternatively tried
'---- this would set f3 to the filepath... not the file object
Set f3 = f4.Files.Item(x).Path
' [do stuff...]
Next x
