Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing CountA - Excel automation object not closing
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 Befo=.Range("a" & r) r = r + 57 Loop 'outer loop End With '.sheets(strsheetstorename) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing CountA - Excel automation object not closing
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 _ Befo=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 Befo=.Range("a" & r) r = r + 57 Loop 'outer loop End With '.sheets(strsheetstorename) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing CountA - Excel automation object not closing
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 Befo=.Range("a" & r) "Jim Cone" wrote: 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 _ Befo=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 Befo=.Range("a" & r) r = r + 57 Loop 'outer loop End With '.sheets(strsheetstorename) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing CountA - Excel automation object not closing
Keith,
"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 Befo=.Range("a" & r) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing CountA - Excel automation object not closing
Keith,
Also, what does .Range refer to here?... "Befo=.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 Befo=.Range("a" & r) '<<<<<<<<<<<<<<<<<< |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing CountA - Excel automation object not closing
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 Befo=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 "Jim Cone" wrote: Keith, Also, what does .Range refer to here?... "Befo=.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 Befo=.Range("a" & r) '<<<<<<<<<<<<<<<<<< |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA COUNTA function and C++ automation | Excel Programming | |||
help !!! please... closing excel application using automation | Excel Programming | |||
Closing Excel Object | Excel Programming | |||
help !!!!.. Excel object not closing | Excel Programming | |||
Using Userform object in Excel via automation from VB 6 | Excel Programming |