Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

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

Comment on Re: Excel Save & Cell Protect
Download Code

Log In?

What's my password?
Create A New User
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 having an uproarious good time at the Monastery: (3)
As of 2015-11-29 17:54 GMT
Find Nodes?
    Voting Booth?

    What would be the most significant thing to happen if a rope (or wire) tied the Earth and the Moon together?

    Results (751 votes), past polls