Tag Archives: VBA

Running Command Prompt Commands from VBA

As someone who works extensively in Microsoft Access and Excel, I often need to execute Command Prompt commands from within VBA. The standard WScript.Shell.Run method can run commands easily, but it does not capture or return their output.

To fill that gap, I created CP_GetOutput a VBA function that executes any Command Prompt command and retrieves its textual output, ready to be used in your VBA project.

Today I’m sharing both the code, a brief explanation of how it works and a few examples of who it can be used.
 
Continue reading →

Mastering Loops in VBA for Office Developers

Loops form the backbone of iterative programming in VBA for Microsoft Access, allowing developers to process recordsets, validate form inputs, or manipulate arrays efficiently without writing repetitive code. VBA provides four primary loop structures, Do loops (with While/Until variations), For Next, and For Each, each designed for specific scenarios such as conditional repetition or fixed-range traversal. Selecting the right loop depends on factors like whether the iteration count is known upfront, the need for early exits, or the data structure involved, such as DAO recordsets common in Access databases.
 
Continue reading →

Making Invisible Internet Explorer Instances Visible

While investigating behavior I had previously documented in which SuperAntiSpyware spawns hidden iexplore.exe processes. Rather than terminating them blindly, I wanted a way to make every existing Internet Explorer instance visible so I could see exactly what was going on.

Internet Explorer is not just a browser. It is also a COM automation server. This allows applications, scripts, and services to create Internet Explorer instances in the background for tasks such as report generation, authentication flows, or legacy system integrations. When those instances are created with their visibility turned off, or are never explicitly shown, they can remain running long after their original purpose has passed.

Task Manager will show the process, but it offers no insight into why it exists or what it is doing. Ending the process may remove the symptom, but it does not explain the behavior and can potentially disrupt the software that created it.

What I wanted was simple.

If Internet Explorer is running, I wanted to see it, see what it was up to.
 
Continue reading →

How to Terminate a Windows Process Using VBA (Shell, WMI, and API Methods)

Following my previous post on working with Windows processes, specifically:

Using VBA to List Active Processes
Because of some recent work I’ve been doing trying to embed applications within an Access form (so far I’ve managed to embed Brave, Edge, PowerShell ISE, Excel, Word, …) I’ve been working with listing the active computer processes. So I thought I’d share a few procedures I’ve developed to aid me with that task. I tried a couple different approaches to get the process listing. Initially, I was using the cmd: tasklist /fo csv /nh and although it did work, it was slow and created unnecessary lag in my process (no pun intended). Eventually I turned to my tried, tested…

Continue reading →

this article explores different techniques for terminating a Windows process using VBA.
 
Continue reading →

Get a Directory Listing Using VBA

Mastering Directory Listings in VBA: Exploring Folders and Subfolders with the Dir Function and FSO

Automating directory listings in VBA is a common requirement for Excel, Access, and other Microsoft Office solutions. Whether you need to list folders, enumerate subdirectories, or recursively scan an entire directory tree, VBA provides multiple ways to interact with the Windows file system each with distinct strengths and limitations.

In this article, you’ll learn how to generate directory listings in VBA using several proven techniques, including the built-in Dir function, the FileSystemObject (FSO) model, Shell.Application, WMI, and even PowerShell integration. We’ll explore when each approach is appropriate, how to handle recursive folder traversal safely, and why certain methods, especially Dir!, fail in nested scenarios.

If you’ve ever searched for “VBA list folders and subfolders”, “VBA recursive directory listing”, or “Dir vs FileSystemObject in VBA”, this guide walks through real-world, production-ready examples you can drop directly into your projects. By the end, you’ll know exactly which technique to use for simple folder scans, deep recursive listings, or advanced system-level automation.

Methods covered in this guide include:

  • Dir function (non-recursive directory listings)
  • FileSystemObject (FSO) for recursive folder traversal
  • Shell.Application for Windows-based folder enumeration
  • WMI for system-level and remote directory queries
  • PowerShell integration from VBA

 
Continue reading →

Using VBA to List Active Processes

Because of some recent work I’ve been doing trying to embed applications within an Access form (so far I’ve managed to embed Brave, Edge, PowerShell ISE, Excel, Word, …) I’ve been working with listing the active computer processes. So I thought I’d share a few procedures I’ve developed to aid me with that task.

I tried a couple different approaches to get the process listing.

Initially, I was using the cmd:

tasklist /fo csv /nh

and although it did work, it was slow and created unnecessary lag in my process (no pun intended). Eventually I turned to my tried, tested and true friend WMI!
 
Continue reading →

Microsoft Adds Native RegExp to VBA Ahead of VBScript Deprecation

Microsoft officially announced the phased deprecation of the VBScript library in October 2023, with a detailed timeline publicly shared in May 2024. VBScript has been a foundational technology in Windows scripting and VBA projects for decades. Its deprecation impacts key VBScript components such as VBScript.RegExp for regular expressions, Scripting.FileSystemObject (FSO) for file system operations, and Scripting.Dictionary collections, which many VBA developers currently rely on.
 
Continue reading →

VBA – Read Write INI Files

Looking through an old database I had created I came across some code I had put together a while back to read and write INI files.

If you’ve searched online a little, I’m sure you’ve found APIs such as the GetPrivateProfileString function, that can be used to do this, but as much as I can, I try to minimize my use of ActiveX and APIs and this is one case where an API is not required to perform basic text search and writing, because after all an INI file is nothing more than a text file.

As time has passed, I’ve also learnt the value of using INI files to store user preferences.  While it is possible to store such data in the database front-end itself, it is lost upon updating.  Another option would be to use the registry, but I dislike using the registry except for registration information and the likes.  Beyond which, registry settings cannot easily be pushed out to other new computers.  By using a simple INI file, you can store any information you choose, it remains intact when updates are performed and can be transferred with great ease to other computers so the user can retain their settings.
 
Continue reading →

Extracting the Decimal Part of a Number Using VBA

In a previous post I demonstrated several ways we could extract the whole number from a decimal value:

Extracting the Whole Number From a Decimal in VBA
Sometimes we wish to extract the whole number from a decimal value. So say you are doing an arythmatic like: 43/5 Which returns a value of: 8.6 and you wish to solely extract the whole number, thus you wish to get: 8 Here are a few techniques you can employ in VBA.  

Today, I thought we should examine the flip side of that same issue and see how we can retrieve just the decimal portion of a number (or mathematical operation) using VBA.

Continue reading →