ObjGet is slow when using index parameter

[water]

Lets say I open 12 Excel workbooks and use _Excel_BookList to get a list of this open workbooks. It can take the function up to 45 seconds to return the result.
Each call to ObjGet takes longer then the previous one while incrementing the index:

Quote

--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
ObjGet for instance 1: 382.251450162039
ObjGet for instance 2: 777.418685971442
ObjGet for instance 3: 1019.9805791774
ObjGet for instance 4: 1438.43658964894
ObjGet for instance 5: 1813.44021612065
ObjGet for instance 6: 2252.33544777966
ObjGet for instance 7: 2624.21945529542
ObjGet for instance 8: 3459.48036500224
ObjGet for instance 9: 3805.80542656693
ObjGet for instance 10: 4110.24388662199
ObjGet for instance 11: 5074.12806820839
ObjGet for instance 12: 5631.12553735778
Runtime for _Excel_BookList: 37838.0588392901
+>14:41:47 AutoIt3.exe ended.rc:0
+>14:41:47 AutoIt3Wrapper Finished.
>Exit code: 0    Time: 42.32

I searched the forum but only found some threads from 2008 discussing the processing of the ROT (Running Objects Table).
Does someone have an idea how to enhance this function (some miraculous WinAPI calls ...)?

expandcollapse popup

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Global $iTimer = TimerInit()
Global $aWorkbooks = _Excel_BookListEX()
ConsoleWrite("Runtime for _Excel_BookList: " & TimerDiff($iTimer) & @CRLF)

Func _Excel_BookListEX($oExcel = Default)
    ; Error handler, automatic cleanup at end of function
    Global $oError = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc_RT")
    #forceref $oError
    Local $aBooks[1][3], $iIndex = 0
    If IsObj($oExcel) Then
        If ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, 0, 0)
        Local $iTemp = $oExcel.Workbooks.Count
        ReDim $aBooks[$iTemp][3]
        For $iIndex = 0 To $iTemp - 1
            $aBooks[$iIndex][0] = $oExcel.Workbooks($iIndex + 1)
            $aBooks[$iIndex][1] = $oExcel.Workbooks($iIndex + 1).Name
            $aBooks[$iIndex][2] = $oExcel.Workbooks($iIndex + 1).Path
        Next
    Else
        If $oExcel <> Default Then Return SetError(1, 0, 0)
        Local $oWorkbook, $sCLSID_Workbook = "{00020819-0000-0000-C000-000000000046}"
        While True
            $iTimerList = TimerInit()
            $oWorkbook = ObjGet("", $sCLSID_Workbook, $iIndex + 1)
            If @error Then ExitLoop
            ConsoleWrite("ObjGet for instance " & $iIndex + 1 & ": " & TimerDiff($iTimerList) & @CRLF)
            ReDim $aBooks[$iIndex + 1][3]
            $aBooks[$iIndex][0] = $oWorkbook
            $aBooks[$iIndex][1] = $oWorkbook.Name
            $aBooks[$iIndex][2] = $oWorkbook.Path
            $iIndex += 1
            $oWorkbook = 0
        WEnd
    EndIf
    Return $aBooks
EndFunc   ;==>_Excel_BookListEX

 

[Subz]

Probably not what you're after but I've always just use ObjGet("...\ExcelPath.xlsx") for example:

#include <Excel.au3>

Local $aWorkBook, $aWorkBooks[] = [ _
@ScriptDir & "\Test1.xlsx", _
@ScriptDir & "\Test2.xlsx", _
@ScriptDir & "\Test3.xlsx", _
@ScriptDir & "\Test4.xlsx", _
@ScriptDir & "\Test5.xlsx", _
@ScriptDir & "\Test6.xlsx", _
@ScriptDir & "\Test7.xlsx", _
@ScriptDir & "\Test8.xlsx", _
@ScriptDir & "\Test9.xlsx", _
@ScriptDir & "\Test10.xlsx", _
@ScriptDir & "\Test11.xlsx", _
@ScriptDir & "\Test12.xlsx", _
@ScriptDir & "\Test13.xlsx", _
@ScriptDir & "\Test14.xlsx"]

