http://qs321.pair.com?node_id=291180


in reply to Excel Save & Cell Protect

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});

C-.

---
Flex the Geek