none
UiAutomation on VBA(Excel Macro??) RRS feed

  • Question

  • I found a way to use UiAutomation with VBA, i'm not having a problem with normal use like locating the UIelements using tree or conditions.
    But now i faced some problem with UiAutomation that i really need some help

    First , I can't use UiElement.CurrentNativeWindowHandle
    I can't get the hwnd of the element. It says something like the function is marked and restricted.

    Second, I can't use CUIAutomation.ElementFromHandle or CUIAutomation.ElementFromPoint.

    Third.. I can't use anything with relates to event handler..

    The question is.. is there someone that can help me resolve this issues?
    Or just point me to a Guide using UiAutomationCore with VBA?

    Tuesday, April 21, 2015 1:09 PM

All replies

  • I doubt anybody is using this with VBA.  Why do you want to use VBA rather than C# or VB.Net?
    Tuesday, April 21, 2015 1:25 PM
  • Thanks for the fast reply..
    Actually we're working on an environment where i can't install anything other than Microsoft Office
    We even use Microsoft 2003 on it actually

    That's why i need a way to completely integrate(?) or how should i phrase this, use it to it's outmost proficiency.

    Tuesday, April 21, 2015 1:41 PM
  • I am confused.  I have never used UI Automation.  I thought it was used to automate UI testing of .Net apps.  Someone must have installed a .Net app on the PC so why can't you install another .Net app?  This is rather limiting.   
    Tuesday, April 21, 2015 3:41 PM
  • No actually UiAutomationCore.Dll can be found on any Microsoft OS..
    Which is why i can use UiAutomation even though i don't have .Net app..
    And i'm using UiAutomation in automation, that automatically moves an application without human intervention.
    Wednesday, April 22, 2015 8:22 AM
  • I tried to add a reference to it Excel 2010 and it gave me an error.  When I added a reference from C# it worked perfectly.  I think it is only designed to work with .Net.  The documentation says it works with WPF apps.  Again, I am no expert on this.

    Wednesday, April 22, 2015 1:55 PM
  • There's two way to reference it on microsoft excel
    one is to reference with through coding

    and the other one is that to copy the UiAutomationCore.dll from system32 to you my documents then manually add it to reference. please try :)

    Thursday, April 23, 2015 1:04 PM
  • Didn't work.  Can't get a reference to it.
    Thursday, April 23, 2015 8:31 PM
  • Although little late below is with Excel 2013 on a Windows 10 environment 64 bits

    1. UIAutomationCore.Dll is also in C:\Windows\SysWOW64 and you can probably reference that one from excel
    2. Copy UIAutomationCore.DLL from c:\windows\system32 to my documents seems also to work (although I do not understand why) I think then implicitly it loads the one from 1
    3. Add reference from code (also here I referense system32 and not syswow64 but I get syswow64 reference)
      make sure reference to microsoft visual basic for applications extensibility 5.3 reference is on
    Sub AddReference()
        Dim VBAEditor As VBIDE.VBE
        Dim vbProj As VBIDE.VBProject
        Dim chkRef As VBIDE.Reference
        Dim BoolExists As Boolean
    
        Set VBAEditor = Application.VBE
        Set vbProj = ActiveWorkbook.VBProject
    
        '~~> Check if "Microsoft VBScript Regular Expressions 5.5" is already added
        For Each chkRef In vbProj.References
            If chkRef.Name = "UIAutomationClient" Then
                BoolExists = True
                GoTo CleanUp
            End If
        Next
    
        vbProj.References.AddFromFile "C:\WINDOWS\system32\uiautomationcore.dll"
    
    CleanUp:
        If BoolExists = True Then
            MsgBox "Reference already exists"
        Else
            MsgBox "Reference Added Successfully"
        End If
    
        Set vbProj = Nothing
        Set VBAEditor = Nothing
    End Sub


    So much power in VBA and Excel for automating stuff

    Sub demoUIAutomation()
        Dim oUIAutomation As New CUIAutomation8
        Dim oUIADesktop As IUIAutomationElement
        Dim allChilds As IUIAutomationElementArray
        
        Set oUIADesktop = oUIAutomation.GetRootElement
        
        Debug.Print oUIADesktop.CurrentName
        
        Set allChilds = oUIADesktop.FindAll(TreeScope_Children, oUIAutomation.CreateTrueCondition)
        
        For i = 0 To allChilds.Length - 1
            Debug.Print i & ":=" & allChilds.GetElement(i).CurrentName & vbTab & allChilds.GetElement(i).CurrentClassName
        Next
            
    End Sub
    


    • Edited by junkew Tuesday, September 6, 2016 7:08 PM
    Tuesday, September 6, 2016 6:47 PM
  • Demo treewalker VBA Excel 2013 windows 10 64 bits

    Sub demoUIAutomationTreewalker()
        Dim oUIAutomation As New CUIAutomation8
        Dim oUIADesktop As IUIAutomationElement
        Dim allChilds As IUIAutomationElementArray
        Dim oUIElement As IUIAutomationElement
        Dim oTW As IUIAutomationTreeWalker
        
        Set oUIADesktop = oUIAutomation.GetRootElement
        Debug.Print oUIADesktop.CurrentName
        
        Set oTW = oUIAutomation.ControlViewWalker
        
        Set oUIElement = oTW.GetFirstChildElement(oUIADesktop)
        
        'while isobject(oUIElement) is not working so do it with exit do
        Do While True
            If oUIElement Is Nothing Then
                Exit Do
            End If
            
            Debug.Print i & "Title is: " & oUIElement.CurrentName & vbTab & "Handle=" & Hex(oUIElement.GetCurrentPropertyValue(UIA_NativeWindowHandlePropertyId)) & vbTab & "Class=" & oUIElement.CurrentClassName & vbCrLf
            Set oUIElement = oTW.GetNextSiblingElement(oUIElement)
           
            i = i + 1
        Loop
                
    End Sub


    • Edited by junkew Tuesday, September 6, 2016 7:49 PM
    Tuesday, September 6, 2016 7:29 PM
  • Hi,

    Is there a way to use elementFromPoint With VBA (excel) ?

    I have an error at debugging :

    User-Defined type may not be passed ByVal

    Sub Test()
    Dim uiAuto As New UIAutomationClient.CUIAutomation
    Dim elmRibbon As UIAutomationClient.IUIAutomationElement
    Dim pt As tagPOINT
     
        pt.x = 541
        pt.Y = 99
        Set elmRibbon = uiAuto.ElementFromPoint(pt.x, pt.Y)
    End Sub


    Have a nice day. Oliv-

    Wednesday, September 27, 2017 10:41 AM
  • what is your motive to using this?
    Friday, February 21, 2020 9:08 AM
  • might this will help you

    #If VBA7 Then
        Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
        Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
        #If Win64 Then
            Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal arg1 As LongPtr, ppacc As IAccessible, pvarChild As Variant) As Long
            Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
        #Else
            Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
        #End If
    #Else
        Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
        Private Declare Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
        Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
    #End If
    Private Const CHILDID_SELF = &H0&
    Private Const S_OK As Long = &H0

    Dim oIA As IAccessible
        Dim lResult As Long
        Dim tPt As POINTAPI
        Dim uiAuto As UIAutomationClient.CUIAutomation
        Dim myelement As UIAutomationClient.IUIAutomationElement

    GetCursorPos tPt
        #If Win64 Then
            Dim lngPtr As LongPtr
            CopyMemory lngPtr, tPt, LenB(tPt)
            lResult = AccessibleObjectFromPoint(lngPtr, oIA, 0)
        #Else
            lResult = AccessibleObjectFromPoint(tPt.x, tPt.Y, oIA, 0)
        #End If
        On Error Resume Next
        Set uiAuto = New UIAutomationClient.CUIAutomation
        Set myelement = uiAuto.ElementFromIAccessible(oIA, 0)
        Dim acs As UIAutomationClient.IUIAutomationInvokePattern
        
        Set acs = myelement.GetCurrentPattern(UIAutomationClient.UIA_InvokePatternId)
        acs.Invoke

    Friday, February 21, 2020 10:36 AM
  • Hi,

    Thank you, in my case it's not good. When i use ElementFromIAccessible i dont' get all the properties of the uiautomation element !

    try this with Excel run lance() and put your mouse under an icon. 

    the 2 msgbox don't get the same informations 

    Option Explicit
     
    Private Type POINTAPI
        x As Long
        Y As Long
    End Type
     
    #If VBA7 Then
        Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" (lpPoint As POINTAPI) As Long
        Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
        #If Win64 Then
            Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal arg1 As LongPtr, ppacc As IAccessible, pvarChild As Variant) As Long
            Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
        #Else
            Private Declare PtrSafe Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
        #End If
    #Else
        Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
        Private Declare Function AccessibleObjectFromPoint Lib "Oleacc" (ByVal lX As Long, ByVal lY As Long, ppacc As IAccessible, pvarChild As Variant) As Long
        Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
    #End If
     
    Private Const CHILDID_SELF = &H0&
    Private Const S_OK As Long = &H0
     
     
    Sub lance()
     
        Beep
        Application.OnTime DateAdd("s", 3, Now), "get_element_under_mouse"
     
    End Sub
    Private Sub get_element_under_mouse()
        Dim oIA As IAccessible
        Dim oCmbar As CommandBar
        Dim lResult As Long
        Dim tPt As POINTAPI
        Dim oButton As IAccessible
     
        GetCursorPos tPt
     
        #If Win64 Then
            Dim lngPtr As LongPtr
            CopyMemory lngPtr, tPt, LenB(tPt)
            lResult = AccessibleObjectFromPoint(lngPtr, oIA, 0)
        #Else
            lResult = AccessibleObjectFromPoint(tPt.x, tPt.Y, oIA, 0)
        #End If
     
        If lResult = S_OK Then
            '  On Error Resume Next
            MsgBox "name: " & oIA.accName(CHILDID_SELF) & vbCr & "------------------------------------" _
                 & vbCr & "Description: " & oIA.accDescription(CHILDID_SELF) & vbCr & "------------------------------------" _
                 & vbCr & "Value: " & oIA.accValue(CHILDID_SELF) & vbCr & "------------------------------------" _
                 & vbCr & "KeyboardShortcut: " & oIA.accKeyboardShortcut(CHILDID_SELF) & vbCr & "------------------------------------" _
                 & vbCr & "DefaultAction: " & oIA.accDefaultAction(CHILDID_SELF) & vbCr & "------------------------------------" _
                 & vbCr & "HelpText: " & oIA.accHelp(CHILDID_SELF) & vbCr & "------------------------------------" _
                 & vbCr & "RoleText: " & oIA.accRole(CHILDID_SELF) & vbCr & "------------------------------------" _
                 & vbCr & "Childcount: " & Val(oIA.accChildCount) & vbCr & "------------------------------------" _
                 & vbCr & "AccState: " & oIA.accState(CHILDID_SELF), , "IAccessible"
        End If
     
        Dim uiAuto As UIAutomationClient.CUIAutomation
        Dim elmRibbon As UIAutomationClient.IUIAutomationElement
        Dim cndProperty As UIAutomationClient.IUIAutomationCondition
        Dim ptnAcc As UIAutomationClient.IUIAutomationLegacyIAccessiblePattern
        Dim accRibbon As Office.IAccessible
        Dim i As Long
     
        On Error Resume Next
        Set uiAuto = New UIAutomationClient.CUIAutomation
        Set elmRibbon = uiAuto.ElementFromIAccessible(oIA, 0)
     
        If Not elmRibbon Is Nothing Then
            MsgBox "Name: " & elmRibbon.CurrentName _
                 & vbCr & "------------------------------------" _
                 & vbCr & "CurrentAcceleratorKey: " & elmRibbon.CurrentAcceleratorKey _
                 & vbCr & "CurrentAccessKey: " & elmRibbon.CurrentAccessKey _
                 & vbCr & "CurrentAriaProperties: " & elmRibbon.CurrentAriaProperties _
                 & vbCr & "CurrentAriaRole: " & elmRibbon.CurrentAriaRole _
                 & vbCr & "CurrentAutomationId: " & elmRibbon.CurrentAutomationId _
                 & vbCr & "CurrentClassName: " & elmRibbon.CurrentClassName _
                 & vbCr & "CurrentFrameworkId: " & elmRibbon.CurrentFrameworkId _
                 & vbCr & "CurrentHelpText: " & elmRibbon.CurrentHelpText _
                 & vbCr & "CurrentItemStatus: " & elmRibbon.CurrentItemStatus _
                 & vbCr & "CurrentItemType: " & elmRibbon.CurrentItemType _
                 & vbCr & "CurrentLocalizedControlType: " & elmRibbon.CurrentLocalizedControlType _
                 & vbCr & "CurrentProviderDescription: " & elmRibbon.CurrentProviderDescription _
                 & vbCr & "processID :" & elmRibbon.CurrentProcessId _
                 & vbCr & "CurrentItemType: " & elmRibbon.CurrentItemType, , "ui automation"
     
     
     
        End If
    End Sub
    


    Have a nice day. Oliv-


    • Edited by Oliv- Friday, February 21, 2020 2:23 PM
    Friday, February 21, 2020 2:22 PM
  • Did you ever get event handlers to work?
    Thursday, August 13, 2020 11:45 PM