For $i = 0 To UBound($aWorkBooks) - 1
    $aWorkBooks[$i] = ObjGet($aWorkBooks[$i])
    $aWorkBook = $aWorkBooks[$i]
    ConsoleWrite("Workbook Name: " & $aWorkBook.Name & @CRLF)
    ConsoleWrite("Workbook Path: " & $aWorkBook.Path & @CRLF)
Next

 

[water]

Thanks for your code snippet.
That could be a solution for the "FilePath" mode.
But for "FileName" and "Title" the current solution is still too slow when there are multiple open workbooks to check.

[Danyfirex]

Hello you could do this: (tested with Excel 2013)

 

#include <Array.au3>
#include <WinAPI.au3>

Local $aBooks[0][3]
Local Const $OBJID_NATIVEOM = 0xFFFFFFF0
Local $hExcelControl = ControlGetHandle("[CLASS:XLMAIN]", "", "EXCEL71")
Local $tGUID = _WinAPI_GUIDFromString('{00020400-0000-0000-C000-000000000046}') ;IID_IDispatch
Local $pGUID = DllStructGetPtr($tGUID)
Local $aCall = DllCall("Oleacc.dll", "long", "AccessibleObjectFromWindow", "hwnd", $hExcelControl, "dword", $OBJID_NATIVEOM, "ptr", $pGUID, "idispatch*", 0)
If $aCall[0] = $S_OK Then
    Local $oExcel = $aCall[4].Application
    ReDim $aBooks[$oExcel.Workbooks.Count][3]
    Local $iIndex = 0
    For $oWorkbook In $oExcel.Workbooks
        $aBooks[$iIndex][0] = $oWorkbook
        $aBooks[$iIndex][1] = $oWorkbook.Name
        $aBooks[$iIndex][2] = $oWorkbook.Path
        $iIndex += 1
    Next
    _ArrayDisplay($aBooks)
EndIf

Tested with 100 workbooks. It takes about 500 milliseconds for listing in my machine.

 

Saludos

[water]

That's perfect!

Additional question: How would you handle multiple instances of the Excel application? With the code you posted you just get the last active application.
Thanks for taking the time to speed up this Excel UDF functions

[Danyfirex]

You're right, here is a mod.

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>
#include <WinAPI.au3>


Local $oExcel1 = _Excel_Open()
Local $oExcel2 = _Excel_Open(True, False, True, True, True)
Local $oExcel3 = _Excel_Open(True, False, True, True, True)

For $i = 1 To 5
    _Excel_BookNew($oExcel1, 1)
    _Excel_BookNew($oExcel2, 1)
    _Excel_BookNew($oExcel3, 1)
Next

Local $hTimer = TimerInit()
Local $aWorkBooks = _Excel_BookList()
_ArrayDisplay($aWorkBooks, " _Excel_BookList Time: " & TimerDiff($hTimer))

$hTimer = TimerInit()
Local $aWorkBooks = _Excel_BookListEx()
_ArrayDisplay($aWorkBooks, " _Excel_BookListEx Time: " & TimerDiff($hTimer))


_Excel_Close($oExcel1)
_Excel_Close($oExcel2)
_Excel_Close($oExcel3)

