VBA – Shell.Application Deep Dive

In this installment … I thought I’d try and cover the wonderful world of the Shell.Application and illustrate some of its numerous capabilities:

Open file, folders, URLs, …
Get shell settings
Get information about the system
Get system folder paths
- Desktop
- Profile
- StartUp
- Many, many more …
Work with Folders and Files
- Folder Picker/Dialog
- Open Windows Explorer To A Specific Folder
- Determine If A Folder Exists
- Determine If A File Exists
- Create A Directory Structure/Nested Folders
- List SubFolders (recursively if wanted)
- List Files in a Folder or Zip File (recursively if wanted)
- Unzip a Zipped File/Folder
- Extract a Single File from a Zipped File/Folder
- Create a Zip File – Zip Folder or Files
and much, much more.

Continue reading →

VBA – Add Trailing Character or String

I think most of us have a function similar to Allen Browne’s

Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0 Then
        If Right(varIn, 1) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

to add a trailing slash as required to a string.

Today, I propose 2 extensions on that original function:

Continue reading →

VBA – Message Box That Supports Unicode Characters

Working on a recent project I needed to be able to use unicode characters in a Message Box. Sadly, the built-in MsgBox does not support such characters. Who knew?!

So I set off and quickly came across a Stack Overflow thread in which it was mentioned to use the MessageBoxW API. I then search it up and found:

MessageBoxW function (winuser.h) - Win32 apps

Displays a modal dialog box that contains a system icon, a set of buttons, and a brief application-specific message, such as status or error information. The message box returns an integer value that indicates which button the user clicked. (MessageBoxW)

Continue reading →

VBA – WMI – Deleting A Printer

Just a short post today to demonstrate how easy it is to delete a printer from a PC executing some simple WMI code via VBA.

If you follow my blog at all, by now you are fully aware that WMI can easily retrieve information regarding pretty much any aspect of your PC: Hardware, Software, Events, …, but it can do so much more. Did you know it can also make changes to your PC! It can install, configure and even delete pretty much anything as well. So let’s explore a first simple example, the printer.

Continue reading →

Access – Check If Anyone Has The Database Open

How can we tell if someone else has the database open?

Check for the Existence of a Lock File

In most cases, we should be able to check for the existence of a lock file (ldb or laccdb file). To do so we can use a function like the one presented in my article:

VBA - Determine if a File Exists or Not

Today, I thought I’d demonstrate 2 distinct methods that can be used to validate whether a file exists or not. The VBA Dir Function The File System Object’s FileExist Method

Now, in most scenarios, like 95+% of the time checking for the presence of a lock file is sufficient. However, there is one scenario where a lock file will not be present and that is when someone already has it opened exclusively.

If a user opens a database with exclusive access (by clicking the arrow to the right of the Open button, and then clicking Open Exclusive), record locking is not used. Therefore, Microsoft Access doesn’t attempt to open or create a lock file. Microsoft

Therefore, looking for a lock file will not detect those instances when someone has the database open in Exclusive mode.

The instant you go to open a database file, that is already opened exclusively, you will receive an error message:

Could not use ‘FilePathAndName’; file already in use.

Continue reading →

Access – VBA – Get The Next AutoNumber Value

Following up on my post:

Access - Reset AutoNumber Counter/Seed

Every once in a while I see a question regarding wanting to reset an autonumber’s counter/seed value to a specific number. Eliminating Gaps If the numbers have a big jump in numbering between the last entry in the table and a new one you are creating, and you are simply wanting to eliminate this…

Continue reading →

I thought we could look at how we can determine the next AutoNumber Value that will be assign when a new entry is made in a table.

Continue reading →

VBA – Universal RegEx Match Function

After yesterday’s post on how we can extract the MIME type from an HTML Img src attributes:

VBA - RegEx - Retrieve HTML Img Src MIME Type

I was working on some HTML automation recently and needed to extract the MIME type from an HTML Img Src Attribute value. I could get into Left, Right, Mid, InStr, Len, …, but I thought I’d stretch my VBA RegEx legs a little and create a simple reusable function to do the job. In case…

Continue reading →

I thought that I’d show how we can slightly adapt the code to be universal. Instead of creating one off functions for each and every scenario, how we can build a single function that we can use for all cases.

Continue reading →

VBA – Early and Late Binding Declarations

In a continuation on the subject of Early and Late Binding:

VBA - Early Binding and Late Binding

Early Binding and Late Binding When coding using VBA, as a developer, you have a choice of either using Early Binding or Late Binding. Below I will attempt to explain in plain English what each technique consists of and go over their Pros and Cons. Please keep in mind the advice provided below is all…

Continue reading →

VBA – Early Binding and Late Binding – Part 2

In my first post on the subject, VBA – Early Binding and Late Binding, I went over the basics, but I thought I’d push forward a little more to help demonstrate how to implement “The Best of Both World”. As I explained, in my first article, the best of both worlds consists of developing using…

Continue reading →

I decided I should provide a few examples of going from Early to Late binding and vice versa and give the necessary VBA Reference Library for Early Binding …

These should be great starting points in knowing which Reference Libraries are required for which Early Binding declarations, and also aid anyone wanting to convert in either direction.

I hope this help a few of you!

These are the Examples provided thus far:

Access
ADODB
- Connection
- Stream
ADOX
DAO
Excel
Microsoft Internet Controls
- Internet Explorer
MSHTML
PowerPoint
Microsoft Scripting Runtime
- Dictionary
- File System Object (FSO)
Microsoft Speech Object Library

WIA
- ImageFile
- ImageProcess
MSI
Word
MSXML
RegEx
WinHTTP
WMI
Script Control
WScript
- Shell
- Network
Shell Application

Continue reading →

VBA – RegEx – Retrieve HTML Img Src MIME Type

I was working on some HTML automation recently and needed to extract the MIME type from an HTML Img Src Attribute value.

I could get into Left, Right, Mid, InStr, Len, …, but I thought I’d stretch my VBA RegEx legs a little and create a simple reusable function to do the job.

In case it can help anyone else out there, here it is!

Continue reading →

Access – Hiding Database Objects

Have you ever wanted to hide a table, query or other database object from prying eyes?

Today, I thought we explore a few of the available options to do so. I’d like to explore:

Continue reading →