Managing files should be really simple in Access, but it’s
not. Well, once you get familiar with the File Scripting Object (FSO), it’s not
so bad, but getting started is the painful part. To help you along, this month’s
download includes sample code for both
Access (early bound) and VB Script (late bound).
Here’s what the sample code will demonstrate:
- Use FSO in a VB Script file to backup a file
- Use FSO from Access to perform the following
a list of files from a folder
file attribute details
files from one folder to another
That’s not too ambitious for one article, but it’s
functionality that will be returned to time and again. Let’s see how it works.
FSO in VB Script
The download includes a file named FileCopy.vbs, the text of
which is reproduced below. This is a VB Script file, which may be edited in
Notepad. However, if you double click this file, Windows will try to execute
the code inside, so be careful with these.
Most of the script relates to creating a prefix for the file, based on today’s
date. Comments make up the bulk of what’s left, and way down there at the
bottom are the couple of lines that do the work … the FileScriptingObject
The FileScriptingObject API has been around long enough that, unless you’re
still running Windows 95, you needn’t worry about installing anything, and even
then, it might be there. All you need to do is call CreateObject() to
instantiate an instance, and start whacking away with commands. In this
example, we create the FSO object, and copy a file:
Set objFSO= CreateObject("Scripting.FileSystemObject") objFSO.CopyFile sDevFile, sArcFile Set objFSO= Nothing
This is called Late Binding, because our variable, objFSO,
is a variant type and the CreateObject() call is used to load the Scripting.FileSystemObject
into it. This is all that’s available to us in a VB Script file, but it works,
so we don’t complain.
The full text of the script is below. Copy it from this web page (watch for word wrap at the arrow) and paste into
a text file. Change the file paths ,save it as Something.vbs and you’re ready
to go. I use a script like this every day to make copies of my dev files to an
archive folder on the server. Once you see how we use FSO in the Access
application, you’ll realize how powerful this object is, and remember,
everything can be done in VB Script files, like the one below.
' ********************************************************************************* ' Script To Backup an Access MDB file to a Archive Server ' ' Danny J. Lesandrini ' October 18, 2007 ' [email protected] ' DataFast Consulting ' ' ********************************************************************************* ' Note, if you already didn't realize, that VB Script variable ' are declared without a data type. While VBA in Access allows ' for them, script does not. Dim sPrefix, sMonth, sDay, sYear, sHour, sMinuite, objFSO, sDevFile, sArcFile ' //////////////////////////////////////////////////////////////////////////// ' Get the file prefix for archive filename ' Format: YYYY-MM-DD_HH-NN i.e. 2007-10-18_08-27_ sYear = DatePart("yyyy",Date()) sMonth = DatePart("m",Date()) If Len(sMonth) = 1 Then sMonth = "0" & sMonth sDay = DatePart("d",Date()) If Len(sDay) = 1 Then sDay = "0" & sDay sHour= DatePart("h",Now()) If Len(sHour) = 1 Then sHour = "0" & sHour sMinuite = DatePart("n",Now()) If Len(sMinuite) = 1 Then sMinuite = "0" & sMinuite sPrefix = sYear & "-" & sMonth & "-" & sDay & "_" & sHour & "-" & sMinuite & "_" ' //////////////////////////////////////////////////////////////////////////// ' Set paths to development and Archive files sDevFile = "C:\YourDevelopment\YourFile.mdb" sArcFile = "\\YourServer\Public\Archive\" & sPrefix & "YourFile.mdb" ' The Scripting Object does the work Set objFSO= CreateObject("Scripting.FileSystemObject") objFSO.CopyFile sDevFile, sArcFile Set objFSO= Nothing ' All done. Report to user. MsgBox "Development file has been copied to Archive.", 64,"Finished"
FSO from Access
Interestingly, the code above will work in Microsoft Access
as is, provided it’s placed in a Function or Sub routine. However, there’s a
trick that will expose the world of FSO to us through IntelliSense. To enter
that world, we need to make one tweak to our Access mdb file: Add a reference
to the Windows Scripting Runtime library.
To add a reference, open any module and select References from the Tools menu.
Scroll down the list until you find the item highlighted in the screen shot
below, the Microsoft Scripting Runtime. Select it and click OK to save the
selection. This allows for Early Binding of variables, as we’ll see below.
Now that you have reference to FSO, you can create an early bound variable and
take advantage of Microsoft IntelliSense. This time, we create an object, objFSO,
not as a variant type, but of the type FileSystemObject. Now, Access can
provide help with properties and methods that are available to this object, as
shown in the image below.
We’ll see how this becomes helpful when we review the functionality of the demo
The Demo Application
I’m not going to review all the code in the demo app in this
article, but you can download it for
yourself and step through to see how it works. All the code relating to FSO is
I will, however, explain the code used to populate the list of files. You’ll
need to declare some variables as FileSystemObject, Folder and File. Also,
because I reuse this function for both listboxes (both left and right), it
takes an argument named sType to identify the control to be affected.
Private Function LoadFileList(ByVal sType As String) As Long On Error GoTo Err_Handler Dim objFSO As FileSystemObject Dim objFld As Folder Dim objFile As File Dim strSQL As String Dim intItem As Integer Dim ctlList As Access.ListBox Dim lngCount As Long Dim strFolder As String Dim strFile As String ' This function works for both list boxes, Left and Right ' The function argument, sType, determines which, and a ' control, ctlList, is set to the appropriate control. If sType = "Left" Then Set ctlList = Me!lstLeft strFolder = Me!txtLeft ElseIf sType = "Right" Then Set ctlList = Me!lstRight strFolder = Me!txtRight Else ' Bad call. Can't proceed Exit Function End If ' This is how objFSO is instantiated as an FSO object, ' and objFld is set to the selected folder Set objFSO = New FileSystemObject Set objFld = objFSO.GetFolder(strFolder) ' First, I empty the listbox of it's contents For intItem = 0 To ctlList.ListCount - 1 ctlList.RemoveItem 0 Next ' Now, loop through the files in folder object and ' add the file name to the list. For Each objFile In objFld.Files ctlList.AddItem objFile.Name lngCount = lngCount + 1 Next ctlList.Requery Exit_Here: LoadFileList = lngCount ' Don't forget to clean up after yourself! Set objFSO = Nothing Set objFld = Nothing Set objFile = Nothing Exit Function Err_Handler: MsgBox Err.Description, vbCritical Resume Next End Function
Similar code is used in a function called when you double-click on an item in
the listbox. That function is named ShowFileInfo() and after collecting some of
the interesting information about a file, it’s dumped into a message box. The
code to grab this data is shown below the screen shot, and as you can see, it’s
pretty much self-documenting.
Set objFSO = New FileSystemObject Set objFile = objFSO.GetFile(strFile) strOut = "File: " & objFile.Name & vbCrLf & _ "Size: " & objFile.Size & vbCrLf & _ "Type: " & objFile.Type & vbCrLf & _ "Created: " & objFile.DateCreated & vbCrLf & _ "Modified: " & objFile.DateLastModified & vbCrLf & _ "Parent: " & objFile.ParentFolder & vbCrLf MsgBox strOut, vbInformation
The FileSystemObject library has been around for a while, so
this is nothing new. Still, there are times when you just need to loop through
folders and files to perform maintenance, or as in this app, to list and
interrogate files. Download the demo application and give it a whirl.