Morellio has asked for the wisdom of the Perl Monks concerning the following question:

First post here, let me know if I should be doing anything differently. Have had some success first getting into Excel/OLE and have learned through this site how to use macros to determine what tags to use. Thanks! One of the tags for setting vertical and horizontal alignment (to center text) does not appear to be having any effect despite all of the permutations I've tried. In some guides online I've seen examples like:

$sheet -> Range("G7:H7") -> {HorizontalAlignment} = xlHAlignCenter;   
+         # Center text;

[download]

However by making/editing a macro after manually centering the text I see the equivalent of the following:

$sheet -> Range("F1:S1")    -> {HorizontalAlignment} = "xlCenter";
$sheet -> Range("F1:S1")    -> {VerticalAlignment}     = "xlCenter";

[download]

Both seem to be recognized tags and don't toss errors, but none actually modify the format of those cells. They remain the default xlGeneral, xlBottom after the code runs. Here is the macro output I'm looking at pushing into OLE code.

    Range("A1:S1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With

[download]

Replies are listed 'Best First'.
Re: Another Excel/OLE question.
by Anonymous Monk on Jun 03, 2011 at 01:29 UTC
[reply]

      Thanks for the response and input! I had actually tried passing the value as a string and a constant with the same result. I wasn't actually sure it mattered, because no errors are thrown and if I set Merge to "True", it had the same effect as the constant True. To add a bit more detail, I am merging a bunch of cells and trying to add the title of an Excel spread sheet, and center it. Here's the nitty gritty:

      
#Insert logo and title
$sheet -> Range("1:1")        -> {RowHeight} = 60;
$sheet -> Range("F1:S1")    -> Font -> {Name}    = "Arial Rounded MT B
+old";
$sheet -> Range("F1:S1")    -> Font -> {Size}    = 36;
$sheet -> Range("F1:S1")    -> Font -> {Bold}    = True;
$sheet -> Range("F1:S1")    -> {MergeCells}        = True;    
$sheet -> Range("F1:S1")    -> {HorizontalAlignment}    = xlCenter;
$sheet -> Range("F1:S1")    -> {VerticalAlignment}         = xlCenter;
$sheet -> Range("F1:S1")    -> {Value}        = "Great Job!";

      [download]
      Read more... (173 Bytes)
[reply]
[d/l]
[select]
        I had actually tried passing the value as a string and a constant with the same result.

        Did you declare/define the constant?

[reply]

Back to Seekers of Perl Wisdom