Jim,
There seems to be a problem, at least in 2002, with calling on the
Page.Setup in XL4.
Run-time error '1004': The formula you typed contains an error.
It is probably something simple, but I'm just not sure what, having no
experience with XL4
This is where it chokes
pgSetup = "PAGE.SETUP(" & head & c & foot & c & _
LeftMarginInches & c & RightMarginInches & c & _
TopMarginInches & c & BottomMarginInches & c & _
PrintHeadings & c & PrintGridlines & c & _
CenterHorizontally & c & CenterVertically & c & _
Orientation & c & PaperSize & c & Zoom & c & _
FirstPageNumber & c & Order & c & BlackAndWhite & c & _
PrintQuality & c & HeaderMarginInches & c & _
FooterMarginInches & c & PrintComments & c & Draft & ")"
Application.ExecuteExcel4Macro pgSetup
This is how I am calling on the Sub
Call PageSetupXL4M("", "", "", "Kidney Foundation of Canada",
_
"Page &P of &N", "Online " & Data & "
Report", _
"", "", "", "", "", "", "$1:$4", "", "",
"", _
"", "", "xlLandscape", "", "xlPaperLegal",
"", _
"", "True", "")
It's probably a syntax in there somewhere. Thoughts?
Steven
On Sep 4, 2:41*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
Thanks for responding back and posting the web link. I knew I had seen that
code somewhere before, but since I could not remember where I just told you
to go looking for it. I will now keep a copy of that for future reference....
--
HTH...
Jim Thomlinson
" wrote:
Hi Jim Cone:
Conveniently enough, I found a webpage (thanks to Jim Thomlinson's
advice) that shared code to resolve exactly this problem.
http://www.mcgimpsey.com/excel/udfs/pagesetup.html
Very minimal learning on this one - copy/paste and declare the options
when calling on the PubSub.
I appreciate the clarification on the page setup properties. I was
trying to limit the number of rows per page to 40, hence the TallPages
calculation. I guess I appropriated that improperly.
It'll be a bit before I can use the next iteration of Excel from MS.
Limited by company budget - what they get is what I get. Right now
that is still 2002.
S
On Sep 4, 12:36 pm, "Jim Cone" wrote:
FitToPagesTall would normally be used to squeeze data that slightly
exceeded one page in length onto one page.
A TallPages calculation of 1000/40, if it worked, would result in
*something unreadable.
Also, zoom and fit to pages are mutually exclusive - use one or the other..
So... try commenting out the TallPages, FitToPages and the Zoom lines..
Then add this line just below your variable declarations...
Application.DisplayPageBreaks = False.
Also, as Jim Thomlinson pointed out, using XL4 pagesetup code can be
faster. *However, unless you are considerable smarter than average, it will take
more time than it is worth to master it. *(i had lots of spare time)
Furthermore, I recall reading that there is a high probably that MS will "improve"
the next release of Excel by eliminating the use of XL4 code.
--
Jim Cone
Portland, Oregon *USA
wrote in message
Hi all:
I'm using the following code to format the page setup for a workbook,
but as soon as I introduced it, *the process time increased from 15
seconds to 3.5 minutes. Can anyone tell me why my code is so slow?
DestWS is the worksheet in the workbook this code is acting on. There
are 2 workbooks generated by my previous code and multiple worksheets
that are created per workbook that the following code is applied to.
DestEntRng is the entire range of data in DestWS, anywhere from 5 rows
to 1000, columns A:Z. BookType is a simple string for the footer
field.
Public Sub RptPageSetup(ByVal DestWS As Worksheet, DestEntRng As
Range, BookType As String)
Dim TallPages As Long
TallPages = Round(DestEntRng.Rows.Count / 40, 0)
* * With DestWS.PageSetup
* * * * .PrintTitleRows = "$1:$4"
* * * * .PrintArea = DestEntRng.Address
* * * * .LeftFooter = "Kidney Foundation of Canada"
* * * * .CenterFooter = "Page &P of &N"
* * * * .RightFooter = "Online " & BookType & " Report"
* * * * .CenterHorizontally = True
* * * * .CenterVertically = True
* * * * .Orientation = xlLandscape
* * * * .PaperSize = xlPaperLegal
* * * * .FirstPageNumber = xlAutomatic
* * * * .Order = xlDownThenOver
* * * * .BlackAndWhite = True
* * * * .Zoom = 56
* * * * .FitToPagesWide = False
* * * * .FitToPagesTall = TallPages
* * End With
End Sub