Manipulating Local Files Using VBA

Introduction

A common requirement for Office Automation applications is to use the data in your spreadsheet / database / application to manipulate files stored on your local filing system. In VBA on a Windows platform there are two approaches you could use to do this:

In this article I use Excel as the host environment as an example but this approach works for everything using the standard Visual Basic for Applications environment including ASP although in Web applications it only makes sense for server side code; enabling access to local file manipulation from an Internet client is a totally unsafe thing to do so do not ask it of your users. I find myself using it mainly from Excel and occasionally from Access.

Please note that if you are using a .Net based development environment then you should use the System.IO.File library built into the framework…there is no need to touch the scripting objects at all.
 
So we are going to use something called the FileSystemObject in the Microsoft Scripting environment. The FileSystemObject appears in a number of the dll’s available for managing scripting but we just want to use the object not host our own scripting, so we only need to access the scripting runtime dll.

Referencing the DLL in Your Project

To gain the benefits of Intellisense and to be able to inspect the scripting type library in the VBA Object Browser we need to add a reference to the DLL to our projects. You do this from the References dialog accessed via the Tools > References… menu in the VBA Window.

You then need to select the DLL…either browse for scrrun.dll in the \\Windows\system32 folder or look for it under Microsoft Scripting Runtime in the Available References listbox on the dialog (see screenshot).

Exploring the Library

Having referenced the type library like this you can now use VBA’s built-in object browser (see screenshot below) to explore the objects, methods and properties provided by the library. Remember to select the Scripting type library in the top left dropdown to access the Scripting library hierarchy. There are three top level objects:


You can also review the documentation for the FileSystemObject provided by Microsoft in MSDN at http://msdn.microsoft.com/en-us/library/6kxy1a51(v=VS.85).aspx.

Using the FileSystemObject – A Simple Example

In this example we are going to copy some files from a base folder to programmatically created sub-folders and rename the files as we do so. It is a simplification of a real application for judging photographs in a competition.

Each photo is a jpg file with an anonymous ID and an Excel spreadsheet is used to record a score from a panel of 3 judges against the ID of the photo.

After the initial scoring the judges have to review the images again to award medals and ribbons; but they need to do that within each ability category rather than globally and obviously they want to see the images in reverse order of score. So we prefix the original filename with the score so the images will sort by score in an image browser; and then we copy the files, renaming them as we go, into sub-folders that match the categories.

The demo has 5 columns…image ID, category, score, source filename and destination filename. It copies all the files from the source folder to a sub-folder created using the categories. Outside the scope of this article there is also a simple macro to allow the user to select the base image folder (the sub-folders are created under that).
The zip file attached here contains a pdf of the article, the demo project spreadsheet and 10 appropriately named dummy jpg’s so you can test it. Just unzip to wherever you want then open the spreadsheet and click on the ‘Get Source Folder’ button and select the folder containing the jpgs. Then press ‘Extract Files’ to run the renaming code.
fsoArticle.zip

Key Features in the Example Code

The code is fairly self-explanatory with the main procedure shown below. All the actual folder and file handling are in only two lines of the code inside the for/next loop.

Also of note is that the CopyFile method will accept wildcards allowing you to copy multiple files with a single command; however there is also a single file method called Copy if you prefer that.

Conclusion

It is left to the reader to review the documentation for the FileSystemObject on MSDN and to explore its capabilities in more detail (http://msdn.microsoft.com/en-us/library/6kxy1a51(v=VS.85).aspx).

Basically this very useful scripting library provides a simple to use and powerful set of tools for drive, folder and file level manipulations from inside Excel, Access etc. It also supports text file sequential IO for programmatically working with structured text files (e.g. creating or parsing a delimited file like a csv, or field to a line etc).

It opens up the use of Excel in particular, as a host for some very sophisticated file handling utilities.