Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

Excel Save & Cell Protect

by Anonymous Monk
on Sep 11, 2003 at 08:23 UTC ( #290615=perlquestion: print w/replies, xml ) Need Help??
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Answers to either of the following will be much appreciated.

Excel Save

I want to overwrite an existing Excel spreadsheet without being asked the question 'Do I really want to do this'. I could delete the file before overwriting but I wondered if there was an option for the $excel->Workbooks(1)->SaveAs(filename)that allowed this.

Cell Protection

I want to protect cells of an Excel spreadsheet that I create so that when using Excel to read the spreadsheet these cells cannot be modified. How can this be done?

Replies are listed 'Best First'.
Re: Excel Save & Cell Protect
by Anonymous Monk on Sep 11, 2003 at 08:34 UTC


    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
      I tried the ALT+F11 with protect and got the following


      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!

      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.
Re: Excel Save & Cell Protect
by hagen (Friar) on Sep 12, 2003 at 06:26 UTC

    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.




Re: Excel Save & Cell Protect
by cacharbe (Curate) on Sep 12, 2003 at 20:41 UTC

    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
Node Status?
node history
Node Type: perlquestion [id://290615]
Approved by gmax
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (5)
As of 2017-01-17 05:33 GMT
Find Nodes?
    Voting Booth?
    Do you watch meteor showers?

    Results (152 votes). Check out past polls.