RichardL

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

9 posts in this topic

#1 ·  Posted (edited)

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

 

 

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 by RichardL
put fix in title

Share this post


Link to post
Share on other sites



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.

Share this post


Link to post
Share on other sites

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


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

#4 ·  Posted (edited)

@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 by RichardL
typo

Share this post


Link to post
Share on other sites

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.


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

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

 


Click here to see my signature:

Spoiler

I will always thank you for the time you spent for me.
I'm here to ask, and from your response, I'd like to learn.
By my knowledge, I can help someone else, and "that someone" could help in turn another, and so on.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


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