Tag Archives: MS Excel 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 →

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 →

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 →

How-to Extract All The URLs From a String

I was helping out in a forum discussion in which a developer was asking for assistance in getting a listing of URLs found within a string of text and thought I’d share here the solution(s) I came up with as I’m sure he’s not the first, nor the last to need to do this type of thing.
 
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 →

Auto Orienting Images via VBA

Have you ever loaded an image into say an Access Image control, or ‘legacy’ Web Browser control, only to have the image display flipped and/or rotated like:

when if you open it in most image software, preview it in explorer, open it in most modern day web browsers it displays just fine like:

and wondered what was going on exactly.

This happens because of something called the Exif Orientation tag, which is a piece of metadata stored inside many JPEG images by cameras.

Basically, when you take a photo, your camera records how you were holding the device (upright, sideways, upside down, etc.) and saves this information in the Exif Orientation tag (this is hidden information that is stored with the image). Instead of actually rotating the pixels in the image file, the camera just notes the orientation in the metadata. Some programs, like Windows Explorer, read this tag and automatically display the image in the correct orientation. Other programs might ignore the Exif Orientation tag and show the image as it is physically stored, which can result in the image appearing sideways or upside down
 
Continue reading →

The Best Approach To Reading Files In VBA

Years ago, I develop a simple function to read a ‘text’ file using VBA so I could pass the ‘text’ file’s content to a VBA variable to then be able to work with.  You can consult that article by referring to:

VBA - Read File into Memory
The following VBA function enables you to read files, for instance a text file, into memory (pass the read file’s content to a variable for instance) to further use within your application. Instead of reading each line, one by one, the following approach reads the entire file in one go. So this should be faster than other methods. …

Continue reading →

More recently, having over the years used a variety of different ways to read files, I was curious if one approach was better (from a performance standpoint)?
 
Continue reading →