VBA – Ribbon Automation Via UIAutomation

I’ve made a couple posts recently about UIAutomation:

VBA - Microsoft Access Navigation Pane Automation Via UIAutomation
A recent question by a fellow MVP made me look at some old code to see if I could solve the issue using VBA UIAutomation. I’ve briefly touched upon UIAutomation in the past with articles like: So What Can UIAutomation Do For Us When It Comes To The Navigation Pane? Well, it can enable us…

Continue reading →

VBA – Microsoft Access Navigation Pane Automation Via UIAutomation Cont'd
I recently got a comment/question from Access MVP Alum George Hepworth regarding my article and YouTube video: where he inquired: Daniel, is there a method to expand or collapse the nodes inside the Nav Pane, i.e. show or hide reports under that node? Like a dog with a bone, it got me intrigued to the…

Continue reading →

Measuring Tape
VBA - UIAutomation - Is The MS Office Ribbon Expanded or Collapse?
I’ve been playing around a lot with the UIAutomation library recently and wanted to see if it would be possible to check the status of the Ribbon. Traditionally, to check if the Ribbon was Expanded or Collapsed, people rely on the height, but I don’t like that because I have no clue if that value…

Continue reading →

Spring
VBA - UIAutomation - Minimize and Maximize the Office Ribbon
Just a quick post today and sort of a Part 2 to my previous post: I thought to myself, now that I can easily and reliably determine if the Ribbon is Expanded or Collapse, wouldn’t it be useful if I could change that (thinking about changing tab and clicking on command – coming soon!). And…

Continue reading →

VBA - Find a Window By Title and Set The Focus
In my previous post: I demonstrated a simple API that we can use to bring any window to the foreground to ensure our user see it.  This is great when automating external applications to make sure the automate app in visible to the user and not hidden underneath some other program window. The problem being…

Continue reading →

and more.

I had mentioned I would give a few demonstrations of working with the Ribbon, invoking commands and that what this article is about.

Below are simply a series of procedure to accomplish a variety of different things that can be done by using the Ribbon. Demonstrations of interacting with the UI via the UIAutomationClient library.

Continue reading →

VBA – Find A Window By Its Title and Bring It To The Front

In my previous post:

VBA - Bring a Window/Application to the Front
Here’s a very common question: If I open Excel/Word (any other application), how can I ensure it comes to the front and is visible to my users? Depending on how you start an application, depending on the version of Office, sometimes the application will automatically be on top.  Yet, in other cases it will not…

Continue reading →

I demonstrated a simple API that we can use to bring any window to the foreground to ensure our user see it.  This is great when automating external applications to make sure the automate app in visible to the user and not hidden underneath some other program window.

The problem being that the previous method relied on us having the window’s handle (Hwnd) for the window you wanted to bring to the front.  Now, for automating say Access, Excel, Word, … we can easily get the Hwnd to call the API, but there are many application where it isn’t as straightforward or even possible.  Microsoft simply hasn’t standardized this in any manner throughout its applications, and in some case didn’t make it possible at all. Think of PowerPoint for instance, where there is no application or presentation Hwnd property to use?!  This is where this article’s ‘more versatile’ approach comes in!

Continue reading →

VBA – Bring a Window/Application to the Front

Here’s a very common question:

If I open Excel/Word (any other application), how can I ensure it comes to the front and is visible to my users?

Depending on how you start an application, depending on the version of Office, sometimes the application will automatically be on top.  Yet, in other cases it will not and then the users don’t realize the command worked properly… then they run it again and again,… and find themselves with 10 copies…

We need a reliable solution!

Luckily there is a simple way to achieve this.

Continue reading →

VBA – Get UTC DateTime Information

Clocks Wave

Today, I thought I’d show you a simple way to determine the UTC Date and/or Time without the need of any APIs and complex algorithms.

The solution I found came from my extensive work with the WebBrowser control (but no, we’re not using any WebBrowser controls here today). I learnt through development about the Microsoft Script Control which enables us to run, amongst other things, JavaScript code right in VBA! And the solution was born. JS natively can retrieve and work with UTC Date and Time, so it was just a question of building a VBA wrapper around it. To make things more useful, I also added the necessary code to only retrieve the component I wanted at runtime: Date, Time or Date and Time.

Continue reading →

VBA – Determine the Current Ribbon State and Change It

I’ve shown you the way you can determine and then Expand and Collapse the Ribbon using UIAutomation in my previous articles:

