_Excel_BookOpen crashing random spreadsheets

[BigDaddyO]

I have a script that pull data out of each spreadsheet in a folder "These are error logs in .xlsx format"

It worked fine on my sample folder which only had 3 spreadsheets but when I'm running them on the full list (currently 123) I get an error on _Excel_BookOpen.  oddly it's not always on the same file.  I just ran it 5 times and each time it was a different spreadsheet.  Error is always the same.

$oExcel.Windows($oWorkbook.Name).Visible = $bVisible
$oExcel.Windows($oWorkbook.Name)^ ERROR

I tried adding the default Com error handler from the help file but when I did that, I actually got an AutoIT has stopped responding message.

This is my script to pull the data:

For $i = 1 To UBound($aSpreadsheets) - 1
    ConsoleWrite("Excel file:  " & $aSpreadsheets[0] & "\" & $aSpreadsheets[$i] & @CRLF)
    $oExcel = _Excel_Open(False, False, False)
    If @error Then
        If MsgBox(262161, "Error", "Unable to open the following report" & @CRLF & $aSpreadsheets[0] & "\" & $aSpreadsheets[$i] & @CRLF & "Click OK to skip and continue, or Cancel to stop") = 2 Then Return SetError(1)
    Else
        $oExcelBook = _Excel_BookOpen($oExcel, $aSpreadsheets[0] & "\" & $aSpreadsheets[$i], True, False)
        if $i = 1 Then
            $aData = _Excel_RangeRead($oExcelBook, Default, $oExcelBook.ActiveSheet.Usedrange.Columns($Columns), 1, True)
            _ArrayDelete($aData, 0) ;Removes the header row
        Else
            $aDataB = _Excel_RangeRead($oExcelBook, Default, $oExcelBook.ActiveSheet.Usedrange.Columns($Columns), 1, True)
            _ArrayDelete($aDataB, 0) ;Removes the header row
        EndIf

        If @error Then
            MsgBox(0, "Excel Error", "Error = " & @error & @CRLF & "Extended = " & @extended)
            _Excel_Close($oExcel, False)
            SetError(1)
            Return
        EndIf
        _Excel_BookClose($oExcelBook, False)
        _Excel_Close($oExcel, False)
        If $i > 1 Then _ArrayConcatenate($aData, $aDataB)
    EndIf
Next

I'm using v3.3.14.1

Any ideas?

Thanks,

Mike

[water]

Move _Excel_Open out of the loop:

$oExcel = _Excel_Open(False, False, False)
If @error Then
    If MsgBox(262161, "Error", "Unable to open the following report" & @CRLF & $aSpreadsheets[0] & "\" & $aSpreadsheets[$i] & @CRLF & "Click OK to skip and continue, or Cancel to stop") = 2 Then Return SetError(1)
EndIf
For $i = 1 To UBound($aSpreadsheets) - 1
    ConsoleWrite("Excel file:  " & $aSpreadsheets[0] & "\" & $aSpreadsheets[$i] & @CRLF)
    $oExcelBook = _Excel_BookOpen($oExcel, $aSpreadsheets[0] & "\" & $aSpreadsheets[$i], True, False)
    If $i = 1 Then
        $aData = _Excel_RangeRead($oExcelBook, Default, $oExcelBook.ActiveSheet.Usedrange.Columns($Columns), 1, True)
        _ArrayDelete($aData, 0) ;Removes the header row
    Else
        $aDataB = _Excel_RangeRead($oExcelBook, Default, $oExcelBook.ActiveSheet.Usedrange.Columns($Columns), 1, True)
        _ArrayDelete($aDataB, 0) ;Removes the header row
    EndIf
    If @error Then
        MsgBox(0, "Excel Error", "Error = " & @error & @CRLF & "Extended = " & @extended)
        _Excel_Close($oExcel, False)
        SetError(1)
        Return
    EndIf
    _Excel_BookClose($oExcelBook, False)
    If $i > 1 Then _ArrayConcatenate($aData, $aDataB)
Next
_Excel_Close($oExcel, False)

 

[BigDaddyO]

just finished trying that and I also moved that one error trap up where it should have been but i'm still getting the same error on random spreadsheets.

expandcollapse popup

Func _GetDataFromSpreadsheets($aSpreadsheets, $Columns)
    GUICtrlSetData($hPickListProgress, 1)
    $oExcel = _Excel_Open(False, False, False)
    If @error Then
        MsgBox(0, "Error", "Unable to open Excel")
        Return SetError(1)
    EndIf

    For $i = 1 To UBound($aSpreadsheets) - 1

        ConsoleWrite("Excel file:  " & $aSpreadsheets[0] & "\" & $aSpreadsheets[$i] & @CRLF)
        $oExcelBook = _Excel_BookOpen($oExcel, $aSpreadsheets[0] & "\" & $aSpreadsheets[$i], True)
        If @error Then
            MsgBox(0, "Excel Error", "Error = " & @error & @CRLF & "Extended = " & @extended)
            _Excel_BookClose($oExcelBook, False)
            _Excel_Close($oExcel, False)
            SetError(1)
            Return
        EndIf

        if $i = 1 Then
            $aData = _Excel_RangeRead($oExcelBook, Default, $oExcelBook.ActiveSheet.Usedrange.Columns($Columns), 1, True)
            _ArrayDelete($aData, 0) ;Removes the header row
        Else
            $aDataB = _Excel_RangeRead($oExcelBook, Default, $oExcelBook.ActiveSheet.Usedrange.Columns($Columns), 1, True)
            _ArrayDelete($aDataB, 0) ;Removes the header row
        EndIf

        _Excel_BookClose($oExcelBook, False)
        If $i > 1 Then _ArrayConcatenate($aData, $aDataB)

        $iPct = Round(100 * ($i / UBound($aSpreadsheets) ), 0)                                              ;Calculate the current percentage complete for this file rounded to the nearest 10 "-1"
        GUICtrlSetData($hPickListProgress, $iPct)

    Next

    _Excel_Close($oExcel, False)

    ConsoleWrite("Total records = " & UBound($aData) & @CRLF)
    Return $aData
EndFunc

 

Edited August 17, 2015 by BigDaddyO

[BigDaddyO]

In an attempt to look closer at the random spreadsheets that were failing I switched it to display the spreadsheets while running

$oExcel = _Excel_Open(True, False, True)

Using it like this, it processed all spreadsheets.  I tried (False, False, True) and (True, False, False) and got the error message with those.

Not sure why (True, False, True) is the magic scenario but I can't exactly leave it like that.

 

Thanks,

Mike

[BigDaddyO]

Got it working:

it appears to be the $bUpdateLinks option.  if I set it to 0 it works.  not sure why it doesn't like Default "sometimes".

$oExcelBook = _Excel_BookOpen($oExcel, $aSpreadsheets[0] & "\" & $aSpreadsheets[$i], True, False, Default, Default, 0)

None of these spreadsheets have any links either.

 

Mike

[water]

Strange, but great you could solve your problem