Sign in to follow this  
Followers 0
fitzgerac

Excel 2010 detect password prompt

6 posts in this topic

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!):

#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

Share this post


Link to post
Share on other sites



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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0