Excel 2010 detect password prompt

[fitzgerac]

Hi all, I have been looking all through the help files and forum, but I just can't figure out my issue. Here is what I'm trying to do:

We had an error on our file server which has caused corruption of some of our files. So, I am writing an AutoIT script that will attempt to open each Excel file on the network drive. If the file is corrupted, the script will fail to open the Excel file, record the error in a log file, then move on to the next file in the list.

I've got this mostly working, except for when AutoIT attempts to open an Excel file that has been password protected by the user. I am using the Excel.Application com object to open the Excel files. But it seems like what's happening is that the Workbooks.Open method does not 'finish' until the Password message box has been acknowledged. Anybody have any ideas? Thanks in advance. Here is my code (sorry it's so long!):

expandcollapse popup

#include <Excel.au3>
$oMyError = ObjEvent("AutoIt.Error", "MyErrFunc") ; Install a custom error handler, need this to catch errors opening files because of corruption
Dim $FileSize,$WindowText, $WindowText2, $SleepTime, $ErrorMessage
Global $FileName

; --------- Get text files ready for reading and writing ----------------
;Open Input File contains list of files to check
$InputFilePath = "C:\FileList.txt"
$InputFileObject = FileOpen($InputFilePath,0)
If $InputFileObject = -1 Then 
   MsgBox(0,@ScriptName,'failed to open the Input File')
   Exit 1
EndIf

;Create Detailed Log file
$DetailLogPath = "C:\DetailedLog.log"
$DetailLogObject = FileOpen($DetailLogPath,2)
If $DetailLogObject = -1 Then
   FileClose($InputFileObject)
   MsgBox(0,@ScriptName,'failed to open the Detailed log File')
   Exit 1
EndIf

;Create report file
$ReportFilePath = "C:\FileCheckReport.csv"
$ReportFileObject = FileOpen($ReportFilePath,2)
If $ReportFileObject = -1 Then 
   FileClose($InputFileObject)
   FileClose($DetailLogObject)
   MsgBox(0,@ScriptName,'failed to open the Report File')
   Exit 1
Else
   FileWriteLine($ReportFileObject,"File Path,File Size,File Open Status,Error Message")
EndIf
;-----------------------------------------------------------------------------------


;=======================MAIN LOGIC==================================================================
While 1
   $FileName = FileReadLine($InputFileObject)
   If @error then ExitLoop ;exit the loop if at end of File
   TestFile($FileName)
WEnd
FileWriteLine($DetailLogObject,"===============================================================================================================")
FileWriteLine($DetailLogObject,"Script Ended at: "&@MON&"/"&@MDAY&"/"&@YEAR&" "&@HOUR&":"&@MIN&":"&@SEC)
FileClose($InputFileObject)
FileClose($DetailLogObject)
FileClose($ReportFileObject)
;====================================================================================================


Func TestFile($FileName)
   MsgBox(0,"AutoIT","Process Next File?")
   FileWriteLine($DetailLogObject,"------------------------------------------------------------------------------------------------------------")
   FileWriteLine($DetailLogObject,"File check Started at: "&@MON&"/"&@MDAY&"/"&@YEAR&" "&@HOUR&":"&@MIN&":"&@SEC)
   ;getting file size
   $FileSize = Ceiling((FileGetSize($FileName))/1048576)
   FileWriteLine($DetailLogObject,"Checking file: "&$FileName)
   FileWriteLine($DetailLogObject,"File size: "&$FileSize&"MB")
  
   ;Open the file with Excel
   ;$oExcel = _ExcelBookOpen($FileName)
   $oExcel = ObjCreate("Excel.Application")
   $oExcel.Workbooks.Open($FileName)
   $oExcel.Visible = True
   IF @Error Then
      FileWriteLine($DetailLogObject,"Excel encountered an error")
   Else
      AutoItSetOption("WinTitleMatchMode", 1)
      WinWaitActive("Microsoft Excel")
      FileWriteLine($DetailLogObject,"Excel now open")
      $WindowText = WinGetText("[active]")
      FileWriteLine($DetailLogObject,"File opened OK")    
          
      AutoItSetOption("WinTitleMatchMode", 1)
      msgbox(0,"AutoIT",WinGetTitle("[active]"))
      If WinGetTitle("[active]")="Password" Then
         FileWriteLine($DetailLogObject,"Password protected file message detected")
         FileWriteLine($ReportFileObject,$FileName&","&$FileSize&","&"FAILED, Excel message: "&$WindowText)
         FileWriteLine($DetailLogObject,"Attempting close password message (Send Escape)")
         Send("{ESCAPE}") ;close the error message
      EndIf
      
      _ExcelBookClose($oExcel,0,0) ;close excel
      ;detect Excel windows still open
      ;AutoItSetOption("WinTitleMatchMode", 2)
         If WinGetTitle("[active]")="Microsoft Excel" Then
            ;excel is still open
            $WindowText2 = WinGetText("[active]")
            FileWriteLine($DetailLogObject,"Excel is still open with message: "&$WindowText2)
         Else
            FileWriteLine($DetailLogObject,"Excel closed.")
         EndIf
   EndIf
EndFunc

; This is my custom error handler
Func MyErrFunc()
   $HexNumber = Hex($oMyError.number, 8)
   $ErrorMessage = "AutoIt intercepted a COM Error! Number is: " & $HexNumber & _
    " Windescription is: " & $oMyError.windescription & _
    " Source Obj is: " & $oMyError.windescription & _
    " Description is: " & $oMyError.windescription
   FileWriteLine($DetailLogObject,$ErrorMessage)
   FileWriteLine($ReportFileObject,$FileName&","&$FileSize&",FAILED,"&$ErrorMessage)
   Return SetError(1) ; something to check for when this function returns
EndFunc

[water]

Insert

$oExcel.DisplayAlerts = False

before you open the Excel file.

I think it should suppress certain alerts and messages and use the default response.

If you don't pass the password the Open method should return an error.

Can't test at the moment so I'm not 100% sure it works the way described.

[fitzgerac]

Thanks Water, but that did not suppress the password prompt.

[fitzgerac]

I have also tried passing a blank password when opening the files:

$oExcel.Workbooks.Open($FileName),"Password:="&Chr(34)&Chr(34))

And this works for files that are actually password protected in that it causes a COM error that my custom error handler can catch. Unfortunately, when the same blank password is passed to a file that is not password protected, the exact same COM error is produced, which means I can't separate the 2 scenarios to respond accordingly.

If only there was a way to determine if the file was password protected before I actually attempt to open the file. Anybody have any suggestions? Thanks!

[water]

Start a second script that waits for the password prompt and then presses Cancel or enters the correct password. Have a look at my OutlookEX UDF. _OL_Open uses a second script to process the Outlook security warnings.

[fitzgerac]

Ha ha, thanks Water...that's exactly what I did. I was just gonna post a reply explaining the same thing.