in reply to Problem with Excel and Win32::OLE

Perhaps changing $newworksheet -> DisplayGridlines = 'False'; to $newworksheet -> {DisplayGridlines} = 'False'; may help? This is just a guess, though.

Also, the 

ApplyDataLabels(AutoText=>'True', LegendKey=>'False',
 ShowSeriesName=>'False',
 ShowCategoryName=>'False',
 ShowValue=>'True',
 ShowPercentage=>'False',
 ShowBubbleSize=>'False');

[download]
looks like it should probably be 
ApplyDataLabels({AutoText=>'True', LegendKey=>'False',
 ShowSeriesName=>'False',
 ShowCategoryName=>'False',
 ShowValue=>'True',
 ShowPercentage=>'False',
 ShowBubbleSize=>'False'}); # Note the { } added

[download]
Also just a guess.

Replies are listed 'Best First'.
Re: Re: Problem with Excel and Win32::OLE
by Streen (Sexton) on Apr 30, 2004 at 08:37 UTC
    First, thanks for the help, the Data Labels are now working. But I cannot switch the gridlines off, the Recorded VB macro was: 
    ActiveWindow.DisplayGridlines = False

    [download]
    I tried several codes also the one you showed me: You wrote: Perhaps changing $newworksheet -> DisplayGridlines = 'False'; to $newworksheet -> {DisplayGridlines} = 'False'; may help? This is just a guess, though. This didn't solve my problem, maybe you have some other suggetstion for switching off the gridlines? (Why the Lineends are cut of in the second half of my post?)
[reply]
[d/l]
[select]
      If that's what is recorded, 'ActiveWindow' ne 'Worksheet' (your code uses Worksheet.Gridlines). You need to translate each object in VBA into the appropriate object in Perl. To help with the translation, either use the Object Browser in Excel, the OLE-Browser packaged with ActiveState's Perl, or search for Object Model in Excel's VBA help file.

      Looking in the OLE-Browser for ActiveWindow, I see it is a property of the Application object. My application object is $excel in the following:

      use strict;
use warnings;
use Win32::OLE;

my $excel = Win32::OLE->CreateObject('Excel.Application', 'quit');
$excel->{Visible} = 1;
$excel->Workbooks->Add();
$excel->ActiveWindow->{DisplayGridlines} = 0; #gridlines disappear!
print "press enter...";
<STDIN>;

      [download]

      Remember - if you are having problems, check the Object Model!

[reply]
[d/l]
[select]
        Thanks for the help,

        I managed to solve the problem:
        $newworksheet->Activate();
$Excel->ActiveWindow->{DisplayGridlines} = 'False';

        [download]


        But now I have an other problem... I searched in the object browser, but I din't manage to solve the problem.
        In a chart I would like to set the font size to 8... I recorded the following macro and I tried to set them to 8 but the fonts still 8 and no error message comes up.
        The VB macro
        Selection.TickLabels.AutoScaleFont = False
With Selection.TickLabels.Font
    .Name = "Arial"
    .FontStyle = "Standard"
    .Size = 8
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .Background = xlAutomatic
End With

        [download]

        Because only the Size is needed I tried to convert it into the following perl code:
        $Excel->Selection->ThickLabels({AutoScaleFont => 0});
$Excel->Selection->ThickLabels({Font => {Size => 8}});

        [download]

        No error message and no size of 8, can anybody help me?
        Thanks a lot
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom