Manipulating Files and Folders

In addition to just finding out file system information, VBA also lets you manipulate various aspects of the file system, including changing the current drive and folder, creating new folders, and deleting and renaming files and folders. The next few sections take you through each of VBA's file system manipulation statements.

The ChDir Statement

Each Office application maintains a default folder setting, which is the folder that appears initially when you display a folder-based dialog box, such as Open or Save As. To change an application's default folder, use the ChDir statement:

ChDir Path

Path
A string that specifies the new default folder.

If the Path parameter doesn't include a drive designator, VBA changes the folder on whatever drive is current. If Path does include a drive, VBA changes the default folder on that drive, but it doesn't change the current drive. For example, if the current drive is C and you run ChDir D:\, the default folder is changed on drive D, but drive C remains the current drive. If you want the folder on D to appear in Open or Save As by default, you must also change the default drive, as explained in the next section.

The ChDrive Statement

To change the default drive, use the ChDrive statement:

ChDrive Drive

Drive
A string that specifies the letter of the new default drive.

For example, the following statement changes the default drive to D:

ChDrive "D"

The FileCopy Statement

If you need to copy a disk file from one location to another, use VBA's FileCopy statement:

FileCopy(Source, Destination)

Source
A String value that specifies the name of the file you want to copy (including, optionally, the drive and folder in which the file resides).

Destination
A String value that specifies the name of the destination file (including, optionally, the drive and folder).

The following statements set the Source variable to a filename, set the Destination variable to a filename on a network drive, and use FileCopy to copy the file:

source = Environ("User Profile") & "\Documents\Letter.doc"
destination = "\\Server\pub\users\paul\Letter.doc"
FileCopy source, destination

The Kill Statement

When you need to delete files from the system, use the aptly (if perhaps a bit violently) named Kill statement:

Kill Pathname

Pathname
A String value that specifies the name of the file you want to delete (including, optionally, the drive and folder in which the file resides).

You can use the ? and * wildcard characters in the Pathname argument to delete multiple files. Note that the Kill statement generates an error if the specified file is open or doesn't exist. To avoid the latter error, first use the Dir function to see whether the file exists:

If Dir("C:\Garbage.txt") <> "" Then
    Kill "C:\Garbage.txt"
End If

The MkDir Statement

If your application requires a new folder in which to store files, you can use the MkDir statement to create the folder:

MkDir Path

Path
A string that specifies the new folder. If you don't include the drive letter, VBA creates the folder on the current drive.

The following statement creates a new Backup folder on drive E:

MkDir "E:\Backup"

The Name Statement

You can rename a file or folder by running the Name statement:

Name oldpathname As newpathname

oldpathname
A String value that specifies the pathname of the folder or file you want to rename. (Wildcards are not supported.)

newpathname
A String value that specifies the new name of the folder or file. If you change the path but not the name of the file, VBA moves the file to the new location.

The Name statement generates an error if the specified file is open or doesn't exist.

The RmDir Statement

To let you delete a folder you no longer need, VBA offers the RmDir statement:

RmDir Path

Path
A string that specifies the folder you want to delete. If you don't include the drive letter, VBA deletes the folder from the current drive.

Note that RmDir raises an error if the folder you specify still contains files. Your code should check to see whether a folder contains files and, if it does, it should first use Kill to delete the files and then use RmDir to delete the folder. Listing below shows a procedure that does exactly that. After getting the name of the folder to delete, the procedure uses Dir to loop through the folder's files. (You might want to modify this code to check for subfolders as well.) If the folder contains files, the total is reported to the user, who then has the option of canceling the deletion. If the user elects to proceed, Kill is used to delete each file, and then RmDir deletes the folder.

This Procedure Deletes a Folder, First Deleting Any Files the Folder Contains

Sub DeleteFolder()
    Dim folder As String
    Dim filename As String
    Dim totalFiles As Integer
    '
    ' Get the folder name
    '
    folder = InputBox("Enter the name of the folder to delete:")
    '
    ' 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, vbDirectory)
	'
	' Bail out if the folder doesn't exist
	'
	If filename = "" Then
	    MsgBox "Folder doesn't exist!"
	    Exit Sub
	End If
	'
	' Loop through the rest to get the file total
	'
	totalFiles = 0
	Do While filename <> ""
	    '
	    ' Get the next filename
	    '
	    filename = Dir
	    '
	    ' Ignore the parent (..) and the last Dir
	    '
	    If filename <> ".." And filename <> "" Then
	        '
	        ' Update the total number of files
	        '
	        totalFiles = totalFiles + 1
	    End If
	Loop
	'
	' Check the total
	'
	If totalFiles > 0 Then
	    '
	    ' If there are files, let the user know
	    '
	    If MsgBox("The folder " & folder & _
		    " contains " & totalFiles & _
		    IIf(totalFiles > 1, " files.", "file.") & _
		    vbCrLf & _
		    "Are you sure you want to delete it?", _
	    	    vbOKCancel + vbQuestion) = vbCancel Then
		Exit Sub
	    End If
	    '
	    ' Get the first filename
	    '
	    filename = Dir(folder, vbNormal)
	    '
	    ' Loop through and Kill the rest of the files
	    '
	    Do While filename <> ""
		Kill folder & filename
		'
		' Get the next filename
		'
		filename = Dir
	    Loop
	End If
	'
	' Delete the folder
	'
	RmDir folder
    End If
End Sub

The SetAttr Statement

Earlier you saw how to use the GetAttr function to return the attributes of a file or folder. However, you can also set these attributes by invoking the SetAttr statement:

SetAttr Pathanme, Attributes

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

Attributes
One or more constants that specify the attributes you want to set.

The constants recognized by VBA are the same as those outlined earlier for the GetAttr function (except that you can set the Directory attribute): vbReadOnly, vbHidden, vbSystem, and vbArchive. Note that this statement produces an error if you attempt to set the attributes of an open file.

[Previous] [Contents] [Next]