Hello All, I am currently attempting to create an Excel workbook that contains several sheets of data, and on some sheets, charts.

The problem that I'm running into is that the $Chart->Location function does not place the chart on the worksheet I specify; it keeps putting it on the very first sheet.

Below is a snippet of the code where I write data to a sheet and then attempt to create a chart from that data. Both charts end up being placed on the very first sheet. 

#------------------------------------------------------------
# Make the all_amr_by_builds Sheet
#____________________________________________________________
$Sheet = $Book->Worksheets($allamrbybuildsNum);
$Sheet2 = $Book->Worksheets($allbuildsdataNum);
$row = 2;
$col = 1;
$val = "SMRs Authorized to AMR Builds:";
&Write($row, $col, $val, 3);
$row = $row + 1;
foreach $oldRow (2..$rows[0])
{
   $col = 1;
   foreach $oldCol (1..7)
   {
      $val = $Sheet2->Cells($oldRow, $oldCol)->{'Value'};
      if ((!($val)) && ($oldRow != 2)) { $val = "=0"; }
      if ($oldRow != 2) { &Write($row, $col, $val, 1); }
      else { &Write($row, $col, $val, 4); }

      $col = $col + 1;
   }
   $row = $row + 1;
}
#------------------------------------------------------------
# Make the all_amr_by_builds Chart
#____________________________________________________________
$sourceRow = $row-1;
$Range = $Sheet->Range("A3:G$sourceRow");
$Chart = $Book->Charts->Add;
#$Chart->{ChartType}=xlBarClustered;
$Chart->{ChartType}=xlColumnClustered;
$Chart->SetSourceData({Source => $Range, PlotBy => xwColumns});
$Chart->{HasTitle} = 1;
$Chart->ChartTitle->{Text} = "SMRs Assigned by Build";
$Chart->ChartTitle->Font->{Size}=10;
$Chart->{HasLegend} = 1;
$Chart->Legend->{Position} = xlBottom;
$Chart->Axes(xlCategory, xlPrimary)->{HasTitle} = 1;
$Chart->Axes(xlCategory, xlPrimary)->AxisTitle->{Text} = "By Build/Sev
+erity Rating";
$Chart->Axes(xlCategory, xlPrimary)->AxisTitle->Font->{Size}=10;
$Chart->Axes(xlValue, xlPrimary)->{HasTitle} = 1;
$Chart->Axes(xlValue, xlPrimary)->AxisTitle->{Text} = "# of SMRs";
$Chart->Axes(xlValue, xlPrimary)->AxisTitle->Font->{Size}=10;
$Chart->Location({Where => xlLocationAsObject, Name => $Sheet->{Name}}
+);
#$Chart = $Excel->ActiveChart;



#------------------------------------------------------------
# Make the history_cati-1s Sheet
#____________________________________________________________
$Sheet = $Book->Worksheets($histcati1sNum);
$Sheet2 = $Book->Worksheets($histdataNum);
$row = 1;
$col = 1;
$val = "Cumulative Status of CATI-1 SMRs (Less BCRs) - Authorized vs. 
+Resolved vs. Closed";
&Write($row, $col, $val, 3);
$row = $row + 1;

@histCATIheaders = ('Workflow States', 'Authorized to Work - Cumulativ
+e', 'Resolved', 'Closed-Cumulative');
foreach $val (@histCATIheaders)
{
   &Write($row, $col, $val, 4);
   $col = $col + 1;
}
$row = $row + 1;

foreach $oldCol (2..$cols[4])
{
   $col = 1;
   foreach $oldRow (1..4)
   {
      $val = $Sheet2->Cells($oldRow, $oldCol)->{'Value'};
      &Write($row, $col, $val, 1);

      $col = $col + 1;
   }
   $row = $row + 1;
}

#------------------------------------------------------------
# Make the history_cati-1s Chart
#____________________________________________________________
$name = "history_cati-1s";
my $Sheet = $Book->Worksheets("history_cati-1s");
$Sheet->Activate();
$sourceRow = $row-1;
$Range = $Sheet->Range("A3:D$sourceRow");
$Chart = $Book->Charts->Add;
$Chart->{ChartType}=xlColumnClustered;
$Chart->SetSourceData({Source => $Range, PlotBy => xwColumns});
$Chart->{HasTitle} = 1;
$Chart->ChartTitle->{Text} = "AMR";
$Chart->ChartTitle->Font->{Size}=10;
$Chart->{HasLegend} = 1;
$Chart->Axes(xlCategory, xlPrimary)->{HasTitle} = 1;
$Chart->Axes(xlCategory, xlPrimary)->AxisTitle->{Text} = "By Week";
$Chart->Axes(xlCategory, xlPrimary)->AxisTitle->Font->{Size}=10;
$Chart->Axes(xlValue, xlPrimary)->{HasTitle} = 1;
$Chart->Axes(xlValue, xlPrimary)->AxisTitle->{Text} = "# of SMRs";
$Chart->Axes(xlValue, xlPrimary)->AxisTitle->Font->{Size}=10;
$Chart->Location({Where => xlLocationAsObject, Name => $Sheet->{Name}}
+);
#$Chart->Location(xlLocationAsObject, $name);
#$Chart->Location(xlLocationAsObject, $Sheet->{Name});
#$Chart->Location(xlLocationAsObject, "history_cati-1s");
#$Chart->Location(2, $sheetName);
$Chart = $Excel->ActiveChart;

[download]
Any help is appreciated,
Thanks ~Vanessa L.~

BazB added readmore tags

In reply to Creating Multiple Excel Graphs on different Sheets by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.