Fixed versions of _Excel_RangeFind, _Excel_BookOpen and _Word_DocSaveAs

[water 2,720]

After AutoIt 3.3.12.0 the handling of COM errors has been "fixed".
Now nested COM operations crash the script in case of an error even when there is a COM error handler.

Edit: All 3 problems have been fixed in AutoIt 3.3.14.3.

Here you find a fixed version of function _Excel_RangeFind. I simply removed all nestings and added the missing COM error hander:

Spoiler

expandcollapse popup

Func _Excel_RangeFind($oWorkbook, $sSearch, $vRange = Default, $iLooke = Default, $iLookAt = Default, $bMatchcase = Default)
    ; Error handler, automatic cleanup at end of function
    Local $oError = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc")
    #forceref $oError
    If Not IsObj($oWorkbook) Or ObjName($oWorkbook, 1) <> "_Workbook" Then Return SetError(1, 0, 0)
    If StringStripWS($sSearch, 3) = "" Then Return SetError(2, 0, 0)
    If $iLooke = Default Then $iLooke = $xlValues
    If $iLookAt = Default Then $iLookAt = $xlPart
    If $bMatchcase = Default Then $bMatchcase = False
    Local $oMatch, $sFirst = "", $bSearchWorkbook = False, $oSheet
    If $vRange = Default Then
        $bSearchWorkbook = True
        $oSheet = $oWorkbook.Sheets(1)
        $vRange = $oSheet.UsedRange
    ElseIf IsString($vRange) Then
        $vRange = $oWorkbook.Activesheet.Range($vRange)
        If @error Then Return SetError(3, @error, 0)
    EndIf
    Local $aResult[100][6], $iIndex = 0, $iIndexSheets = 1, $oTemp
    While 1
        $oMatch = $vRange.Find($sSearch, Default, $iLooke, $iLookAt, Default, Default, $bMatchcase)
        If @error Then Return SetError(4, @error, 0)
        If IsObj($oMatch) Then
            $sFirst = $oMatch.Address
            While 1
                $aResult[$iIndex][0] = $oMatch.Worksheet.Name
                $oTemp = $oMatch.Name
                If Not @error Then $aResult[$iIndex][1] = $oTemp.Name
                $aResult[$iIndex][2] = $oMatch.Address
                $aResult[$iIndex][3] = $oMatch.Value
                $aResult[$iIndex][4] = $oMatch.Formula
                $oTemp = $oMatch.Comment
                If IsObj($oTemp) Then $aResult[$iIndex][5] = $oTemp.Text
                $iIndex = $iIndex + 1
                If Mod($iIndex, 100) = 0 Then ReDim $aResult[UBound($aResult, 1) + 100][6]
                $oMatch = $vRange.Findnext($oMatch)
                If Not IsObj($oMatch) Or $sFirst = $oMatch.Address Then ExitLoop
            WEnd
        EndIf
        If Not $bSearchWorkbook Then ExitLoop
        $iIndexSheets = $iIndexSheets + 1
        $sFirst = ""
        $oSheet = $oWorkbook.Sheets($iIndexSheets)
        If @error Then ExitLoop
        $vRange = $oSheet.UsedRange
    WEnd
    ReDim $aResult[$iIndex][6]
    Return $aResult
EndFunc   ;==>_Excel_RangeFind

 

 

Another function that needed some modification is _Excel_BookOpen. It crashed when a workbook was opened using _Excel_BookOpen with parameter $bVisible = True, saved and then reopened. The following modification solves the problem:

Spoiler

Func _Excel_BookOpen($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default, $bUpdateLinks = Default)
    ; Error handler, automatic cleanup at end of function
    Local $oError = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc")
    #forceref $oError
    If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0)
    If Not FileExists($sFilePath) Then Return SetError(2, 0, 0)
    If $bReadOnly = Default Then $bReadOnly = False
    If $bVisible = Default Then $bVisible = True
    Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, $bUpdateLinks, $bReadOnly, Default, $sPassword, $sWritePassword)
    If @error Then Return SetError(3, @error, 0)
    Local $oWindow = $oExcel.Windows($oWorkbook.Name) ; <== Modified
    If IsObj($oWindow) Then $oWindow.Visible = $bVisible ; <== Modified
    ; If a read-write workbook was opened read-only then set @extended = 1
    If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(0, 1, $oWorkbook)
    Return $oWorkbook
