$xl = Win32::OLE->new('Excel.Application');
$xl->{CutCopyMode} = 0;
[download]

Thank you, John. You are so right! I had done some trial and error and came up with that same line. Came online to let everyone know. It works perfectly. Thank you so much for your response.

david

Basically, you use Win32::OLE to make the same calls from Perl that you would do from VBA. The Win32::OLE documentation shows how to translate the calls.

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.

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

Given:

$Excel = Win32::OLE->new('Excel.Application');
[download]

$Excel->{CutCopyMode} = 0;
[download]

ruxer

If you plan to use Win32::OLE to interact with Microsoft applications often - I have found ActiveState's VBScriptConverter which ships with their PDK Productivity Tools to be very helpful. This program will convert VB Macros directly to Perl code.

Another useful tip: After recording a Macro in Excel - press Alt+F11. This will open up Visual Basic where you will be able to view and edit the VBScript generated when you recorded the Macro. You can then convert this to perl yourself using the rules in the Win32::OLE documentation - or paste it into the VBScriptConverter and let it do the work.

From within Visual Basic - you can also press F2 (or select View->Object Browser) to bring up the object browser. Here you can view the different attributes and methods of the different objects you can interact with.

I find that when you are unsure of how to do something, this is the easiest way to figure it out is to record the Macro and then look at the VBScript that was generated. This is usually much easier and quicker than piling through documentation.