Do something to each file in a folder

[Guest]

Hello,

In the past, I was able to make the code below work. In using it against a
different folder, it works, but stops after 18 files (there are 45 in the
folder, all identical except for their names, which follow the exact same
naming scheme)..... I don't get an error, it just acts like it is done.

Any clues?

TIA!



Dim FSO As Object
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Dim oWb As Workbook
Dim sFolder As String

Set FSO = CreateObject("Scripting.FileSystemObject")

'*** THE FOLDER I WANT TO WORK IN ******************
sFolder = "Y:\Sales\Forecast Workbooks"
' *************************************************

If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)
Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Set oWb = Workbooks.Open(Filename:=file.Path)


**********************************************************
Do something
**********************************************************

ActiveWorkbook.Save
ActiveWorkbook.Close

End If
Next file

End If
End Sub

 

[Bob Phillips]

Jeff,

I just modified it slightly and it processed 22 files for me.

Are you sure you have more than 18 Excel workbooks in that directory. It
ignores other file types.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Hi Bob,

Yes, they are all identical except the salesperson's name

 

[Bob Phillips]

Then, without being able to see the environment, I am afraid I am out of
ideas. As I said, it works okay for me.

Just a thought, could it be that it is failing on the 18th item? Rename it
and see if you get past it.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Very odd.... I removed the 18 successful files and re-ran it. Again, it
worked for the first 18 and stopped. I can make it work by repeating these
steps three times, but I remain confused....

Thanks.

 

[Dave Peterson]

If you change the "Do something" to something very easy:

msgbox file.path

does it work ok?

Maybe it's something in your code that's causing the trouble.

Or maybe it's something in the 19th workbook that's causing the trouble--maybe
disabling the events before you open the workbook would help.

 

[Gary Keramidas]

i got an error after 172 files on my system, just opening and saving them

 

[Gary Keramidas]

i had a corrupted file, fixed it and it opened and closed all 285 files in
the folder.

so it must be something in the "do something" loop