Obtain scale of result of fittopagestall, wide

[mike777]

Hello.

I have some code:

set xlwks=excel.worksheet
with xlwks.pagesetup
     zoom=false
     fittotpagestall=1
     fittopageswide=3
end with

I run this code from MS Access.

When I do this, the print is quite small (but it has to be this way). However, I find that if I go Excel and got to File > Print Preview > Setup, I can click on Adjust to ##% normal size. Here I increase the % scale by 2-8%. The document still prints 1 page tall and three wide (my challenge is getting it 1 page tall; the "wideness" isn't that critical), but the print is much easier to read. So, basically, Excel is overcompensating a bit when it does the "fittopagestall, fittopageswide" routine.

What I'd like to do in my MS Access code, is set the fittopagestall and wide, then obtain the number that is in the "Adjust to ##% normal size" box. Then I would simply add three or four percent to this number to make the document print in the correct page format, but also with the biggest font possible on the printed page.

I can say xlwks.pagesetup.zoom=[someliteralvaluelike,say,52]. This works. However, I don't know what that number should always be. That's why I need to retrieve the number that Excel keeps in that little "Adjust to ##% normal size" field.

The challenge is that, of course, if you use the "fittopagestall, fittopageswide" routine, you also have to set zoom=false. So if I try to catch the "Adjust to ##% normal size" number by saying:

 xlwks.pagesetup.zoom=xlwks.pagesetup.zoom+3

I get an error, since at this point, xlwks.pagesetup.zoom=false.

Is this making sense??!! *&^*&

Any help you can provide will be very much appreciated.

Thank you!!

-Mike

 

[VBAjedi]

Not a direct answer to your question, but. . .

What if you just went into Page Setup and adjusted the page margins to allow more printable space? Excel looks at the margins when calculating the "Fit To" percentage, and so will make the doc a bit larger. . .

Just a thought!


VBAjedi

 

[mike777]

VBAJedi,
Thanks for the help.
I already have adjusted the margins as much as I can.
What I really need is that number in the little box "Adjust to ##% normal size".
Many thanks.
-Mike

 

[VBAjedi]

Ok, untested, but if you can set the zoom to a literal, you should also be able to do something like:

xlwks.pagesetup.zoom = (xlwks.pagesetup.zoom * 1.05)

which would increase it 5%.


VBAjedi

 

[mike777]

VBAJedi -
Yep, my thoughts exactly.
Problem is, though, in order to use the "Fit to Pages" feature, you also have to set:

xlWks.pagesetup.zoom=false

Then, if you do:

 msgbox xlwks.pagesetup.zoom

,
the answer is "false". Obviously, you can't add or multiply "false" by anything.
If you try to set xlwks.pagesetup=true at this point, you get an error. However, if you do the zoom=false, fittopages=etc.etc., and then go into Print Preview to view the document, then go into Setup, you will see that the "Adjust to ##% normal size" actually has a number in it. That's the number I need, so I can set xlwks.pagesetup.zoom=[this number] * 1.05, etc.
It's a catch-22 situation. Do you see my dilemma?
Thanks again.
-Mike

 

[GummowN]

Did you get an answer to your question? If so please share - I have the same but want to programatically change the page setup to landscape if the zoom is under a certain %.



If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[GummowN]

I had enough of Excel so bodged a solution. Here is a code extract - so no dimming going on but....

lngCol = 0
For J = 0 To UBound(avarTransposed, 2)
lngCol = lngCol + rng.columns(J + 1).ColumnWidth
Next J

lngRow = rng.Rows.Count * rng.Rows(1).RowHeight

' Normally there are 4 points per CM - if we get the current pagewidth deduct the margins we know the scale!!!
' Uses A4 as its papersize!!!
Debug.Print "Height: " & lngRow & " Width: " & lngCol

With wks.pagesetup
Debug.Print .LeftMargin
dblWid = 21 - ((.LeftMargin + .RightMargin) / 28.4)
dblHei = 29.7 - ((.LeftMargin + .RightMargin) / 28.4)
dblWid = lngCol / (dblWid * 4.05)
dblHei = lngCol / (dblHei * 4.05)
X = 1 ' Default page width
Y = 0 ' Successful format agreed

Do Until Y = 1
If X / dblWid < 0.3 And X / dblHei < 0.3 Then
If MsgBox("Width of " & X & " pages is too small. Increase to " & X + 1 & "?", vbYesNo, "Formatting trouble") = vbYes Then
X = X + 1
Else
If MsgBox("The report is too large to fit in portrait. Would you like to use landscape?", vbYesNo, "Formatting trouble") = vbYes Then
.Orientation = xlLandscape
End If
Y = 1
End If
ElseIf X / dblWid < 0.3 And X / dblHei >= 0.3 Then
If MsgBox("The report is too large to fit in portrait. Would you like to use landscape?", vbYesNo, "Formatting trouble") = vbYes Then
.Orientation = xlLandscape
End If
Y = 1
Else
Y = 1
End If
Loop

.PrintTitleRows = ""
.PrintTitleColumns = ""
.PrintArea = ""
.Zoom = False
.fittopageswide = X
.fittopagestall = False
.CenterFooter = "&D &T"
End With

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.

 

[mike777]

Wow...I don't quite understand your code. Could you possibly give a language-summary?
I never got a solution to my problem.
If I understand your solution, then that will be what I use.
Thanks.
-Mike

 

[GummowN]

It is part of a module that exports a recordset from Access.

Avartransposed in the recordset data from Access. Prior to this code the spreadsheet is completely formatted - column widths the lot.

The code takes the columnwidth of each column in the range (in my case the ubound of the array) and adds them together.

The dblWid is basically the width of the paper in CM once the margins have been removed (the 28.4 is a fudge factor - but does work). Note that this is for A4 paper and you might want to fudge or have a lookup table of paper sizes, I couldnt be bothered to try something clever!

The column width over the page width (using a 4.05 fudge factor - again it works) gives the number of pages wide it would be at 100%.

I therefore say if 1 page over the number of pages normally in portrait and landscape would be 0.3 - ie 30% - then ask them questions about what to do.

The bottom section is just what I do in the pagesetup.

As I said it is a big hack/bodge/workround - but it does work in my case - and the users seem to like its semi-intelligence.

If at first you don't succeed, try

http://www.tek-tips.com

for the answer.