Month: October 2017

Manipulating filenames in Excel or Access, or VB/A

Do you often need to manipulate filenames in VBA ?
Here’s a small set of very useful functions that I regularly use:

GetFileExt

Get the file extension part of a filename, without the leading dot (“.”).

Example (type in immediate window):

 

StripFileExt

Get the left part of a filename (and path), without the file extension part.

Example (type in immediate window):

 

StripFilePath

Get only the filename of a full or partial filename and path.

Example (type in immediate window):

 

StripFileName

Get only the denormalized(*) path of a full or partial filename and path.

Example (type in immediate window):

(*)denormalized: without a path separator (“\”) at the end.

 

ChangeExt

Change the current extension of a filename (ex. “pdf” in c:\temp\test.pdf) to the specified new extension, and return the new filename.

Example (type in immediate window):

 

Code

A simplified sprintf function for VBA

Did you ever need to quickly replace some placeholders in a string with a variable number of literals or variable values ?

Here’s a ssprintf() VB/A variant of C’s sprintf() function.
Let’s say that the first “s” in the name stands for “simplified”.
We’re – of course – far away from the full fledged power of the C function, but this may nonetheless come in handy or just be a starting point for your own custom implementation.

Check the comments for details.

The case for the accented characters

Do you want of the get rid of the accented characters when you convert a string to upper case in VBA ?

Sure, if you’re developing an Access (or another Office – Excel, Word, etc… – or VB/A) application targeting french, you’ll want to sometimes avoid the À for Ucase$(“à”).
I’m speaking of the default behavior of VB/A’s Ucase$() function:

This UUCase() function may spare you some time (talking about minutes, of course):

Voilà.