Cannot get number of page breaks using VBA/Excel/ASP 
Author Message
 Cannot get number of page breaks using VBA/Excel/ASP

I've got a spreadsheet resident in memory on the server-side (ASP) and
am trying to find page breaks.  It has 140 rows and two page breaks.  
 (I'm using IIS 5.0 and Excel 2000 is installed on the server)

I first try to get a count of the number of page breaks in the
spreadsheet using:

objExcel.Workbooks(1).Sheets(1).HPageBreaks.Count

 - but this returns a total count of zero.

I then tried using the following function (from 12/31/2001 posting in
this newsgroup) to get the number of rows

For Each row In objExcel.Workbooks(1).Sheets(1).UsedRange.Rows
 If row.PageBreak <> xlNone Then
  count = count + 1
  ReDim Preserve rNums(count)
  rNums(count) = row.Column
  Response.write("Found break at row " & row.Column & "<br>")
 End If
Next

But this returns

Microsoft Excel error '800a03ec'
Unable to get the PageBreak property of the Range class

Any thoughts on what might be wrong?   Here is the whole page.  
Thanks in advance.

Brent T. Meyer
Systems Analyst/Programmer
UNLV Systems and Applications

------------------------------------------------------------

<%

Dim objExcel, objWB

Main

Function Main()

        'Create Application Object
        CreateExcel

        'Turn off alerts to avoid server crashing
        objExcel.Application.DisplayAlerts = False

        'Loop to create two pages
        For i = 1 to 140

                objExcel.Workbooks(1).Sheets(1).Cells(i,1).Value = "This is row " &
i & " and cell 1"

        Next

        'Check page breaks and identify
        Response.write("Count of used range is " &
objExcel.Workbooks(1).Sheets(1).UsedRange.Rows.Count & "<br>")
        Response.write("Number of page breaks is " &
objExcel.Workbooks(1).Sheets(1).HPageBreaks.Count & "<br>")
        FindBreakPageRows objExcel.Workbooks(1).Sheets(1)

        'Save it
        On Error Resume Next
        objExcel.Workbooks(sWBName).SaveAs ("e:\hos\Test.xls")
        ErrorHandler

        'Close Excel and set to Nothing
        objExcel.Quit
        Set objExcel = Nothing

End Function

Function FindBreakPageRows(ThisSheet)
'The code below returns an array of row numbers corresponding to rows
'immediately below a manual or automatic page break.
Dim rNums

count = 0
For Each row In ThisSheet.UsedRange.Rows
 If row.PageBreak <> xlNone Then
  count = count + 1
  ReDim Preserve rNums(count)
  rNums(count) = row.Column
  Response.write("Found break at row " & row.Column & "<br>")
 End If
Next

End Function

Function CreateExcel()

        'Create application object
        Set objExcel = CreateObject("Excel.Application")
        Response.write("Created new excel application object<br>")

        'Create workbook object
        Set objWB = objExcel.Workbooks.Add
        Response.write("Created workbook object<br>")

        'Activate it
        objExcel.Workbooks(sWBName).Activate

End Function

Function ErrorHandler()

        Response.write("Error num was " & Err.Number & " and desc was " &
Err.Description)
        Err.Clear

        Set objWB = Nothing
        objExcel.Quit
        Set objExcel = Nothing

End Function

%>



Sat, 17 Jul 2004 09:04:37 GMT  
 Cannot get number of page breaks using VBA/Excel/ASP
I'm not sure why your code's not working, but you might have more success
with something like this...

    Dim PageCount As Long
    PageCount = Application.ExecuteExcel4Macro _
        ("GET.DOCUMENT(50,""" & ActiveSheet.Name & """)")
    MsgBox PageCount


Quote:
> I've got a spreadsheet resident in memory on the server-side (ASP) and
> am trying to find page breaks.  It has 140 rows and two page breaks.
>  (I'm using IIS 5.0 and Excel 2000 is installed on the server)

> I first try to get a count of the number of page breaks in the
> spreadsheet using:

> objExcel.Workbooks(1).Sheets(1).HPageBreaks.Count

>  - but this returns a total count of zero.

> I then tried using the following function (from 12/31/2001 posting in
> this newsgroup) to get the number of rows

> For Each row In objExcel.Workbooks(1).Sheets(1).UsedRange.Rows
>  If row.PageBreak <> xlNone Then
>   count = count + 1
>   ReDim Preserve rNums(count)
>   rNums(count) = row.Column
>   Response.write("Found break at row " & row.Column & "<br>")
>  End If
> Next

> But this returns

> Microsoft Excel error '800a03ec'
> Unable to get the PageBreak property of the Range class

> Any thoughts on what might be wrong?   Here is the whole page.
> Thanks in advance.

> Brent T. Meyer
> Systems Analyst/Programmer
> UNLV Systems and Applications

> ------------------------------------------------------------

> <%

> Dim objExcel, objWB

> Main

> Function Main()

> 'Create Application Object
> CreateExcel

> 'Turn off alerts to avoid server crashing
> objExcel.Application.DisplayAlerts = False

> 'Loop to create two pages
> For i = 1 to 140

> objExcel.Workbooks(1).Sheets(1).Cells(i,1).Value = "This is row " &
> i & " and cell 1"

> Next

> 'Check page breaks and identify
> Response.write("Count of used range is " &
> objExcel.Workbooks(1).Sheets(1).UsedRange.Rows.Count & "<br>")
> Response.write("Number of page breaks is " &
> objExcel.Workbooks(1).Sheets(1).HPageBreaks.Count & "<br>")
> FindBreakPageRows objExcel.Workbooks(1).Sheets(1)

> 'Save it
> On Error Resume Next
> objExcel.Workbooks(sWBName).SaveAs ("e:\hos\Test.xls")
> ErrorHandler

> 'Close Excel and set to Nothing
> objExcel.Quit
> Set objExcel = Nothing

> End Function

> Function FindBreakPageRows(ThisSheet)
> 'The code below returns an array of row numbers corresponding to rows
> 'immediately below a manual or automatic page break.
> Dim rNums

> count = 0
> For Each row In ThisSheet.UsedRange.Rows
>  If row.PageBreak <> xlNone Then
>   count = count + 1
>   ReDim Preserve rNums(count)
>   rNums(count) = row.Column
>   Response.write("Found break at row " & row.Column & "<br>")
>  End If
> Next

> End Function

> Function CreateExcel()

> 'Create application object
> Set objExcel = CreateObject("Excel.Application")
> Response.write("Created new excel application object<br>")

> 'Create workbook object
> Set objWB = objExcel.Workbooks.Add
> Response.write("Created workbook object<br>")

> 'Activate it
> objExcel.Workbooks(sWBName).Activate

> End Function

> Function ErrorHandler()

> Response.write("Error num was " & Err.Number & " and desc was " &
> Err.Description)
> Err.Clear

> Set objWB = Nothing
> objExcel.Quit
> Set objExcel = Nothing

> End Function

> %>



Sat, 17 Jul 2004 14:50:04 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Cannot get number of page breaks using VBA/Excel/ASP

2. Get the line number of a page break using VBA

3. Place page break in excel spreadsheet using VBA

4. page break/section break/page numbering problem

5. Cannot control page breaks by using styles

6. fileOpen, fileSaveAs using vba in an asp page

7. using section breaks and page numbering

8. Using VBA to remove page breaks

9. Getting rid of the comma in a number using VBA

10. page break in Excel via VBA Automation


 
Powered by phpBB® Forum Software © phpBB Group