How can I suppress Excel's password prompt in Perl?

Question

Please tell me a solution to suppress passsword prompting of an excel file.

    use Win32::OLE;

      my $xlApp = Win32::OLE->new('Excel.Application');

      $xlApp->{Visible} = 0;

      $xlApp->{DisplayAlerts} = 0;

      # Open excel file.
      my $xlBook = $xlApp->Workbooks->Open("C:\\Documents and Settings\\username\\Desktop\\testfile.xls");
      my $Sheet1 = $xlBook->Worksheets(1);

        my $row = 1;
        my $col = 1;

       $Sheet1->Cells($row,$col)->{'Value'} = 5;

        if (Win32::OLE->LastError) 
        {
                print "File protected";
        }

      $xlBook ->Close();

  undef $xlBook;

Answer 1

You may convert the following vb code to perl and give a try, Please note that this code is for vbproject, similary you can check for the worksheets, cells, or entire sheet, the same way.

' returns TRUE if the VB project in the active document is protected ' Please not

Function ProtectedVBProject(ByVal wb As Workbook) As Boolean

Dim VBC As Integer

  VBC = -1

  On Error Resume Next

  VBC = wb.VBProject.VBComponents.Count

  On Error GoTo 0

  If VBC = -1 Then

    ProtectedVBProject = True

  Else

    ProtectedVBProject = False

  End If

End Function

Example:

If ProtectedVBProject(ActiveWorkbook) Then Exit Sub

For Worksheet

If ActiveWorkbook.ProtectStructure=True  Then Exit Sub

For active work book windows

If ActiveWorkbook.ProtectWindows= True Then Exit sub

and so on..

Or You can open excel sheet with password

The Open method for the Workbook object, takes 12 arguments. To open a workbook with password protection, you would need to write the following code:

 Workbooks.Open "Book1.xls", , , ,"pswd"

You can also check with perl the same with empty arguments. I am not sure how to give...

Answer 2

If you know the passwords, you can supply them in the password and/or writerespassword arguments of the open command. Excel will not prompt for the passwords if they are supplied this way.

If you don't know the passwords but want to prevent the dialog box from appearing, you can supply dummy passwords in these parameters ("ThisIsNotAPassword", for instance). I have not found this in the documentation, but tested it in Excel 2003:

• If the Excel file does not have passwords, it is opened.
• If it does have passwords (other than those supplied), it will not ask the user for a password, but fail with an error you can detect.

Answer 3

You may be better off using Spreadsheet::ParseExcel and Spreadsheet::WriteExcel than messing around starting up a copy of Excel and mucking around with OLE.

It'd be a lot more portable too.

Answer 4

Working off of lakshmanaraj's idea, and unknown's response:

use Win32::OLE;

sub is_protected_vb_project { 
    my $work_book = shift;
    eval { 
        my $count = $work_book->{VBProject}{VBComponents}{Count};
    };
    Carp::carp $@ if $@;
    return $@ ? 1 : 0;
}

my $work_book = Win32::OLE->GetObject( 'd:/some/path/somewhere.xls' );
printf "is_protected_vb_project( \$work_book )=%s\n"
     , is_protected_vb_project( $work_book )
     ;