Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

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?

Comment on Excel Save & Cell Protect
Re: Excel Save & Cell Protect
by Anonymous Monk on Sep 11, 2003 at 08:34 UTC

    Hi,

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

      Example

      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!

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.

    $Excel->{DisplayAlerts}=0;"

    Cheers

    hagen

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

    C-.

    ---
    Flex the Geek

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://290615]
Approved by gmax
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (10)
As of 2014-12-19 14:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (84 votes), past polls