Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Re: Excel Save & Cell Protect

by cacharbe (Curate)
on Sep 12, 2003 at 20:41 UTC ( #291180=note: print w/replies, xml ) Need Help??


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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://291180]
help
Chatterbox?
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (2)
As of 2016-12-09 03:22 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    On a regular basis, I'm most likely to spy upon:













    Results (148 votes). Check out past polls.