Hi,

Why do't you just do $excel->Workbooks(1)->Save then? You only need SaveAs($fname) if you are changing the name/location of the file.

If you've got Excel installed just do ALT+F11 to get the the VB IDE and then insert a module, type your keywords (e.g. Protect) and hit F1 (help) for the VB syntax.

Something like this should work although you really need to use the manual!

$excel->sheet(1)->Cells(1, 1)->CurrentRegion->Cells->Protect
[download]

Many thanks for your reply. I would like to use the manual if only I knew which one it was! Therefore anything like a title would help greatly.

I tried the ALT+F11 with protect and got the following

Example

As it applies to the Chart and Worksheet objects.

This example protects the active worksheet. You can verify the worksheet is protected, by attempting to enter a value into any cell, on the active worksheet.

Sub ProtectSheet()

ActiveSheet.Protect Scenarios:=True, UserInterfaceOnly:=True

End Sub

This example protects the active chart. You can verify the chart is protected, by attempting to enter a value into any cell, on the active worksheet. This example assumes a chart exists in the application.

Sub ProtectChart()

ActiveChart.Protect Scenarios:=True, UserInterfaceOnly:=True

End Sub

This only shows how toprotect whole sheets or charts. I want to protect indivual cells. Therefore I guess I really do need the book!

Hi anon. You should take a look at cacharbe's node 153486 which I've used to good effect to get a quick handle on Excel munging.

In particular it contains the following re avoiding Excel's warning dialogs:-

"For the sake of this program, we'll turn off all those pesky alert boxes, such as the SaveAs response "This file already exists", etc. using the DisplayAlerts property.

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

Cheers

hagen

I received a private email from the author and answered this question, but I'll post the answer here, just incase others come looking.

You have to protect an entire sheet, and then only allow certain ranges to be edited. It seems kind of backwards, but there you go. Reject All, allow by exception, if you will. And this functionality is only available in Office XP forward. Office 2000 doesn't support the Sheet->Protection objects.

So, this is what you end up with:

#!c:\perl\bin\
use strict;

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Win32::OLE::Variant;
use Win32::OLE::NLS qw(:LOCALE :DATE);

$Win32::OLE::Warn = 3; # Die on Errors.

my $excelfile = 'c:\perl\projects\win32\excel\protect.xls';

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
        || Win32::OLE->new('Excel.Application', 'Quit'); 
   $Excel->{DisplayAlerts}=0;   

my $Book = $Excel->Workbooks->Add();
   $Book->SaveAs($excelfile); 
      
    my $Sheet = $Book->Worksheets("Sheet1");
       $Sheet->Activate();
       $Sheet->{Name} = "DidItInPerl";

my $vtfalse =  Variant(VT_BOOL, 0);
my $vttrue =  Variant(VT_BOOL, 1);
my $Range = $Sheet->Range("A1:c2");

## You can only use this function in XP forward from what I can see. 

$Sheet->Protection->AllowEditRanges->Add({Title=>"MyRange", Range=>$Ra
+nge});

## This, however, will work
$Sheet->Protect(                                         
{DrawingObjects=>$vttrue, 
Contents=>$vttrue, 
Scenarios=>$vttrue,});

## And you can also include the following in XP
# AllowFormattingCells=>$vttrue, 
# AllowFormattingColumns=>$vttrue,
# AllowFormattingRows=>$vttrue, 
# AllowInsertingColumns=>$vttrue, 
# AllowInsertingRows=>$vttrue, 
# AllowInsertingHyperlinks=>$vttrue, 
# AllowDeletingColumns=>$vttrue,
# AllowDeletingRows=>$vttrue});

[download]

C-.

---
Flex the Geek