EndFunc   ;==>_Excel_BookOpen

 

 

2017/06/19:
Another function that needed some modification is _Word_DocSaveAs. It doesn't work with Word 2013 or later as MS felt the urge to change the name of the save method from SaveAs to SaveAs2.
The following modification solves the problem:

Spoiler

Func _Word_DocSaveAs($oDoc, $sFileName = Default, $iFileFormat = Default, $bReadOnlyRecommended = Default, $bAddToRecentFiles = Default, $sPassword = Default, $sWritePassword = Default)
    ; Error handler, automatic cleanup at end of function
    Local $oError = ObjEvent("AutoIt.Error", "__Word_COMErrFunc")
    #forceref $oError

    If $sFileName = Default Then $sFileName = ""
    If $iFileFormat = Default Then $iFileFormat = $WdFormatDocument
    If $bReadOnlyRecommended = Default Then $bReadOnlyRecommended = False
    If $bAddToRecentFiles = Default Then $bAddToRecentFiles = 0
    If $sPassword = Default Then $sPassword = ""
    If $sWritePassword = Default Then $sWritePassword = ""
    If Not IsObj($oDoc) Then Return SetError(1, 0, 0)
    $oDoc.SaveAs2($sFileName, $iFileFormat, False, $sPassword, $bAddToRecentFiles, $sWritePassword, $bReadOnlyRecommended) ; Try to save for >= Word 2010
    If @error = 0x80020006 Then $oDoc.SaveAs($sFileName, $iFileFormat, False, $sPassword, $bAddToRecentFiles, $sWritePassword, $bReadOnlyRecommended) ; COM error "Unknown Name" hence save for <= Word 2007
    If @error Then Return SetError(2, @error, 0)
    Return 1
EndFunc   ;==>_Word_DocSaveAs

 

 

Please tell me if you still have problems with this or any other function from the Excel or Word UDF!

Edited February 2, 2018 by water

[SorryButImaNewbie 36]

Hello water!

Thank you for the fix! (now that I finished _Excel_RangeRead everything I needed ,just pointing out Murphy, I'm really glad that I can make my code more efficient now)

I just want to ask, do I need to update my autoit for this? (3.3.14.1 right now) I'm just not sure how updateing is goin on around here, what should I do? 

Thank you again! This part was a painfull little error for everyone who worked on excel I think

[water 2,720]

If you are now running a version > 3.3.12.0 then there is no need to update.

[water 2,720]

NB:
The above fixes for _Excel_RangeFind, _Excel_BookOpen and _Word_DocSaveAs have been added to the code repository and hence will be part of the next beta version 3.3.15.1.

[Yanccery 0]

Is there any way for us ?

[water 2,720]

What exactly do you mean?

[water 2,720]

The fixes from post #1 are no longer needed if running AutoIt 3.3.14.3 or later

[mLipok 1,389]

On 2.02.2018 at 11:59 AM, water said:

The fixes from post #1 are no longer needed if running AutoIt 3.3.14.3 or later

So should this topic be sticky anymore ?

 

[water 2,720]

I think it is sensible to un-stick the thread as soon as the next production version of AutoIt (3.3.16.0) has been released.
3.3.14.3 is a interim release with a lot of backported fixes.
As the thread is marked as popular I think it is still read by a lot of users.

[rcmaehl 97]

On 2/5/2018 at 4:37 AM, water said:

I think it is sensible to un-stick the thread as soon as the next production version of AutoIt (3.3.16.0) has been released.
3.3.14.3 is a interim release with a lot of backported fixes.
As the thread is marked as popular I think it is still read by a lot of users.

I wonder when that will be