|
Posted by aaron80v on April 6, 2007, 1:06 am
Please log in for more thread options
Hi,
I modified slightly the sample codes shown here,
http://www.ngbdigital.com/perl_ole_excel.html. However, when I tried
to run it, it keeps generating the error, "Can't use an undefined
value as a HASH reference at ole5.pl line 27." which is the line that
says "my $LastRow = $Sheet->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious, SearchOrder=>xlByRows})->;"
Can I point me to why I am facing this problem?
My environments:
1. Windows XP SP2
2. Excel 2002
3. Active Perl-5.8.8.817
The sample code I am running is
========================
use strict;
use warnings;
use Win32::OLE;
use Win32::OLE qw(in with);
use Win32::OLE::Variant;
use Win32::OLE::Const 'Microsoft Excel';
my $Excel = Win32::OLE->GetActiveObject('Excel.Application') ||
Win32::OLE->new('Excel.Application');
$Excel-> = 0; #0 is hidden, 1 is visible
$Excel->=0; #0 is hide alerts
# Open File and Worksheet
my $Book = $Excel->Workbooks->Open
('E:\tmp\test1.xls'); # open Excel file
my $Sheet = $Book->Worksheets(1);
# Create New Workbook
$Excel-> = 1;
my $Book2 = $Excel->Workbooks->Add();
my $Sheet2 = $Book2->Worksheets(1);
$Sheet2-> = 'My test worksheet';
# Find Last Column and Row
my $LastRow = $Sheet->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious, SearchOrder=>xlByRows})->;
my $LastCol = $Sheet->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious, SearchOrder=>xlByColumns})->;
my $mylastcol = '';
for (my $m=1;$m<$LastCol;$m++)
my $mylastcol2 = '';
for (my $m=1;$m<($LastCol - 1);$m++)
# Draw Borders
my @edges = qw (xlInsideHorizontal xlInsideVertical);
my $range = "a1:$mylastcol$LastRow";
foreach my $edge (@edges)
{
with (my $Borders = $Sheet->Range($range)->Borders(eval($edge)),
LineStyle =>xlContinuous,
Weight => xlThin ,
ColorIndex => 1);
}
# Cell Values
$Sheet->Range('a1')-> = $Sheet->Range('b2')->;
# Resize Columns
my @columnheaders = qw(A:B);
foreach my $range(@columnheaders){
$Sheet->Columns($range)->AutoFit();
}
# Order Rows
my $tmp = "$mylastcol2".'3';
my $Rangea = $Sheet->Range("$tmp");
my $Rangeb = $Sheet->Range("a3");
$Excel->Selection->Sort({Key1 => $Rangea,
Order1 => xlDescending,
Key2 => $Rangeb});
# Merge Cells
my $mynextcol = 'b';
for (my $n=1;$n<$LastCol;$n+=2)
{
my $range = $mynextcol++ . '1:' . $mynextcol++ . '1';
$Sheet->Range($range)->Merge();
$Sheet->Range($range)-> = xlHAlignCenter;
}
# Pie Chart
my $Range = $Sheet->Range('a1:d2');
my $Chart = $Book->Charts->Add;
$Chart->ChartWizard($Range,xl3DPie,7,xlRows,1,0,2,"Sales
Percentages");
# Bar Graph and Rotate
$Range = $Sheet->Range('a1:a3');
$Chart = $Excel->Charts->Add;
$Chart-> = xl3DColumn;
for (my $i = 30; $i <=180; $i+=10)
{
$Chart-> = $i;
sleep(1);
}
# Line Chart and Save
$Range = $Sheet->Range('a1:d2');
$Chart = $Excel->Charts->Add;
$Chart-> = xlLine;
$Chart->SetSourceData({Source => $Range, PlotBy => xlColumns});
$Chart-> = 1;
$Chart->ChartTitle-> = "Some Title";
my $ChartObj = $Sheet->ChartObjects;
$Chart->Export({
FileName => my $filegraphname,
FilterName => 'GIF',
Interactive => 0});
# Save as PDF
$Excel->ActiveWindow->SelectedSheets->PrintOut({
Copy => 1,
ActivePrinter => 'Acrobat PDFWriter'});
# Save as Excel
$Book->SaveAs({Filename =>'E:\tmp\test1.xls',
FileFormat => xlWorkbookNormal});
$Book->Close();
$Excel->Quit();
========================
|
|
Posted by Mumia W. on April 6, 2007, 3:39 am
Please log in for more thread options
On 04/06/2007 12:06 AM, aaron80v@yahoo.com.au wrote:
> Hi,
>
> I modified slightly the sample codes shown here,
> http://www.ngbdigital.com/perl_ole_excel.html. However, when I tried
> to run it, it keeps generating the error, "Can't use an undefined
> value as a HASH reference at ole5.pl line 27." which is the line that
> says "my $LastRow = $Sheet->UsedRange->Find({What=>"*",
> SearchDirection=>xlPrevious, SearchOrder=>xlByRows})->;"
>
> Can I point me to why I am facing this problem?
> [...]
Evidently the 'Find' method returns an undefined value. Perhaps you are
not using the method properly, or there may be no objects of the
requested type in the file.
|
| Similar Threads | Posted | | Help Perl and Excel | August 16, 2006, 9:25 am |
| OLE Module - Need to convert Excel VB Macro to perl | August 3, 2005, 9:06 am |
| OLE Excel AutoFilter | September 10, 2006, 4:28 pm |
| Excel Data conversion | July 27, 2004, 2:37 pm |
| Problem with Name of Excel Chart | September 22, 2005, 9:21 am |
| DBD:Excel on unix machine | March 9, 2006, 11:18 am |
| Everything but the column: win32::OLE Excel fun | June 7, 2006, 9:07 pm |
| Writing row at a time in Excel using OLE | June 14, 2007, 1:43 pm |
| How to write into existing Excel Spreadsheets? | July 13, 2004, 1:24 pm |
| Can I write Macros in excel workbook ? | October 8, 2004, 7:50 am |
|
XML Sitemap