MS-Excel / General Formatting

---

Accessing the File System Through VBA

If your applications need to work with the file system, VBA boasts quite a few features that make it easy. These features include a number of statements that return information about files and folders, as well as a number of functions with which you can manipulate files and folders. There are also powerful functions that give you direct access to files. This section examines all VBA's file-related statements and functions.

Returning File and Folder Information

If you need information about the file system-whether it's the name of the current directory, whether or not a particular file exists, or a file's date and time stamp-VBA has a function that can do the job. The next few sections look at five VBA functions that return file system data: CurDir, Dir, FileDateTime, FileLen, and GetAttr.

The CurDir Function

If you need to know the name of the active folder on a specific drive, use either the CurDir or the CurDir$ function:

CurDir(drive)
CurDir$(drive)

drive
The disk drive with which you want to work. If you omit drive, VBA uses the current drive.

The CurDir function returns the path as a Variant, and the CurDir$ function returns the path as a String. For example, the following statements display the current folder on drive D and the letter of the current drive:

MsgBox "Current folder on drive D is " & CurDir$("D")
MsgBox "The current drive is " & Left(CurDir$, 1)

To change the current drive and folder, see the descriptions of the ChDrive and ChDir statements later in this tutorial.

The Dir Function

To return the name of a file or folder, use the Dir function:

Dir(Pathname, Attributes)

Pathname
A String value that gives the file or folder specification. Note that you can use the standard wildcard characters-? for single characters and * for multiple characters.

Attributes
One or more constants that specify the file attributes:

Constant	Attribute
vbNormal (or 0)	Normal
vbReadOnly (or 1)	Read-Only
vbHidden (or 2)	Hidden
vbSystem (or 4)	System
vbVolume (or 8)	Volume label
vbDirectory (or 16)	Folder

If Dir is unsuccessful-that is, if no such file or folder exists-it returns the null string (""). This is a handy way to check for the existence of a file. Listing 14.3 shows an example.

A Procedure That Checks for the Existence of a File Before Opening It

Sub OpenToDoList()
    Dim strFile As String
    strFile = Environ("UserProfile") & "\Documents\To-Do List.txt"
    If Dir(strFile) <> "" Then
        Shell "Notepad " & strFile, vbNormalFocus
    End If
End Sub

This procedure builds the file path by using the Environ function to return the value of the %UserProfile% environment variable, to which \Documents\To-Do List.txt is added. The Dir function then checks to see whether this file exists and, if so, it uses the Shell function to load the file into Notepad.

If Dir is successful, it returns the first file or folder name that matches the Pathname file specification. To return the next file or folder name that matches the specification, you call Dir again, but this time without any arguments. Listing below shows a procedure that utilizes this technique to store the names of all the files from the user profile's Documents folder in a worksheet. After a bit of preparation, the procedure runs Dir to return the first file. Then a Do While loop runs Dir until there are no more filenames to return. Along the way, the filenames are stored in a worksheet. Then, when all is said and done, the filenames are sorted. At this point, you could use the sorted list to populate a list box or some other control.

A Procedure That Reads All the Filenames from the User Profile's Documents Folder, Stores Them in Sheet1, and Sorts Them by Name

