[SOLVED] UI automation with excel

UI automation with excel

I am new to UI Automation. In my current organisation I was tasked with making an automated tool using GUI(Graphics User Interface) screen reading, but it is not working perfectly with other my colleague's machine because of a difference in screen resolution.

I watched this link on you-tube to try and understand UI Automation with excel, but I can't find much on this topic anywhere else.

Can anyone direct me toward resources on UI Automation? I Would like to know where I can learn it, read about it, and how to implement it with Excel.

Thanks in advance I really appreciate if anyone could help me.

Solution

UIAutomation from Microsoft is very powerfull and works well with windows 7, 8, 10 also from visual basic for applications (32 and 64 bits) and can be handy used to do some nice GUI Automation without expensive tools.

Make sure in VBA reference you have UIAutomationCore.Dll references (and weird enough sometimes on some computers you have to copy this to your documents folder)

Below you can see 2 base examples but as MS Automation is a huge library for all routines you can read a lot on MSDN for full documentation. I use the MS UIA routines in AutoIt and in VBA

For AutoIt its shared over here

https://www.autoitscript.com/forum/topic/153520-iuiautomation-ms-framework-automate-chrome-ff-ie/

For VBA I do not have a standard library but someone did a try with this https://github.com/mhumpher/UIAutomation_VBA

Option Explicit

Sub test()
    Dim c As New CUIAutomation
    Dim oDesktop As IUIAutomationElement

    Set oDesktop = c.GetRootElement

    Debug.Print oDesktop.CurrentClassName & vbTab & oDesktop.CurrentName & vbTab & oDesktop.CurrentControlType

End Sub

'Test uia just dumps all windows of the desktop to the debug window
Sub testUIA()
    Dim allEl As IUIAutomationElementArray                  'Returns an element array with all found results
    Dim oElement As IUIAutomationElement                    'Reference to an element
    Dim ocondition As IUIAutomationCondition

    Dim i As Long
    Dim x As New clsUIA


    'Just reference the three mainly used properties. many more are available when needed
    Debug.Print x.oDesktop.CurrentName & x.oDesktop.CurrentClassName & x.oDesktop.CurrentControlType

    Set ocondition = x.oCUIAutomation.CreateTrueCondition             'Filter on true which means get them all
    Set allEl = x.oDesktop.FindAll(TreeScope_Children, ocondition)    'Find them in the direct children, unfortunately hierarchies are strange sometimes

    'Just reference the three mainly used properties. many more are available when needed
    For i = 0 To allEl.Length - 1
        Set oElement = allEl.GetElement(i)
        ' If oElement.CurrentClassName = "PuTTY" Then
          Debug.Print oElement.CurrentClassName & oElement.CurrentName & oElement.CurrentControlType
           ' Debug.Print oElement.CurrentBoundingRectangle

            oElement.SetFocus
            DoEvents
            Sleep 2000
       ' End If

    Next
End Sub