Referencing CountA - Excel automation object not closing

[archerokie]

Hi all. This is my first post here but have gotten valuable info already by
browsing posts.

I need to determine if a row is blank. I found the CountA function and it
works well, returning 0 if the row is blank. However, I have had trouble in
Excel automation in the past by not having valid or properly qualified
references to Excel objects.

The following code (part of a bigger sub) works fine and does what I need
except for one thing, when the procedure is complete, the Excel object won't
close. Can anyone tell me where my improper reference may be?

(if I comment out the rows with CountA, the Excel object closes)

Thanks.
Keith

With .Sheets(strSheetStoreName)
xlApp.ActiveSheet.ResetAllPageBreaks

r = 57
Do Until r > intLastRow 'outer loop
Do Until xlApp.CountA(Rows(r)) = 0 'inner loop
If xlApp.CountA(Rows(r)) <> 0 Then r = r - 1
Loop 'inner loop
If xlApp.WorksheetFunction.CountA(Rows(r)) = 0 Then .HPageBreaks.Add
Before:=.Range("a" & r)
r = r + 57
Loop 'outer loop
End With '.sheets(strsheetstorename)

 

[Jim Cone]

Keith,
You don't show what the object reference is for "With .Sheets(strSheetStoreName)".
I assume it refers to xlApp, but then why do you repeat xlApp within the loop?
Some suggestions...
Set an object reference to the sheet and use the reference.
Avoid the use of the With construct.
Do not use ActiveSheet or any ActiveWhatever or Selection.
The Rows property must be qualified with the sheet object.
Your row index "intLastRow" should be a Long.
So something like this...

Dim wbStores As Excel.Workbook
Dim wsSheet As Excel.Worksheet
Set wbStores = xlApp.Workbooks("SomeName")
Set wsSheet = wbStores.Sheets(strSheetStoreName)

wsSheet.ResetAllPageBreaks
r = 57
Do Until r > intLastRow 'outer loop
Do Until xlApp.CountA(wsSheet.Rows(r)) = 0 'inner loop
If xlApp.CountA(wsSheet.Rows(r)) <> 0 Then r = r - 1
Loop 'inner loop
If xlApp.CountA(wsSheet.Rows(r)) = 0 Then wsSheet.HPageBreaks.Add _
Before:=wsSheet.Range("a" & r)
r = r + 57
Loop 'outer loop
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"archerokie"
wrote in message
Hi all. This is my first post here but have gotten valuable info already by
browsing posts.

I need to determine if a row is blank. I found the CountA function and it
works well, returning 0 if the row is blank. However, I have had trouble in
Excel automation in the past by not having valid or properly qualified
references to Excel objects.
The following code (part of a bigger sub) works fine and does what I need
except for one thing, when the procedure is complete, the Excel object won't
close. Can anyone tell me where my improper reference may be?
(if I comment out the rows with CountA, the Excel object closes)
Thanks.
Keith
'---
With .Sheets(strSheetStoreName)
xlApp.ActiveSheet.ResetAllPageBreaks
r = 57
Do Until r > intLastRow 'outer loop
Do Until xlApp.CountA(Rows(r)) = 0 'inner loop
If xlApp.CountA(Rows(r)) <> 0 Then r = r - 1
Loop 'inner loop
If xlApp.WorksheetFunction.CountA(Rows(r)) = 0 Then .HPageBreaks.Add
Before:=.Range("a" & r)
r = r + 57
Loop 'outer loop
End With '.sheets(strsheetstorename)

 

[archerokie]

Hi Jim, thanks for taking the time to help.

Why is it not advisable to use With construct? ActiveSheet? Selection?

As you can see I have tried to fully qualify the CountA method in these
lines. If I comment them out, the Excel object closes as expected. If I leave
these lines in the code, the Excel automation object remains open even after
the xlAPP.Quit and setting the variable = Nothing.

