Results 1 to 8 of 8
  1. #1
    amandag
    Guest

    PageBreaks (v97)

    I would like to know how many rows there are in a new workbook when I set the top, bottom left right margins. this is because I want to insert data from an external datasource and want the data to have 3 columns on a portrait page. I envisaged doing this by finding out where the automatic page break is located. However I can't seem to get the right code.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PageBreaks (v97)

    Worksheets have HPageBreaks and VPageBreaks collections that contain horizontal and vertical pagebreak objects. Is this what you are looking for?
    Legare Coleman

  3. #3
    amandag
    Guest

    Re: PageBreaks (v97)

    I don't want to manually set a page break. I want to find out where the natural page break would be with the margin settings I provide

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PageBreaks (v97)

    The Count property on the HPageBreaks or VPageBreaks collections should tell you how many page breaks there are in each direction. The Location property of the pagebreak objects in the collections should tell you where they are (using the Address method to turn the range into text).
    Legare Coleman

  5. #5
    amandag
    Guest

    Re: PageBreaks (v97)

    Thanks Legare but I still can't get it.
    This is the code I have so far, but the last line doesn't work
    Sub Main()
    Dim intRows As Integer 'loops round 41 rows
    Dim intCols As Integer 'loops round 3 columns

    Set dbs = OpenDatabase("C:Phone List.mdb")
    Set rs = dbs.OpenRecordset("Export To Excel")
    Set shtList = Sheets(1)
    With shtList.PageSetup
    .TopMargin = 5
    .BottomMargin = 5
    .LeftMargin = 5
    .RightMargin = 5
    .Orientation = xlLandscape
    End With
    shtList.Cells.Font.Size = 7
    intRows = shtList.HPageBreaks(1).Location.Address.Row

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: PageBreaks (v97)

    ** edited- GeoffW. "Pre" tag corrected **

    I can't get it to work either using Excel 97. The correct statement should be:

    <pre>intRows = shtList.HPageBreaks(1).Location.Row
    </pre>


    However, that seems to get a subscript out of range. I can't seem to get the PageBreaks collections to return any pagebreak objects, and I also can't get the example from the help files to add a new page break to work. Although I can't find anything in the MS KB, this appears to be a bug in Excel 97.

    Can someone else out there give this a try in another version of Excel.

    The following code works on my system to find the column number of the first column after the first horizontal page break.

    <pre>Dim iCol As Integer
    For iCol = 1 To ActiveSheet.UsedRange.Columns.Count
    If ActiveSheet.Columns(iCol).PageBreak = xlAutomatic Or _
    ActiveSheet.Columns(iCol).PageBreak = xlManual Then
    Exit For
    End If
    Next iCol
    </pre>


    Maybe you can use that as a workaround.
    Legare Coleman

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: PageBreaks (v97)

    Hi,
    As far as I can tell, Excel doesn't add any automatic pagebreaks until you have enough data to actually require one (even though it will happily show you where they will appear)
    I would suggest you either need to set up your worksheet beforehand, since you know what margins you want, or copy the recordset into the sheet and then determine where the pagebreaks naturally occur.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    amandag
    Guest

    Re: PageBreaks (v97)

    Thanks for replying

    I think I'll just have to assume that there will always be the same number of rows with my current settings.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •