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

I have two chunks of code which will not work and I don't understand why. After creating a new workbook and addin a new sheet, I try to deactivate the gridlines, but no error message comes and the gridlines are still there: 
my $newworksheet = $activeWorkbook->Worksheets->Add({Before=>$activeWo
+rkbook->Worksheets(1)})  or die $!;
$newworksheet -> {Name} = "Chart$Tester";
#the sheet is created -> switch off the gridlines
$newworksheet -> DisplayGridlines = 'False';

[download]
the next problem is, i made a chart, via Win32::OLE (finaly I made it *g*) then I tryed to switch on the values (normaly select the column -> right click -> "Format Data Series..." -> "Data Labels" -> Checkbox "Value" on. I recorded a macro to make this via perl, but here also, no error message, but the values are noot displayed. 
my $Chart = $ChartSheet->ChartObjects->Add(@Position)->Chart; 
$Chart->{ChartType} = xlColumnClustered;
$Chart->SetSourceData({Source =>$Source, PlotBy => xlColumns});
$Chart->{HasTitle} = 1;
$Chart->ChartTitle->{Text} = "$Title";
$Chart->Axes(xlCategory, xlPrimary)->{HasTitle} = 1;
$Chart->Axes(xlCategory, xlPrimary)->AxisTitle->{Text} = "Groups";
$Chart->Axes(xlValue, xlPrimary)->{HasTitle} = 1;
$Chart->Axes(xlValue, xlPrimary)->AxisTitle->{Text} = "Utilization (in
+%)";
$Chart->Location({Where=>xlLocationAsObject, Name=>"$ChartSheet"});

#No Problem till here:      
$Chart->SeriesCollection(1)->ApplyDataLabels(AutoText=>'True', LegendK
+ey=>'False',
 ShowSeriesName=>'False',
 ShowCategoryName=>'False',
 ShowValue=>'True',
 ShowPercentage=>'False',
 ShowBubbleSize=>'False');

[download]
Thanks for help... and sorry for my bad english

Replies are listed 'Best First'.
Re: Problem with Excel and Win32::OLE
by Nkuvu (Priest) on Apr 29, 2004 at 18:08 UTC

    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.
[reply]
[d/l]
[select]
      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]
Re: Problem with Excel and Win32::OLE
by jdporter (Paladin) on Apr 29, 2004 at 19:47 UTC
    Furthermore, I think you need to pass an actual perl false value, not the string 'False', which is a true value in perl. I.e.: 
        ShowSeriesName => 0, # e.g.

    [download]
[reply]
[d/l]
[select]
      I was going to post the same thing - until I tested it. When you pass 'false' to the excel object, it treats it as a False value.

      Quick test: 

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

my $excel = Win32::OLE->CreateObject('Excel.Application');
$excel->{Visible} = 1;
sleep 5;
$excel->{Visible} = 'False';
sleep 5;
$excel->{Visible} = 1;
print "press enter...";
<STDIN>;

      [download]

      Excel will appear, then disappear for 5 seconds, then reappear.

[reply]
[d/l]

Back to Seekers of Perl Wisdom