Func _Excel_BookListEx($oExcel = Default)
    Local Const $OBJID_NATIVEOM = 0xFFFFFFF0
    Local $aBooks[0][3], $iIndex = 0
    If IsObj($oExcel) Then
        If ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, 0, 0)
        Local $iTemp = $oExcel.Workbooks.Count
        ReDim $aBooks[$iTemp][3]
        For $iIndex = 0 To $iTemp - 1
            $aBooks[$iIndex][0] = $oExcel.Workbooks($iIndex + 1)
            $aBooks[$iIndex][1] = $oExcel.Workbooks($iIndex + 1).Name
            $aBooks[$iIndex][2] = $oExcel.Workbooks($iIndex + 1).Path
        Next
    Else
        If $oExcel <> Default Then Return SetError(1, 0, 0)
        Local $aExcelInstances = __GetExcelInstances()
        Local $tGUID = _WinAPI_GUIDFromString('{00020400-0000-0000-C000-000000000046}') ;IID_IDispatch
        Local $pGUID = DllStructGetPtr($tGUID)
        Local $hExcelControl = 0
        Local $aCall = 0
        For $i = 0 To UBound($aExcelInstances) - 1
            $hExcelControl = ControlGetHandle($aExcelInstances[$i], "", "EXCEL71")
            $aCall = DllCall("Oleacc.dll", "long", "AccessibleObjectFromWindow", "hwnd", $hExcelControl, "dword", $OBJID_NATIVEOM, "ptr", $pGUID, "idispatch*", 0)
            If $aCall[0] = $S_OK Then
                $oExcel = $aCall[4].Application
                ReDim $aBooks[UBound($aBooks) + $oExcel.Workbooks.Count][3]
                For $oWorkbook In $oExcel.Workbooks
                    $aBooks[$iIndex][0] = $oWorkbook
                    $aBooks[$iIndex][1] = $oWorkbook.Name
                    $aBooks[$iIndex][2] = $oWorkbook.Path
                    $iIndex += 1
                Next
            EndIf
        Next
    EndIf
    Return ($iIndex > 0 ? SetError(0, $iIndex, $aBooks) : SetError(1, 0, $aBooks))
EndFunc   ;==>_Excel_BookListEx

Func __GetExcelInstances()
    Local $aIntances[0]
    Local $aWindowIntances[0]
    Local $aWindows = WinList("[CLASS:XLMAIN]")
    If IsArray($aWindows) Then
        Local $iFind = 0
        Local $iPID = 0
        For $i = 1 To $aWindows[0][0]
            $iPID = WinGetProcess($aWindows[$i][1])
            $iFind = _ArraySearch($aIntances, $iPID)
            If $iFind = -1 Then
                _ArrayAdd($aIntances, $iPID)
                _ArrayAdd($aWindowIntances, $aWindows[$i][1])
            EndIf
        Next
    EndIf
    Return $aWindowIntances
EndFunc   ;==>__GetExcelInstances

Saludos

Edited July 13, 2018 by Danyfirex

[water]

Thanks for the extended version
Will test as soon as I can reach a Windows PC

[water]

BTW: Does anyone know why looping through the ROT by ObjGet is so slow?
Could this be a bug?

[Danyfirex]

I'm no sure how is designed ObjGet internally but probably a minor bug because I did a test using ROT which is fast enough.

Test Script:

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>
#include <WinAPI.au3>



Global Const $sIID_IEnumMoniker = '{00000102-0000-0000-c000-000000000046}'
Global Const $sTagIEnumMoniker = "Next hresult(dword;ptr*;dword*);" & _
        "Skip hresult(dword);" & _
        "Reset hresult();" & _
        "Clone hresult(ptr*);"

Global Const $sIID_IMoniker = '{0000000f-0000-0000-C000-000000000046}'
Global Const $sTagIMoniker = "GetClassID hresult( clsid )" & _
        "IsDirty hresult(  );" & _
        "Load hresult( ptr );" & _
        "Save hresult( ptr, bool );" & _
        "GetSizeMax hresult( uint64 );" & _
        "BindToObject hresult( ptr;ptr;clsid;idispatch*);" & _
        "BindToStorage hresult( ptr;ptr;clsid;ptr*);" & _
        "Reduce hresult( ptr;dword;ptr*;ptr*);" & _
        "ComposeWith hresult( ptr;bool;ptr*);" & _
        "Enum hresult( bool;ptr*);" & _
        "IsEqual hresult( ptr);" & _
        "Hash hresult( dword*);" & _
        "IsRunning hresult( ptr;ptr;ptr);" & _
        "GetTimeOfLastChange hresult( ptr;ptr;int64*);" & _
        "Inverse hresult( ptr*);" & _
        "CommonPrefixWith hresult( ptr;ptr*);" & _
        "RelativePathTo hresult( ptr;ptr*);" & _
        "GetDisplayName hresult( ptr;ptr;wstr*);" & _
        "ParseDisplayName hresult( ptr;ptr;wstr;ulong*;ptr*);" & _
        "IsSystemMoniker hresult( dword*);"


