Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change

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


Flex the Geek

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://291180]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (2)
As of 2021-10-19 05:19 GMT
Find Nodes?
    Voting Booth?
    My first memorable Perl project was:

    Results (76 votes). Check out past polls.