Strange Failures with Excel and COM - fixed: use 64 bit compile.

[RichardL]

I was using a program that gets data from the first 2 columns of an Excel sheet (then Sends the data to another prog).  Realised that it gives up after about 800 lines.  I've used these methods lots of times before, never had anything like this.  All the lines of code work correctly lots of times, then it fails.  The data in the test sheet is the same on all lines.

Started on Server 2008 with Office 2010. Moved to W7 and Office 2003, fails there as well. Both AutoIt 3.3.14.2

I've reduced the program to the minimum that fails.  (Took hours, swapping things in and out trying to pin it down.)

The failure is usually "Bad variable type" on the Excel read.

It's better if the program does fewer Excel reads, if read column 1 only it usually reads all 985 lines.  If I add writes to Excel it fails earlier.

More strangeness:  If I remove he StringFormat on line 97 or the StringReplace on line 90 the problem is gone.

The test Excel sheet can be simple 1000 lines of 2 columns:    Hello.abc, No

 

 

expandcollapse popup

Opt("MustDeclareVars", 1)

Func _ErrFunc($oError)
    Local $sMsg
    ;ConsoleWrite( _
        $sMsg = _
            "err.number        : " & @TAB & $oError.number         & @CRLF & _
            "err.windescription: " & @TAB & $oError.windescription & @CRLF & _
            "err.description   : " & @TAB & $oError.description    & @CRLF & _
            "err.source        : " & @TAB & $oError.source         & @CRLF & _
            "err.lastdllerror  : " & @TAB & $oError.lastdllerror   & @CRLF & _
            "err.scriptline    : " & @TAB & $oError.scriptline     & @CRLF & _
            "err.retcode       : " & @TAB & $oError.retcode        & @CRLF & _
            @CRLF
            ConsoleWrite($sMsg)
            MsgBox(0, "Error", $sMsg)
            Exit
EndFunc

Local $sFile_xls

$sFile_xls = @ScriptDir & "\" & "R_Test3.xls"

Local $sTmp
Local $iRow
Local $iCol
Local $sMsg

Local $oExcel
Local $oWbook
Local $oSheet

Local $oErrorHandler

$oErrorHandler = ObjEvent("AutoIt.Error", "_ErrFunc")

$sFile_xls = @ScriptDir & "\" & "R_Test4.xls"

Local $sMmNm
Local $iNofSheets
Local $iSheetNo
Local $sMsg


If 0 Then
    ; (good but has been seen not to work)
    $oWbook = ObjGet($sFile_xls)
    $oExcel = $oWbook.Parent
Else
    ; This sometimes makes 1 excel.exe process for each run
    $oExcel = ObjCreate("Excel.Application")
    If IsObj($oExcel) Then
    Else
        $sMsg = "Failed to open Excel."
        MsgBox(0, Default, $sMsg)
        Exit
    EndIf
    $oWbook = $oExcel.WorkBooks.Open($sFile_xls)
    If IsObj($oWbook) Then
    Else
        $sMsg = "Failed to open workbook"
        MsgBox(0, Default, $sMsg)
        Exit
    EndIf
EndIf
;$oExcel.Visible = True
Sleep(500)

$oSheet = $oWbook.Worksheets("Sheet1")


Local $bRqd = True
Local $iCol_MimName = 1
Local $sName

$oSheet = $oWbook.Worksheets(1)

$iRow = 5
$bRqd = False
While 1
        For $iCol = 1 To 4
            $sTmp = $oSheet.Cells($iRow, $iCol).Value
            If $iCol = 1 And    $sTmp = "" Then Exit
            Switch $iCol
                Case 1
                    $sName = StringReplace($sTmp, ".xyz", ".ddd")
                Case 2
                    $bRqd = StringLower(StringLeft($sTmp, 1)) <> "n"
            EndSwitch
            $sMmNm = StringReplace($sName, ".ddd", "")
            ;$sMmNm = $sName
        Next
        ;If StringStripWS($sTmp, 8)  = "" Then ExitLoop
        $sMsg = StringFormat("R %4d  L%3d  T %-20s", $iRow, StringLen($sMmNm), $sMmNm & " " & $sTmp)
        ConsoleWrite($sMsg  & @CRLF)

        $sMsg = StringFormat("%4d %s\n", $iRow, $sName)
        ;TrayTip("Mimic", $sMsg, 1)
    $iRow += 1
WEnd

 

Edited January 26, 2017 by RichardL
put fix in title

[alien4u]

Before you do your StringReplace or StringFormat try:
VarGetType()

To double check if your are trying to make an String operation over a variable which for some reason is not an String.

Regards
Alien.

[water]

Why don't you use the Excel UDF that comes with AutoIt?

[RichardL]

@alien4u - I have the idea that AutoIt converts to string as needed, but I'll try VarGetType and force it with String().

@ Water, Because I became familiar with com+Excel using VB/VBA/VBS then started using AutoIt.  Apart from the Workbook open my prog has only 1 line that uses Excel.   Never had anything like this before but not sure if ever used 1000 reads.

Edited January 19, 2017 by RichardL
typo

[water]

The Excel UDF shouldn't have any problems with the size of your workbook.
The UDF uses COM as well and handles COM errors fine and sets @error and @extended as described in the help file.

[FrancescoDiMuro]

@RichardL
If you need some help with Excel UDF, I'd be very happy to help you
 

[alien4u]

5 hours ago, RichardL said:

@alien4u - I have the idea that AutoIt converts to string as needed, but I'll try VarGetType and force it with String().

@ Water, Because I became familiar with com+Excel using VB/VBA/VBS then started using AutoIt.  Apart from the Workbook open my prog has only 1 line that uses Excel.   Never had anything like this before but not sure if ever used 1000 reads.

@RichardL Weird things happen all the time, so who knows, there is no harm in to double check the var type.

In the other hand as @water point out you can use the Excel UDF which is a very good UDF developed and maintained by him.

Regards
Alien.

[RichardL]

Fixed.  Tried the program on a WXP PC, runs perfectly. On W7 changed the compile to 64 bit and it's fine.

[water]

If you run the 64 bit version of Office then you need to compile the script for 64 bit as well.