Global Const $IID_IRunningObjectTable = "{00000010-0000-0000-C000-000000000046}"
Global Const $sTagIRunningObjectTable = "Register HRESULT(DWORD;PTR;PTR;DWORD*);" & _
        "Revoke HRESULT(DWORD);" & _
        "IsRunning HRESULT(PTR*);" & _
        "GetObject HRESULT(PTR;PTR*);" & _
        "NoteChangeTime HRESULT(DWORD;PTR*);" & _
        "GetTimeOfLastChange HRESULT(PTR*;PTR*);" & _
        "EnumRunning HRESULT(PTR*);"

Global Const $IID_IDispatch = "{00020400-0000-0000-C000-000000000046}"


Local $oExcel1 = _Excel_Open()
Local $oExcel2 = _Excel_Open(True, False, True, True, True)
Local $oExcel3 = _Excel_Open(True, False, True, True, True)

For $i = 1 To 5
    _Excel_BookNew($oExcel1, 1)
    _Excel_BookNew($oExcel2, 1)
    _Excel_BookNew($oExcel3, 1)
Next


Local $hTimer = TimerInit()
Local $aWorkBooks = _Excel_BookListEx()
_ArrayDisplay($aWorkBooks, " _Excel_BookListEx Time: " & TimerDiff($hTimer))

_Excel_Close($oExcel1)
_Excel_Close($oExcel2)
_Excel_Close($oExcel3)


Func _Excel_BookListEx($oExcel = Default)
    Local $aBooks[0][3], $iIndex = 0
    If IsObj($oExcel) Then
        If ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, 0, 0)
        Local $iTemp = $oExcel.Workbooks.Count
        ReDim $aBooks[$iTemp][3]
        For $iIndex = 0 To $iTemp - 1
            $aBooks[$iIndex][0] = $oExcel.Workbooks($iIndex + 1)
            $aBooks[$iIndex][1] = $oExcel.Workbooks($iIndex + 1).Name
            $aBooks[$iIndex][2] = $oExcel.Workbooks($iIndex + 1).Path
        Next
    Else
        If $oExcel <> Default Then Return SetError(1, 0, 0)
        Local $aCall = DllCall("Ole32.dll", "LONG", "GetRunningObjectTable", "DWORD", 0, "PTR*", 0)
        Local $pIRunningObjectTable = $aCall[2]
        Local $oRunningObjectTable = ObjCreateInterface($pIRunningObjectTable, $IID_IRunningObjectTable, $sTagIRunningObjectTable)
        Local $pIEnumMoniker = 0
        $oRunningObjectTable.EnumRunning($pIEnumMoniker)
        Local $oEnumMoniker = ObjCreateInterface($pIEnumMoniker, $sIID_IEnumMoniker, $sTagIEnumMoniker)
        Local $pMoniker = 0
        Local $iFetched = 0
        Local $oMoniker = 0
        Local $pCTX = 0
        Local $oWorkBook = 0
        $aCall = DllCall("Ole32.dll", "long", "CreateBindCtx", "dword", 0, "ptr*", 0)
        $pCTX = $aCall[2]
        While $oEnumMoniker.Next(1, $pMoniker, $iFetched) = 0
            $oMoniker = ObjCreateInterface($pMoniker, $sIID_IMoniker, $sTagIMoniker)
            $oMoniker.BindToObject($pCTX, 0, $IID_IDispatch, $oWorkBook)
            If IsObj($oWorkBook) Then
                ReDim $aBooks[UBound($aBooks)+1][3]
                $aBooks[$iIndex][0] = $oWorkBook
                $aBooks[$iIndex][1] = $oWorkBook.Name
                $aBooks[$iIndex][2] = $oWorkBook.Path
                $iIndex+=1
            EndIf
        WEnd
    EndIf
    Return ($iIndex > 0 ? SetError(0, $iIndex, $aBooks) : SetError(1, 0, $aBooks))
EndFunc   ;==>_Excel_BookListEx

Saludos

Edited July 14, 2018 by Danyfirex

[water]

Fantastic!
Takes 63ms to process