Do Until excel.Application.WorksheetFunction.CountA(xlSheet.Rows(r)) = 0
'inner loop
If excel.Application.WorksheetFunction.CountA(xlSheet.Rows(r)) <> 0
Then r = r - 1
Loop 'inner loop
Debug.Print r & " = " &
excel.Application.WorksheetFunction.CountA(xlSheet.Rows(r))

If excel.Application.WorksheetFunction.CountA(xlSheet.Rows(r)) = 0
Then xlSheet.HPageBreaks.Add Before:=.Range("a" & r)

 

[Jim Cone]

Keith,
re: "Why is it not advisable to use With construct? ActiveSheet? Selection?"

Because they can (but not always) leave orphan references that prevent Excel
from closing.
Also, you must set every object reference to Nothing before quitting the application or
you can/will leave orphan references again. It is probably best to do them in order of
child then parent... range, worksheet, workbook.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"archerokie"
wrote in message
Hi Jim, thanks for taking the time to help.

Why is it not advisable to use With construct? ActiveSheet? Selection?

As you can see I have tried to fully qualify the CountA method in these
lines. If I comment them out, the Excel object closes as expected. If I leave
these lines in the code, the Excel automation object remains open even after
the xlAPP.Quit and setting the variable = Nothing.
Do Until excel.Application.WorksheetFunction.CountA(xlSheet.Rows(r)) = 0
'inner loop
If excel.Application.WorksheetFunction.CountA(xlSheet.Rows(r)) <> 0
Then r = r - 1
Loop 'inner loop
Debug.Print r & " = " &
excel.Application.WorksheetFunction.CountA(xlSheet.Rows(r))
If excel.Application.WorksheetFunction.CountA(xlSheet.Rows(r)) = 0
Then xlSheet.HPageBreaks.Add Before:=.Range("a" & r)

 

[Jim Cone]

Keith,
Also, what does .Range refer to here?...
"Before:=.Range("a" & r)"
Jim Cone


"archerokie"
wrote in message
Hi Jim, thanks for taking the time to help.

Why is it not advisable to use With construct? ActiveSheet? Selection?

As you can see I have tried to fully qualify the CountA method in these
lines. If I comment them out, the Excel object closes as expected. If I leave
these lines in the code, the Excel automation object remains open even after
the xlAPP.Quit and setting the variable = Nothing.
Do Until excel.Application.WorksheetFunction.CountA(xlSheet.Rows(r)) = 0
'inner loop
If excel.Application.WorksheetFunction.CountA(xlSheet.Rows(r)) <> 0
Then r = r - 1
Loop 'inner loop
Debug.Print r & " = " &
excel.Application.WorksheetFunction.CountA(xlSheet.Rows(r))
If excel.Application.WorksheetFunction.CountA(xlSheet.Rows(r)) = 0
Then xlSheet.HPageBreaks.Add Before:=.Range("a" & r) '<<<<<<<<<<<<<<<<<<

 

[archerokie]

Jim,

..Range was referring to the With .sheets(strSheetStoreName). But I took that
out and surprisingly enough it still worked.

I have tried all the iterations I could think of and this is finally working
now:

With xlApp

Do Until r > intLastRow 'outer loop
Do Until .CountA(xlSheet.Rows(r)) = 0 'inner loop
If .CountA(xlSheet.Rows(r)) <> 0 Then r = r - 1
Loop 'inner loop
If .CountA(xlSheet.Rows(r)) = 0 Then xlSheet.HPageBreaks.Add
Before:=xlSheet.Range("a" & r)
r = r + 57
Loop 'outer loop

End With 'xlApp

This part of the procedure tests a spot 57 rows from the start of the first
page, or 57 rows from the previous manually-set hPageBreak. If that 57th row
isn't empty, then it moves up to the 56th row, then to the 55th row, and so
on until it finds an empty row, then sets a new hPageBreak above that row.

Thank you for your help. When a person gets stuck, there's nothing like a
little guidance.

-Keith