BigDaddyO

_Excel_BookOpen crashing random spreadsheets

6 posts in this topic

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


Share this post


Link to post
Share on other sites



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)

 


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

#3 ·  Posted (edited)

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.

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 by BigDaddyO

Share this post


Link to post
Share on other sites

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


Share this post


Link to post
Share on other sites

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


Share this post


Link to post
Share on other sites

Strange, but great you could solve your problem ;)


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

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