MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Why does this loop end before all workbooks are closed

  Asked By: Matilda    Date: Dec 04    Category: MS Office    Views: 1901

Each day I receive 5 excel workbooks containing pivot tables.
I need to extract the underlying data for importing into Access, so
I created the following 2 procs.

Sub XtractFails()
Dim Wb As Workbook
For Each Wb In Workbooks
Call SvFlz
Next Wb
Application.StatusBar = ""
End Sub

Sub SvFlz()
Application.DisplayAlerts = False
Dim strFName, strSuffix As String
strFName = Left(ActiveWorkbook.Name, InStr
(ActiveWorkbook.Name, " ") - 1)
strSuffix = ".xls"
Sheets("Nostro By Counterparty").Select
If strFName = "NYAF" Then
ActiveSheet.PivotTables("PivotTable6").PivotSelect "'Grand
Total'", xlDataOnly
Selection.ShowDetail = True
ActiveSheet.PivotTables("PivotTable4").PivotSelect "'Grand
Total'", xlDataOnly
Selection.ShowDetail = True
End If
ChDir "MyPath"
ActiveWorkbook.SaveAs Filename:="MyPath" & strFName & strSuffix,
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
Application.StatusBar = strFName & " Completed!"
End Sub

The problem is that the ForEach loop stops after the first 2
workbooks. I call it a second time and it does the remaining 3.
The order of the workbooks does not seem to make a difference, but
no matter what I've tried I cannot get this to process all 5.

I've been looking at it for way too long, perhaps another set of
eyes can spot what is breaking the loop.



2 Answers Found

Answer #1    Answered By: Haboos Kauser     Answered On: Dec 04

Didn't try the code myself, or I might have a more definitive answer,
but I have a couple thoughts.

First, I assume this code is in a module that is not being closed  by
your ActiveWindows.Close call?

And second, I wonder if the ActiveWindow.Close call is throwing off the
For Each loop. One way to check would be to comment out that line, run
it again, and see if it works.

But that's where I would look -- at the ActiveWindow.Close call.

Answer #2    Answered By: Bama Cohen     Answered On: Dec 04

I used suggestions to clean this code and make it more efficient.

The ultimate solution was found by rexamining the incoming file
names at which time I found that 1 file was coming in with a modest
difference in the naming convention (1 file actually had 2 spaces
instead of 1) and it caused my loop  to fail.

I addressed this with an If . . .Then statement and all seems to
work as expected now.

Didn't find what you were looking for? Find more on Why does this loop end before all workbooks are closed Or get search suggestion and latest updates.