Open Files, URLs and More Using VBA and PowerShell

This is another post in my recent series of posts regarding using PowerShell to empower any VBA application. Today, I thought I’d look at how PowerShell could offer a solution to launching files. No surprise here, PowerShell easily rose to the occasion!

PowerShell CmdLets

I happily started out developing a function using the Invoke-Item CmdLet, which worked beautifully. Then I had the idea of replacing Access’ Application.FollowHyperlink functionality, so be able to open both files and URLs, and Invoke-Item could deal with URLs.

A little more digging and testing and I was back in business using the Start-Process CmdLet instead and the following procedure was born!

Continue reading →

Get a List of Fonts using VBA and PowerShell

Fonts

Originally, back in 2015, I explored retrieving the font listing by automating MS Word and pulling the list from there. You can review that code by reading my post VBA – Enumerate Fonts.

Recently, I revisited that approach and provided the means to get the font list via the use of APIs, so no external apps required. You can read all the details of that approach by reviewing VBA – List Fonts.

As part of my continued exploration of PowerShell, I decided to see if it could be used in any way to simplify retrieving a list of system fonts.

PowerShell to the Rescue, Again!

The beauty of PowerShell is that it enables us to tap into many different coding languages/libraries … As such, thanks to PowerShell, we can tap into the power of .NET and its’ InstalledFontCollection class and the job is basically done for us!

So once we know that, like every other PowerShell example, it is simply a question of writing a simple wrapper function et voilà!

Continue reading →

How to Zip and Unzip Files Using VBA

I know, I know, you’ve already seen code that can zip and unzip files. Things like:

but, I thought, that perhaps we could explore a newer, and perhaps easier, approach today.

PowerShell to the Rescue!?

A short while back, for fun, I explored invoking PowerShell commands through VBA and created a series of procedures and shared them in my article entitles VBA – Run PowerShell Command.  Since then, I’ve furthered my education in PowerShell and have had my eyes opened to its true power and simplicity (once you understand the basics).  PowerShell basically can do everything on a PC (Networking, Hardware, ActiveDirectory, Events, …), once you know which cmdlet to use!

So I thought I’d take a second look at a common need zipping/unzipping and see if PowerShell could possibly simplify a developers life.

What I found out was the fact that PowerShell can perform either of these action using a single line of code!  No APIs, dlls or convoluted modules.

So combining the PS_Execute() sub from VBA – Run PowerShell Command with either of these commands and we have the ability to create zip files or extract the content of a zip file with 1 line of code.

Below are 2 procedures I created to illustrated how this can be done.

Continue reading →

Office 2021 / Access 2021

If you weren’t already aware, earlier this month both Windows 11 and Office 2021 were released.

I’ve been involved in a couple discussions regarding Office 2021 regarding what it offers and where we can get it.

What’s New in Access 2021?

Continue reading →

Get a String Hash using VBA

Since I was already working on Get a File Hash using VBA it made sense to look into how one could hash a string directly.

The basic principle remains identical, in the larger sense, that is I use VBA to run a PowerShell script and return the generated Hash, but this time we need to use the -InputStream parameter to be able to use a string directly. I won’t bore you with the detail, you can review the code for yourself.

Continue reading →

Get a File Hash using VBA

Once again, as part of a personal project, I had the need to get the hash (MD5 in this instance) of certain files.

Standard VBA Approaches

I searched all over the web. Some solutions worked, others couldn’t handle larger files (in excess of 2 GB), but all were relatively slow.

PowerShell to the Rescue?

This got me thinking about a better solution. That got me thinking of the new technologies that Microsoft is pushing and made me wonder if PowerShell could offer a hand here.

Continue reading →

Get a List of VBA Constants

I was part of a small discussion this morning regarding VBA constants which made me create today’s article.

The more you do Late Binding coding, which IMHO you should always do!, the more you need to use constants for the given application you are automating.

Now, obviously, if you are merely coding a simply procedure, you can quickly open the other application and retrieve the constant you need from the VBE Object Browser. That said, if you get into complex automation and require a lot of the constants, wouldn’t it be nice to simply load a module with them all in one shot and not need to waste your time copying back and forth countless constants?! Lucky for you, it is possible and I’m going to show you just how easy it is to compile such a list.

Generate a List of VBA Constants

The key to the process below is you need to know that we can use Type Information Library (tlbinf32.dll) to query an application to retrieve, amongst other things, their constants. Furthermore, depending on the application, this information is stored in different files, so it can be found in the program’s:

  • exe (Excel)
  • olb (Access, Outlook, PowerPoint, Word)
  • dll (Office)

Continue reading →

VBA’s FileLen Unreliable!

Examine Files

I’ve been working on a personal project in which I need to index files. As part of the indexing process I retrieve the file size.

The Problem With FileLen

For years, in various databases, I have always used VBA’s built-in FileLen Function to quickly and easily return the file size (in bytes) and never experienced any issue.  Well, that was until today!

Sadly, I found out today that FileLen() is simply unreliable and that it returns negative values for certain files, thus crashing my application.

Continue reading →

Working With Access Macros Efficiently

Okay, anyone that reads my blog, or knows me, knows there’s no love lost between myself and Macros.

They are very poor to work with, troublesome to troubleshoot, no means to copy/duplicate and just a plain Pain In The Ass to develop. Once again, another feature that was never fully developed.

Regardless, today I thought I’d share one easy little tip to hopefully help a few of you out there when working with Macros (this also applies to developing Microsoft Flows which are basically the same as developing Macros).

One Of The Main Problems With Working With Macros And Flows

I was working on troubleshooting a client’s database which uses a tremendous amount of macros and they don’t want to spend money converting over to VBA. I was having a hard time to identify where the issue was coming from and wanted to eliminate certain Macros, one by one, to identify the root cause of the problem. The issue is that Access offers no way to deactivate an Event/Macro (nor does flow).

Suggestion For Microsoft
Add a simple checkbox to Macro Actions so developers can easily toggle active/inactive individual Actions. The same would be great for Embedded Macros as a whole at the Event level!

As a developer you basically have 2 options:

  • Delete the Macro and then recreate it once you are done
  • Create a copy of the database to do your investigative work and then return to the original copy for development

Both approaches are far from ideal, typically involves a lot of duplication of work, …

Continue reading →