使用VBA宏进行点击/模拟点击 [英] Clicking/Simulating a Click using a VBA macro
问题描述
I'm looking to write a VBA macro that will execute or click a button from the SAP Analysis for Office plug-in in the ribbon.
当前,我有一个可行的替代方法,可以使用功能区选项卡的热键和在VBA宏中使用SendKeys
发送的按钮,但是它不够健壮,因为不同的用户可能具有不同的功能区布局,因此对于相同的功能.
Currently, I have a working alternative using the hotkeys for the ribbon tab and the button which I send using SendKeys
in my VBA macro but it's not robust enough as different users may have different ribbon layouts and thus different hotkey combinations for the same functionality.
这是我要以编程方式单击的按钮.
This is the button I would like to programmatically click.
您能否使用目前推荐的Office流畅色带概念来协助我实现这一目标?
Can you please assist me in achieving this using the Office fluent ribbon concept that is currently recommended?
宏记录器不会在功能区上记录操作.
The macro recorder does not record actions on the ribbon.
我相信我掌握了包含Analysis功能区结构的XML文件.这是我需要的按钮结构的屏幕截图.
EDIT 2: I believe I got hold of the XML file that contains the Analysis ribbon structure. Here's a screenshot of the structure of the button I need.
推荐答案
我找到了一种使用MS提供的UIAutomation
框架来实现此目的的方法,该框架可以在您的项目中称为UIAutomationClient
I found a way to do it with using the UIAutomation
framework provided by MS which could be referenced in your project as UIAutomationClient
代码如下:
'global object to hold the translations
Dim translations As Object
Public Enum oConditions
eUIA_NamePropertyId
eUIA_AutomationIdPropertyId
eUIA_ClassNamePropertyId
End Enum
'element name translations for English
Function getEnglishTranslations() As Object
Set Words = CreateObject("Scripting.Dictionary")
'element, element name
Words.Add "Analysis", "Analysis"
Words.Add "Lower Ribbon", "Lower Ribbon"
Words.Add "Design Panel Group", "Design Panel"
Words.Add "Design Panel Menu", "Design Panel"
Words.Add "Display Button", "Display"
Set getEnglishTranslations = Words
End Function
'element name translations for German
Function getGermanTranslations() As Object
Set Words = CreateObject("Scripting.Dictionary")
'element, element name
Words.Add "Analysis", "Analysis"
Words.Add "Lower Ribbon", "Unteres Menüband"
Words.Add "Design Panel Group", "Designbereich"
Words.Add "Design Panel Menu", "Designbereich"
Words.Add "Display Button", "Anzeigen"
Set getGermanTranslations = Words
End Function
Function translate(element As String) As String
translate = translations(element)
End Function
Sub toggleDisplay()
Application.ScreenUpdating = False
Dim oAutomation As New CUIAutomation 'the UI Automation Object
'references for elements in the UI tree
Dim root As IUIAutomationElement
Dim xl As IUIAutomationElement
Dim analysisTab As IUIAutomationElement
Dim xlrib As IUIAutomationElement
Dim dpanel As IUIAutomationElement
Dim display As IUIAutomationElement
'pattern objects which allow the execution of different UI elements'
functionality
Dim oTogglePattern As IUIAutomationTogglePattern
Dim expcolPattern As IUIAutomationExpandCollapsePattern
Dim selPattern As IUIAutomationSelectionItemPattern
'multi-clause condition references to locate the UI elements in the UI
tree.
Dim analysisCond As IUIAutomationCondition
Dim displayCond As IUIAutomationCondition
Dim disPanelCond As IUIAutomationCondition
'stores the language code for the current active language
Dim languageCode As Integer
'get the active application language
languageCode = _
Application.International(XlApplicationInternational.xlCountryCode)
'choose the language dictionary based on the active language
If languageCode = 49 Then
Set translations = getGermanTranslations
Else
Set translations = getEnglishTranslations
End If
'get a reference to the UI element tree
Set root = oAutomation.GetRootElement
'locate the Excel window
Set xl = root.FindFirst(TreeScope_Descendants, PropCondition(oAutomation,
eUIA_NamePropertyId, _
"test wb for hidden ribbon - new (delete me).xlsm - Excel"))
'click the Analysis tab
Set analysisCond = _
oAutomation.CreateAndCondition(PropCondition(oAutomation,
eUIA_NamePropertyId, translate("Analysis")), _
PropCondition(oAutomation, eUIA_ClassNamePropertyId, "NetUIRibbonTab"))
Set analysisTab = xl.FindFirst(TreeScope_Descendants, analysisCond)
Set selPattern = _
analysisTab.GetCurrentPattern(UIA_SelectionItemPatternId)
selPattern.Select
'locate the Design Panel Group
Set xlrib = xl.FindFirst(TreeScope_Descendants,
PropCondition(oAutomation, eUIA_NamePropertyId, translate("Lower
Ribbon")))
Set dpanel = xlrib.FindFirst(TreeScope_Descendants,
PropCondition(oAutomation, eUIA_NamePropertyId, translate("Design Panel
Group")))
'try locating the Display button
Set displayCond = _
oAutomation.CreateAndCondition(PropCondition(oAutomation,
eUIA_NamePropertyId, translate("Display Button")), _
PropCondition(oAutomation, eUIA_ClassNamePropertyId,
"NetUIRibbonButton"))
Set display = dpanel.FindFirst(TreeScope_Descendants, displayCond)
'true when the window is shrunk to a point where the display button
'is part of the dropdown menu under Design Panel
If display Is Nothing Then
'expand the Design Panel dropdown first
Set disPanelCond = _
oAutomation.CreateAndCondition(PropCondition(oAutomation,
eUIA_NamePropertyId, translate("Design Panel Menu")), _
PropCondition(oAutomation, eUIA_ClassNamePropertyId, "NetUIAnchor"))
Set dpanel = dpanel.FindFirst(TreeScope_Descendants, disPanelCond)
Set expcolPattern = _
dpanel.GetCurrentPattern(UIA_ExpandCollapsePatternId)
expcolPattern.Expand
'attempt to locate the Display button again
Set display = dpanel.FindFirst(TreeScope_Descendants, displayCond)
End If
'Click the Display button programmatically (FINALLY!!!)
Set oTogglePattern = display.GetCurrentPattern(UIA_TogglePatternId)
oTogglePattern.Toggle
Application.ScreenUpdating = True
End Sub
'generate a Condition object with the string to be matched against the
selected property
Function PropCondition(UiAutomation As CUIAutomation, Prop As oConditions,
Requirement As String) As IUIAutomationCondition
Select Case Prop
Case 0
Set PropCondition = _
UiAutomation.CreatePropertyCondition(UIA_NamePropertyId,
Requirement)
Case 1
Set PropCondition = _
UiAutomation.CreatePropertyCondition(UIA_AutomationIdPropertyId,
Requirement)
Case 2
Set PropCondition = _
UiAutomation.CreatePropertyCondition(UIA_ClassNamePropertyId,
Requirement)
End Select
End Function
Unfortunately, I've ran into another problem with this approach which I have no resolution for as of now.
这是一个视频,您也可以观看它来帮助您入门.
Here's a video which you can watch to help you get started as well.
这篇关于使用VBA宏进行点击/模拟点击的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!