Measuring Tape
VBA - Is The MS Office Ribbon Expanded or Collapse?
I’ve been playing around a lot with the UIAutomation library recently and wanted to see if it would be possible to check the status of the Ribbon. Traditionally, to check if the Ribbon was Expanded or Collapsed, people rely on the height, but I don’t like that because I have no clue if that value…

Continue reading →

Spring
Access - Minimize and Maximize the Office Ribbon
Just a quick post today and sort of a Part 2 to my previous post: I thought to myself, now that I can easily and reliably determine if the Ribbon is Expanded or Collapse, wouldn’t it be useful if I could change that (thinking about changing tab and clicking on command – coming soon!). And…

Continue reading →

and I prefer that approach for a couple reason, but also because it is part of a larger code base I have developed.

but I also am aware that sometimes we want a simple solution. 

So in this article I thought I’d show you a pure VBA (no extra references required) approach to do the same things I achieved with UIAutomation.

Continue reading →

VBA – UIAutomation – Minimize and Maximize the Office Ribbon

Spring

Just a quick post today and sort of a Part 2 to my previous post:

Measuring Tape
VBA - Is The MS Office Ribbon Expanded or Collapse?
I’ve been playing around a lot with the UIAutomation library recently and wanted to see if it would be possible to check the status of the Ribbon. Traditionally, to check if the Ribbon was Expanded or Collapsed, people rely on the height, but I don’t like that because I have no clue if that value…

Continue reading →

I thought to myself, now that I can easily and reliably determine if the Ribbon is Expanded or Collapse, wouldn’t it be useful if I could change that (thinking about changing tab and clicking on command – coming soon!).

And so, I started to do some digging and testing.

I was hung up on UIAutomation, and this was a mistake in this instance!  Sadly, from what I could determine, UIAutomation does not appear to provide any means to perform mouse click per se.  You can retrieve mouse coordinates and get into APIs, but I didn’t want any of that.

I knew we could use

CommandBars.ExecuteMso "MinimizeRibbon"

to minimize the Ribbon, but to maximize it, people often recommend and resort to using things like:

SendKeys "^{F1}", False

but SendKeys are dangerous and should be avoided!

Then, I had a mishap, I ran the minimize command when the Ribbon was already Collapsed, and it Expanded, say what?!  And so, the simplest solution ever was created, use he minimize command to both Collapse and Expand the Ribbon.

Continue reading →

VBA – UIAutomation – Is The MS Office Ribbon Expanded or Collapse?

Measuring Tape

I’ve been playing around a lot with the UIAutomation library recently and wanted to see if it would be possible to check the status of the Ribbon.

Traditionally, to check if the Ribbon was Expanded or Collapsed, people rely on the height, but I don’t like that because I have no clue if that value might vary from PC to PC, or Edition to Edition, or if Microsoft might randomly change it one day down the road for some reason.  So I decided to explore this a little using UIAutomation.

Continue reading →

VBA – Microsoft Access Navigation Pane Automation Via UIAutomation Cont’d

I recently got a comment/question from Access MVP Alum George Hepworth regarding my article and YouTube video:

VBA - Microsoft Access Navigation Pane Automation Via UIAutomation
A recent question by a fellow MVP made me look at some old code to see if I could solve the issue using VBA UIAutomation.

where he inquired:

Daniel, is there a method to expand or collapse the nodes inside the Nav Pane, i.e. show or hide reports under that node?

Like a dog with a bone, it got me intrigued to the point that I had to resolve it.

Continue reading →

VBA – Microsoft Access Navigation Pane Automation Via UIAutomation

A recent question by a fellow MVP made me look at some old code to see if I could solve the issue using VBA UIAutomation.

I’ve briefly touched upon UIAutomation in the past with articles like:

Access - VBA - Compact the Current Database
Here’s a functionality that is often asked for: How can I compact the current database via code (VBA)? Typically the answer is either: It can’t be done (the functionality was removed, don’t ask me why – ask Microsoft?!) Set the Compact on Close property to True, close the database and then set it back to…

Continue reading →

Target
VBA - Activate a Specific Ribbon Tab
As part of my article on Compacting the current database:   Some users indicated an issue that the code only worked when the Database Tools tab was first activated. So I wanted to find a way to do this, and I finally did!
Access - VBA - Set Focus On Specific Document Tab
I was trying to help out in a UA thread:   The Question? The question was: How can one switch to a specific tab, when using Tab Documents?

So What Can UIAutomation Do For Us When It Comes To The Navigation Pane?

Well, it can enable us to do things such as:

So let us explore UIAutomation and the Navigation Pane for a moment!

Continue reading →