[UIAutomation with VBA] Running an application with macros

01/29/2023

RPA-UIAutomation

Have you ever wanted to automate an internal system?

There is, but you have to use RPA or something like that to do it.

Not at all, it can be automated with VBA!

Internal systems are very troublesome to operate. They can be a mass of technical debt.

Do you think that browser-operated things can be manipulated by scraping, but dedicated applications cannot?

It’s okay. You can do that with VBA too.

By utilizing a library called UIAutomation, the same thing as RPA can be achieved with VBA.

Let’s use UIAutomation to automate even the most troublesome internal systems!

What is UIAutomation?

UIAutomation is a library provided by Microsoft.

This library can identify and manipulate user interface (UI) elements of other applications.

As we will see in detail later, by specifying the name and class name of the application element, you can click buttons, enter text, etc.

Introduction to the use of UIAutomation

From now on, I will show you how to use UIAutomation with a Windows calculator application as the subject.

(1) Find out the name and class name of the application element.

Before writing the program, let’s examine the names of the application elements and their class names.

There are many applications to examine, but I will introduce you to inspect.exe, which comes with Windows SDK.

Inspect.exe may already be installed, so first check the following folders to see if inspect.exe is present.

C:\Program Files (x86)\Windows Kits(major version)\bin(version/build number)\x86

Example:C:\Program Files (x86)\Windows Kits\10\bin\10.0.19041.0\x86

https://qiita.com/RPAbot/items/f1d6438c8f2500db65ae

If you don’t have inspect.exe, please download Windows SDK from the link below.

inspect.exe

When you open inspect.exe, you will see the tree structure of the application elements on the left side and the properties of the elements on the right side.

inspect.exe

To try it out, open the calculator application and press the “1" button.

As shown in the image above, the calculator window tree will open and the element “1 “button will become active.

inspect.exe

Once the element is active, look at the properties on the right.

There are two properties used in UIAutomation: Name and ClassName.

Either of these properties can be used to identify an element (elements).

Basically, use ClassName, and if ClassName is the same as another element, as in the image above, use Name to identify the element.

Use a window handle to get a window

The window at the top of the tree is obtained by using a window handle, as described below.

The window at the top of the tree can be obtained by using the window handle as described below.

From this topmost window, we will get the bottom elements one by one to get the desired button elements and so on.

supplement

Some applications do not allow inspect.exe to examine the elements.

In that case, UIAutomation cannot operate it, so please refer to the following article.

