in reply to coding for excel in perl

Welcome to the Monastery ruxer!

It is customary that you show some relevant example code you have written yourself. In this case --for instance-- a small Perl-script which opens an Excel-file, selects and put in the copy buffer some cells and then needs to be completed with the code to release the copy-selection.

You are much more likely to receive favourable answers if you show that you have at least tried but failed, rather than having failed for not having tried at all. It is not a shame to fail, but it is a sin in our Monastery not to try.

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Replies are listed 'Best First'.
Re^2: coding for excel in perl
by ruxer (Acolyte) on Dec 27, 2009 at 15:53 UTC

    Thank you CountZero!

    The following --portion of script-- is copying from one sheet and pasting to another to prepare for print. Everything works beautifully except for the line "$Excel->Application->CutCopyMode = False;".

    I have tried writing the line several different ways and I have searched google and multiple other sights for ideas.

    Opening of script is: 

    use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use warnings;

#Content-Type: text/html;

$Win32::OLE::Warn = 3;    # die on errors...

# get already active Excel application or open new
$Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit');  

# open Excel file
$Book = $Excel->Workbooks->Open("c:/users/david/desktop/RBLCopy.xls");

    [download]
    The following is the portion of script containing the line in question. 
    # print routine ################
print "\nPrint (y/n) ";
chomp($input = <STDIN>);
if ($input eq 'y') {
    $lSheet = $Book->Worksheets("Location");
    $iSheet = $Book->Worksheets("Input");
    $oSheet = $Book->Worksheets("Output");
    
    # Clear Input screen
    $iSheet->Activate();
    if ($iSheet->Range("C4")->{Value} ne '') {
        $iSheet->Range("C4:J10000")->ClearContents();
    }
        
    # Move Location data to Input Screen
    $lSheet->Activate();
    $lSheet->Range("A5:A10000")->Copy();
        $iSheet->Activate();
        $iSheet->Range("C4")->Select();
        $iSheet->Paste();
    $lSheet->Activate();
    $lSheet->Range("L5:L10000")->Copy();
        $iSheet->Activate();
        $iSheet->Range("D4")->Select();
        $iSheet->Paste();
    $lSheet->Activate();
    $lSheet->Range("E5:J10000")->Copy();
        $iSheet->Activate();
        $iSheet->Range("E4")->Select();
        $iSheet->Paste();
        $iSheet->Range("A1")->Select();
    $lSheet->Activate();
    $Excel->Application->CutCopyMode = False;
    $lSheet->Range("A1")->Select();
    
    print "\nAre you ready to print? (y/n)";
    chomp($input = <STDIN>);
    if ($input eq 'y') {
        # print page 1
        $oSheet->Activate();
        $Excel->ActiveWindow->SelectedSheets->Printout();
        
        # print page 2
        $iSheet->Activate();
        if ($iSheet->Range('C34')->{Value} ne '') {
            $iSheet->Range("C34:K63")->Copy();
            $iSheet->Range("C4")->Select();
            $iSheet->Paste();
            $oSheets->Activate();
            $oSheets->Range("K1")->Select();
            $Excel->ActiveWindow->SelectedSheets->Printout();
        }
        # print page 3
        $iSheet->Activate();
        if ($iSheet->Range('C64')->{Value} ne '') {
            $iSheet->Range("C64:K93")->Copy();
            $iSheet->Range("C4")->Select();
            $iSheet->Paste();
            $oSheets->Activate();
            $oSheets->Range("K1")->Select();
            $Excel->ActiveWindow->SelectedSheets->Printout();
        }
    }
}

    [download]
    Thank you in advance for any help you can give.

    david

[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom