Tip of the Week #3: PathCompact

Wow, December was a very busy month for me, so I never got around to posting more than 1 tip of the week in December, so seems I am a bit behind. Anyways the inspiration and code for this tip comes from Ben Sacherich, a fellow member and co-founder of  “Professional Access Developers Unit”, my online networking user group.


The Problem

Sometimes we need to record the path of a file. The problem with filepaths is that they can vary by quite large amounts. You can have very short paths, such a “C:\Myfile.pdf” or you can have extremely long paths such as:

S:\DataStorage\Department of Health and Safety\Projects\Project 50014 Analysis of Health at the construction site on 5th avenue\001 Project Documentation\005 Reports\2013\December\Inspection Notice\2013-12-08 Inspection of site nr 5 – Handrail and staircase inspection.pdf

Now even here on my blog that takes up a few lines on my screen (at least at current resolution/blog template 🙂 ), and in my form:

Textbox not big enough to show contents

Textbox not big enough to show contents

On the other hand, if I make the textbox big enough to show the long path, it will look silly when I have a short path

Textbox looks way too large for its content

Textbox looks way too large for its content

The Solution

What we need is a way to reduce the path, while keeping the most essential parts of the path visible to the user. Now we could of course just write something out in VBA, but actually there is already a windows API function that can do this for us. Using a built in function can result in less control over the output (i.e. the function might reduce the path in a different way than we would have done with our own code) but on the other hand, it might provide a more similar approach between different applications if we are all using the same function. Besides, the already available function has been tested in various scenarios, and we just need to write a small wrapper to implement it in our application. In many cases it will make sense to have a wrapper function, because the wrapper function can make sure we weed out any bugs before they happen. For example this API will crash access if we pass it a negative number. Far better to have the wrapper make sure our number is positive before passing it.

First to make the API available we must declare it, and (of course) it must be done in the declaration section of a VBA module. It can be a form module as well, but in that case it would be private to the form, so it would be more useful to place it in a standard stand-alone module. You can see both the decleration of the API and the code below. The code is a modified version of the code found at Excel MVP Chip Pearsons site.

Now using this function, my textbox ends up looking like this:

Now the text will always fit

Now the text will always fit

In this way we have ensured that the text always fits the textbox. In the next tip of the week I will move forward with this example, and show how we can even allow manual edits to the field, while still having it fit in the box.

One comment on “Tip of the Week #3: PathCompact
  1. Crystal says:

    very nice! thanks, Smiley ~ Crystal

Ask a question, leave some feedback, post a reply!

This site uses Akismet to reduce spam. Learn how your comment data is processed.