(I have implemented this in C#, but I am not sure if it can be implemented in VBA)

(2)Enable reference setting

UIAutomationClient

Activate the UIAutomationClient reference in the VBE reference settings.

(3) How to write automation code with UIAutomation

I will now show you how to write code to automate it with UIAutomation, using a calculator application as the subject.

This code will make the calculator calculate 86-1=. (It’s so easy.)

Private Declare Function FindWindowA Lib "user32" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Sub Calc()
'電卓でやろまい(86-1)を計算
    Dim Hwnd As Long
    Hwnd = FindWindowA(vbNullString, "電卓")
    Call CalcClick(Hwnd, "8")
    Call CalcClick(Hwnd, "6")
    Call CalcClick(Hwnd, "-")
    Call CalcClick(Hwnd, "1")
    Call CalcClick(Hwnd, "=")
End Sub
Private Sub CalcClick(ByVal Hwnd As Long, strBtn As String)
    Dim uiAuto As CUIAutomation: Set uiAuto = New CUIAutomation
    Dim uiElm As IUIAutomationElement
    Dim uiCnd As IUIAutomationCondition
    Dim uiInvoke As IUIAutomationInvokePattern
    
    'ウィンドウハンドルからエレメントを取得
    Set uiElm = uiAuto.ElementFromHandle(ByVal Hwnd)
    
    '電卓ウィンドウのエレメントを取得
    Set uiCnd = uiAuto.CreatePropertyCondition(UIA_ClassNamePropertyId, "Windows.UI.Core.CoreWindow")
    Set uiElm = uiElm.FindFirst(TreeScope_Children, uiCnd)
    
    'groupのエレメントを取得
    Set uiCnd = uiAuto.CreatePropertyCondition(UIA_ClassNamePropertyId, "LandmarkTarget")
    Set uiElm = uiElm.FindFirst(TreeScope_Children, uiCnd)
    
    Dim strBtnName As String '0～9（+-×÷=)エレメントのName
    
    'strBtnに応じてstrBtnNameとuiCndを設定
    Select Case strBtn
        Case "+"
            strBtnName = "プラス"
            Set uiCnd = uiAuto.CreatePropertyCondition(UIA_NamePropertyId, "標準演算子")
        Case "-"
            strBtnName = "マイナス"
            Set uiCnd = uiAuto.CreatePropertyCondition(UIA_NamePropertyId, "標準演算子")
        Case "/"
            strBtnName = "除算"
            Set uiCnd = uiAuto.CreatePropertyCondition(UIA_NamePropertyId, "標準演算子")
        Case "*"
            strBtnName = "乗算"
            Set uiCnd = uiAuto.CreatePropertyCondition(UIA_NamePropertyId, "標準演算子")
        Case "="
            strBtnName = "等号"
            Set uiCnd = uiAuto.CreatePropertyCondition(UIA_NamePropertyId, "標準演算子")
        Case Else
            strBtnName = strBtn
            Set uiCnd = uiAuto.CreatePropertyCondition(UIA_NamePropertyId, "数字パッド")
    End Select
    
    '標準演算子（数字パッド）のエレメントを取得
    Set uiElm = uiElm.FindFirst(TreeScope_Children, uiCnd)
    
    '0～9（+-×÷=)エレメントを取得
    Set uiCnd = uiAuto.CreatePropertyCondition(UIA_NamePropertyId, strBtnName)
    Set uiElm = uiElm.FindFirst(TreeScope_Children, uiCnd)
    
    '取得したエレメントをクリック
    Set uiInvoke = uiElm.GetCurrentPattern(UIA_InvokePatternId)
    uiInvoke.Invoke
    
End Sub

The Calc procedure is the program that controls the whole thing, and the CalcClick procedure is the one that actually operates the calculator application.

From here, we will introduce the key points of the program to be automated by UIAutomation.

Use a window handle to get a window

Private Declare Function FindWindowA Lib "user32" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Hwnd = FindWindowA(vbNullString, "電卓")
    'ウィンドウハンドルからエレメントを取得
    Set uiElm = uiAuto.ElementFromHandle(ByVal Hwnd)

The calculator window at the top of the tree is obtained using a window handle.

(Window handle is a control number that the computer assigns to each window.

Since the FindWindowA function of the user32 library is used, write the Private Declare~ part at the beginning of the module.

This function gets the window handle, and from the obtained window handle, gets the element (elements) on the application.

Get the tree element one level down.

    '電卓ウィンドウのエレメントを取得
    Set uiCnd = uiAuto.CreatePropertyCondition(UIA_ClassNamePropertyId, "Windows.UI.Core.CoreWindow")
    Set uiElm = uiElm.FindFirst(TreeScope_Children, uiCnd)

After this, the operation to get the tree element one level down is performed four times.

To get the tree element, use the FindFirst method.

This function takes two arguments, one for the position in the tree to search, and the other for the condition.

To find the position in the tree, use TreeScope_Children if the element is one level down.

(See the following link for other ways to specify the position in the tree)

The other condition is created using the CreatePropertyCondition method.
Use the name of the element or class name that you have just looked up here.

uiAuto.CreatePropertyCondition(UIA_ClassNamePropertyId, "ClassName")
uiAuto.CreatePropertyCondition(UIA_NamePropertyId, "Name")

Change the first argument between using the class name and using the name.

In this way, the combination of CreatePropertyCondition+FindFirst is used to get the tree element one level below.

The Select Case part may look a little complicated, but it just branches the condition and name depending on the button you want to input.

Check what you are doing while letting the steps run.

Click, enter, retrieve

    Dim uiInvoke As IUIAutomationInvokePattern
    'Click Element
    Set uiInvoke = uiElm.GetCurrentPattern(UIA_InvokePatternId)
    uiInvoke.Invoke

The last thing this program does is click on the calculator application button.

To click on the retrieved element, set it once to uiInvoke and execute the Invoke method.

Now you can click the button.

Dim uiVal As IUIAutomationValuePattern
Set uiVal = Element.GetCurrentPattern(UIA_ValuePatternId)
'Input String
uiVal.SetValue String
'Get the Value
Debug.Print uiVal.CurrentValue

Although we didn’t use it in this program, you can use SetValue/CurrentValue to enter a string or get a value.

Click here to read other VBA articles.

VBA

Posted by やろまい