File Management with Scripting Object

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
1. Retrieve
   a list of files from a folder




2. Show
   file attribute details
3. Copy
   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
calls.

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
'     dlesandrini@hotmail.com
'     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
application.

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
behind frmMain.

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

Conclusion

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.

»


See All Articles by Columnist Danny J. Lesandrini