XL: Error setting print area + multiple ranges 1

[01Patrik]

Hi,

I'm confused. I can't get XL to programatically set the print on a sheet to the mulitple ranges that I have as a print area on another sheet!

Now normally I'd use

    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$5"
        .PrintTitleColumns = "$A:$F"
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With

    ActiveSheet.PageSetup.PrintArea = "$G$7:$L$50;$G$52:$L$103;$N$7:$S$50;$N$52:$S$103;$U$7:$Z$50;$U$52:$Z$103;$AB$7:$AG$50;$AB$52:$AG$103;$AI$7:$AN$50;$AI$52:$AN$103"

...but that isn't working.

If I capture the multiple range by recording the print setup on then other sheet - like below:

    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$5"
        .PrintTitleColumns = "$A:$F"
    End With
    ActiveSheet.PageSetup.PrintArea = "$G$7:$L$50;$G$52:$L$103;$N$7:$S$50;$N$52:$S$103;$U$7:$Z$50;$U$52:$Z$103;$AB$7:$AG$50;$AB$52:$AG$103;$AI$7:$AN$50;$AI$52:$AN$103"

    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.78740157480315)
        .RightMargin = Application.InchesToPoints(0.78740157480315)
        .TopMargin = Application.InchesToPoints(0.984251968503937)
        .BottomMargin = Application.InchesToPoints(0.984251968503937)
        .HeaderMargin = Application.InchesToPoints(0.511811023622047)
        .FooterMargin = Application.InchesToPoints(0.511811023622047)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
    End With

...then one would think that would work. But nooo.


The reason I need this is to transfer the pretty complex report print structure to text-copies of the file.

Any ideas?

(Thread originally posted in Office forum)

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[MJPPaba]

change all the ; to , should work then.

cheers

crazy times call for crazy people


CrazyPabs


sometimes I wonder why I even bova...

 

[MJPPaba]

also works well with an array, just to be on the safe side, Excel doesn't like string lengths of 255 characters sometimes.


ActiveSheet.PageSetup.PrintArea = Array("G7:L50,G52:L103,N7:S50,N52:S103,U7:Z50,U52:Z103,AB7:AG50,AB52:AG103,AI7:AN50,AI52:AN103")

Cheers


crazy times call for crazy people


CrazyPabs


sometimes I wonder why I even bova...

 

[01Patrik]

Right you are, have a shiny one!



...but how can the situation arise at all? The multiple range was captured by recording a macro. I can understand that because PrintArea is set to use strings, the exact content of the string is determined by the content of the print area box in print setup. But since that one REQUIRES the use of ";" instead of "," because of my regional settings (Swedish), it should adjust using something similar to FormulaLocal vs Formula, shouldn't it? Now it was impossible to find any reference work on this matter, on the web or in VBA help, since everybody's using English settings...

So, did someone miss out, or am I just stupid?

(What DOES that goalkeeper do? He runs of the pitch - and the goal is wide open!)


// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[MJPPaba]

What do you have as your setting if you go..

[Windows Start Button]..
[Settings]..
[Control Panel]..
[Regional Options]
[Numbers Tab]..
[List Seperator]..

I have a comma (,) I wonder if you are showing a semi colon ( as your regional setting. Have you considered maybe changing this? What is your Windows Version, I am using 2K Pro.

P.S. Thanks for the Star,


crazy times call for crazy people


CrazyPabs


sometimes I wonder why I even bova...

 

[01Patrik]

Yaeh I have a semicolon, but since that's the regional standard in Sweden I'd be pretty cripled if I changed it (have concidered many times, not least since joining this forum).

But this is actually the first time it's been a problem, normally everythings compatible: VBA still captures the correct syntax when recording .formula, and gets my regional date definitions right - So why not when recording / capturing (****ing) PrintArea? Since it only accepts strings (no semicolons) in VBA but does accept semicolons when entered in the user interface, the 'translation' of my regional semicolons must lie within the PrintArea code.

Obviously I'm missing something? Or have they (--> MS)?


// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.