recalculate page breaks after setting Zoom in pagesetup

[Par]

Cross posted since I have gotten no responce in
microsoft.public.excel.programming for 3 days

Ok I've seen the 1,000s of people trying to program page setup
options
and having a bad time. So let me add more problems.

I was using Excel 2000. I now have 2007. I had a workaround for
this
bug in 2000/2003 but I can't seem to get around it in 2007 (and of
course it isn't fixed)


Here is what I'm trying to do:
Set the page to Landscape, 1 page wide
Then add some intelligent page breaks by looking for the automatic
page break and putting a manual break above it where the data
recently
changed.


The problem is that the automatic page break is not recalculated
after
setting the zoom factor programatically.


if I do:
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Zoom = 65
End With


the first page break is on row 33


In 2000/2003 I could :
SendKeys "%(FUA)" + Format(ActiveSheet.PageSetup.Zoom) + "~",
True


This is a crappy way to get around it but after the sendkeys the
first
page break would not be row 53


I cannot get a key stroke method to the page setup dialog box in
Excel2007 to work from sendkeys (I can do it manually but that
doesn't
help)


Anybody know how to get Excel to recalculate the page breaks?
Obviously something is being called from teh page setup dialog box
that I can't find directly.


Thanks for any help.
Pete

 

[Jim Rech]

Does this help?:

With Sheet1.PageSetup
.Zoom = 65
.PaperSize = .PaperSize
End With


--
Jim
| Cross posted since I have gotten no responce in
| microsoft.public.excel.programming for 3 days
|
| Ok I've seen the 1,000s of people trying to program page setup
| options
| and having a bad time. So let me add more problems.
|
| I was using Excel 2000. I now have 2007. I had a workaround for
| this
| bug in 2000/2003 but I can't seem to get around it in 2007 (and of
| course it isn't fixed)
|
|
| Here is what I'm trying to do:
| Set the page to Landscape, 1 page wide
| Then add some intelligent page breaks by looking for the automatic
| page break and putting a manual break above it where the data
| recently
| changed.
|
|
| The problem is that the automatic page break is not recalculated
| after
| setting the zoom factor programatically.
|
|
| if I do:
| With ActiveSheet.PageSetup
| .Orientation = xlLandscape
| .Zoom = 65
| End With
|
|
| the first page break is on row 33
|
|
| In 2000/2003 I could :
| SendKeys "%(FUA)" + Format(ActiveSheet.PageSetup.Zoom) + "~",
| True
|
|
| This is a crappy way to get around it but after the sendkeys the
| first
| page break would not be row 53
|
|
| I cannot get a key stroke method to the page setup dialog box in
| Excel2007 to work from sendkeys (I can do it manually but that
| doesn't
| help)
|
|
| Anybody know how to get Excel to recalculate the page breaks?
| Obviously something is being called from teh page setup dialog box
| that I can't find directly.
|
|
| Thanks for any help.
| Pete
|

 

[par_60056]

Jim,
That is incredible. It works but I have no idea why. It is such an
obvious solution I don't know why I didn't think of it (NOT)

So I looked up the doc on papersize and it sure doesn't say anything
(surprising isn't it) that would lead me to try this.

Thanks a lot. I have been fighting this problem in my spare time for
over a week

Pete

 

[Jim Rech]

Glad it works. I tried it on a lark. Just got lucky.

--
Jim
| Jim,
| That is incredible. It works but I have no idea why. It is such an
| obvious solution I don't know why I didn't think of it (NOT)
|
| So I looked up the doc on papersize and it sure doesn't say anything
| (surprising isn't it) that would lead me to try this.
|
| Thanks a lot. I have been fighting this problem in my spare time for
| over a week
|
| Pete
|
| > Does this help?:
| >
| > With Sheet1.PageSetup
| > .Zoom = 65
| > .PaperSize = .PaperSize
| > End With
| >
| > --
| >
| > | > | Cross posted since I have gotten no responce in
| > | microsoft.public.excel.programming for 3 days
| > |
| > | Ok I've seen the 1,000s of people trying to program page setup
| > | options
| > | and having a bad time. So let me add more problems.
| > |
| > | I was using Excel 2000. I now have 2007. I had a workaround for
| > | this
| > | bug in 2000/2003 but I can't seem to get around it in 2007 (and of
| > | course it isn't fixed)
| > |
| > |
| > | Here is what I'm trying to do:
| > | Set the page to Landscape, 1 page wide
| > | Then add some intelligent page breaks by looking for the automatic
| > | page break and putting a manual break above it where the data
| > | recently
| > | changed.
| > |
| > |
| > | The problem is that the automatic page break is not recalculated
| > | after
| > | setting the zoom factor programatically.
| > |
| > |
| > | if I do:
| > | With ActiveSheet.PageSetup
| > | .Orientation = xlLandscape
| > | .Zoom = 65
| > | End With
| > |
| > |
| > | the first page break is on row 33
| > |
| > |
| > | In 2000/2003 I could :
| > | SendKeys "%(FUA)" + Format(ActiveSheet.PageSetup.Zoom) + "~",
| > | True
| > |
| > |
| > | This is a crappy way to get around it but after the sendkeys the
| > | first
| > | page break would not be row 53
| > |
| > |
| > | I cannot get a key stroke method to the page setup dialog box in
| > | Excel2007 to work from sendkeys (I can do it manually but that
| > | doesn't
| > | help)
| > |
| > |
| > | Anybody know how to get Excel to recalculate the page breaks?
| > | Obviously something is being called from teh page setup dialog box
| > | that I can't find directly.
| > |
| > |
| > | Thanks for any help.
| > | Pete
| > |
|
|