Sub GetFilenames()
    Dim i As Integer
    i = 0
    '
    ' Start at cell A1
    '
    With Worksheets("Sheet1").[A1]
        '
        ' Clear the current values, if any
        '
        .CurrentRegion.Clear
        '
        ' Get the initial file and store it in A1
        '
        .Value = UCase(Dir(Environ("UserProfile") & "\Documents\", vbNormal))
        '
        ' Get the rest of the files and store them in Column A
        '
        Do While .Offset(i, 0) <> ""
            i = i + 1
            .Offset(i, 0) = UCase(Dir)
        Loop
        '
        ' Sort the filenames
        '
        .Sort Key1:=Worksheets("Sheet1").Columns("A")
    End With
End Sub

The FileDateTime Function

If you need to know when a file was last modified, use the FileDateTime function:

FileDateTime(Pathanme)

Pathname
A string that specifies the file with which you want to work (including, optionally, the drive and folder where the file resides).

If successful, FileDateTime returns a Variant date expression that holds the date and time stamp for the specified file.

The FileLen Function

If you need to know the size of a file (to see whether it will fit on a disk, for example), use the FileLen function:

FileLen(Pathanme)

Pathname
A string that specifies the file with which you want to work (including, optionally, the drive and folder where the file resides).

The FileLen function returns a Long value that tells you the number of bytes in the specified file. (On the odd chance that the file is already open, FileLen returns the size of the file when it was last saved.)

To help you try this function, Listing below presents the GetFolderUsage procedure, which calculates the total disk space used by the files in a folder. This procedure prompts you for a folder name and then uses the Dir function to return the filenames in that folder. For each filename, the FileLen function returns the number of bytes, and a running total is kept in the totalBytes variable.

A Procedure That Combines Dir and FileLen to Determine the Number of Bytes Used by the Files in a Folder

Sub GetFolderUsage()
    Dim folder As String
    Dim filename As String
    Dim totalBytes As Long
    '
    ' Get the folder name
    '
    folder = InputBox("Enter the folder name:", "Bytes Used in Folder")
    '
    ' Did the user click Cancel?
    If folder <> "" Then
        '
        ' Make sure there's a backslash at the end
        '
        If Right(folder, 1) <> "\" Then
            folder = folder & "\"
        End If
        '    
        ' Get the first filename
        '
        filename = Dir(folder, vbNormal)
        totalBytes = 0
        '
        ' Loop through the rest of the files
        '
        Do While filename <> ""
            '
            ' Update the total number of bytes
            '
            totalBytes = totalBytes + FileLen(folder & filename)
            '
            ' Get the next filename
            '
            filename = Dir
        Loop
        '
        ' Display the total
        '
        MsgBox "The folder " & folder & " uses " & totalBytes & " bytes" & _
               " or " & totalBytes / 1048576 & " MB."
    End If
End Sub

The GetAttr Function

As you may know, each file and folder has a set of attributes that indicate its status on the system (such as read-only or hidden). You can test for these attributes by using the GetAttr function:

GetAttr(Pathanme)

Pathname
A string that specifies the file or folder with which you want to work.

The return value is an integer that represents the sum of one or more of the following constants:

Constant	Value	Attribute
vbReadOnly	1	Read-only (the object can't be modified)
vbHidden	2	Hidden (the object isn't visible in the normal Explorer view)
vbSystem	4	System (the object is a system file)
vbDirectory	16	Directory (the object is a folder)
vbArchive	32	Archive (the object has been modified since it was last backed up)

To test for any of these attributes, you use the And operator to compare the result of the GetAttr function with the appropriate constant (this is known in the trade as a bitwise comparison). For example, if the following statement returns a nonzero value, the object given by path is read-only:

GetAttr(path) And vbReadOnly

In Listing below, the GetAttributes procedure prompts you for a filename (using Excel's GetOpenFilename method), uses GetAttr to return the file's attributes, and performs several bitwise comparisons to determine all the file's attributes.

A Procedure That Prompts for a Filename and then Returns the Attributes of the Selected File

Sub GetAttributes()
    Dim pathname As String
    Dim attr As Integer
    Dim msg As String
    '
    ' Get the filename
    '
    pathname = Application.GetOpenFilename("All Files (*.*), *.*")
    '
    ' Did the user click Cancel?
    '
    If pathname <> "" Then
        '
        ' Get the file's attributes
        '
        attr = GetAttr(pathname)
	msg = "Attributes for " & pathname & ":" & vbCrLf
	'
	' Determine the file's attributes and display them
	'
	If attr And vbReadOnly Then msg = msg & vbCrLf & "Read-Only"
	If attr And vbHidden Then msg = msg & vbCrLf & "Hidden"
	If attr And vbSystem Then msg = msg & vbCrLf & "System"
	If attr And vbDirectory Then msg = msg & vbCrLf & "Directory"
	If attr And vbArchive Then msg = msg & vbCrLf & "Archive"
	MsgBox msg
    End If
End Sub