using Win32::OLE to copy or copy a range from one sheet to another

[Glen Plantz]

Title: Message

Hi Folks,

 

I've been using Perl for a number of years, and I'm now learning Win32::OLE for manipulating Excel files. I'm try to copy or cut a range of cells from one Worksheet to another. The "Select" portion works fine, but the "Paste" is not working..

 

Thanks in advance for any help.

 

Glen Plantz

Mitchell International

 

[EMAIL PROTECTED]

 

 

 

Here is my code:

 

my $excel_app = Win32::OLE->new('Excel.Application')
or die "could not create excel app\n";

 

$excel_app->{'Visible'} = 1;

$excel_app->{ 'SheetsInNewWorkbook' }  = 1;

 

my $excel_Workbook = $excel_app->Workbooks->Open($Excel_CSAA_Test_Retrieve )
or die "could not open $Excel_CurrentMonth_Retrieve\n";

 

# this section executes VBA functions in the Workbook opened above to retrieve data from an Hyperion Essbase database... this works fine

    $excel_app->RegisterXLL($Essbase_Addin_loc);

    $excel_app->{ 'DisplayAlerts' } = 0;

 

    my $excel_Workbook = $excel_app->Workbooks->Open($Excel_CSAA_Test_Retrieve )
    or die "could not open $Excel_CurrentMonth_Retrieve\n";
    my $m_sVBAFunctionName = "'" . $Excel_CSAA_Test_Retrieve . "'!Connect";

    my $retVal = $excel_app->Run($m_sVBAFunctionName, $Essbase_Server, $Essbase_UserID, $Essbase_Password, $Essbase_Application, $Essbase_Database);
    print "retVal = $retVal\n";
    $m_sVBAFunctionName = "'" . $Excel_CSAA_Test_Retrieve . "'!Retrieve";

 

    $retVal = $excel_app->Run($m_sVBAFunctionName);

# END - Data Retrieve....

 

   print "retVal = $retVal\n";

my $Sheet1 = $excel_Workbook->Worksheets(1);

 

$Sheet1->Range("A29:P39")->Select()
$Sheet1->Range("A29:P39")->Copy();

my $Sheet = $excel_Workbook->Worksheets->Add({After=>$excel_Workbook->Worksheets($excel_Workbook->Worksheets->{Count})}) or die Win32::OLE->LastError();

$Sheet->{Name} = "Glens"

 

$ works fine up to year,... the "Sheet1" excel sheet above shows that the range "A29:P39" has been selected and copied

 

# >>>>>>>>>>>>>>>>.... Here is where I'm running into trouble.... this Paste is not working...

 

$Sheet->Range("A29")->Paste();

 

 

 

_______________________________________________
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs