One useful file function is the Dir$ function. This function works like the Dir command at an MS-DOS command prompt. You can use the Dir$ function to retrieve a list of one or more operating system files that match a file specification or path. A path can include the name of a directory, a specific file name, or both. For example, C:/*.DWG is the path to all the files in the root directory of drive C having a DWG extension. The syntax of the Dir$ function is as follows :
stringvar = Dir$(path[,attributes])
One use of the Dir$ function is to determine whether a file exists. If you try to open a database or access a file that does not exist, an error occurs. However, you can use Dir$ first to check for a files existence before opening it, as in the following example :
If Dir$("C:/MYFILE.DWG" = " " Then Msgbox "The file was not found. Please try again!" End If
The Dir$ function returns the filename without the full path if the specified file is found, or it returns an empty string if no files were found. The preceding line of code displays a message box if MYFILE.DWG does not exist in the root directory of drive C. If the file does exist, the string "myfile.dwg" is returned. To make things even simpler, you can create a generic function that returns a Boolean value "True" if the given file exists.
Public Function bFileExists (Sfile As String) As Boolean If Dir$(sFile) <> " " Then bFileExists = True Else bFileExists = False End Function
This function could then be used to check any filename passed to the program by the user, as in the following example :
Dim sUserFile As String sUserFile = InputBox$("Enter the File Name : ") If Not bFileExists(sUserFile) Then MsgBox "The file does not exist. Please try again." End End If
Notice that the code sample ends the program if the file does not exist, to prevent any errors that might occur later. Another way to handle this situation would be to keep asking the user for a filename until a valid filename is entered.
Listing Files and Folders
Another use of the Dir$ function is to return a list of files in the specified path. If you use the Dir command at an MS DOS prompt, each matching file is listed on the screen. However, because the Dir$ function is designed to return only a single string variable, you have to use a loop and retrieve one filename at a time. (You can also display a list of files with a file list box, which is one of Visual Basic's default controls.
Suppose that your C:/DRAWINGS directory contains several drawings with a DWG extension. The path used to retrieve these files with the Dir$ function would be C:/DRAWINGS/*.DWG. You can use the following lines of code to retrieve the filenames and add them to a list box :
sNextFile As String sNextFile = Dir$("C:/DRAWINGS/*.DWG") While sNextFile <> " " lstDwgList.AddItem sNextFile sNextFile = Dir$ Wend
In the preceding example, notice that only the file path to Dir$ is supplied on the first call. Each subsequent call to Dir$ has no arguments, indicating that you want to use the previous file path and move to the next filename in the list. When no more files match, Dir$ returns an empty string and the "While" loop terminates.
Caution : When you use Dir$ in a loop, always exit the loop after an empty string is returned. If you try to make another call to Dir$ with no arguments, a runtime error occurs.
The second, optional parameter of the Dir$ function is used to provide additional conditions (beyond the specified path) with which to select files. For example, using the constant "vbDirectory" returns only the subdirectories (or folders) in the specified path. The constant "vbVolume" causes Dir$ to return the specified drive's volume label. The available constants are summarized below :
|vbHidden||2||Include Hidden Files|
|vbSystem||4||Include System Files|
|vbVolume||8||Return Drive Volume Label|
|vbReadOnly||1||Include read-only files|
Note : Constants can be added together if you want to use more than one. For example, the following code finds the system, hidden and read only file IO.SYS on a machine :
debug.Print Dir$("C:/IO.SYS" , vbHidden+vbSystem+vbReadOnly)
Note that the "vbHidden" constant refers to a file's attributes and not the Windows Explorer option that hides certain file types.
File Manipulation Functions
As with the Dir$ function, most of the file manipulation commands in Visual Basic are as straightforward as their MS-DOS equivalents, although with a few limitations. These commands are summarised below :
|File Manipulation Functions|
|Copy a File||FileCopy source dest|
|Delete one or more Files||Kill path|
|Rename a File||Name oldname As newname|
|Create a Folder||MkDir pathname|
|Remove an empty Folder||RemDir pathname|
|Change current directory||ChDir pathname|
|Change current drive||ChDrive drive|
Several of these functions are described in the following sections :
The "FileCopy" command has the limitation that you cannot use wildcards to specify multiple files. "FileCopy" can copy files locally or over a network, as shown in the following example :
'The following line copies a file while changing its name : FileCopy "D:\DRAWING\TEST.DWG", "C:\BACKUP\TEST-BACK.DWG" 'The following lines of code use a network path for the source file : Dim sDest As String Dim sSource As String sSource = "\\MYSERVER\DRAWINGS\TEST.DWG" sDest = "C:\BACKUP\TEST-BACK.DWG" FileCopy sSource, sDest
The "FileCopy" statement automatically overwrites an existing file, unless the file is read-only or locked open by another application.
Visual Basic also allows you to delete files by using the "Kill" statement. "Kill" can use wildcards to specify multiple files, as in the following example :
The "Name" statement is like the MS-DOS "RENAME" command but can be used on only one file at a time"
Name oldname As newname
You can also use "Name" like the "MOVE" command in MS-DOS if the specified paths are different :
'Moves the files to a new directory MkDir "C:\BACKUP\TEST-BACK.DWG" Name "D:\DRAWING\TEST.DWG" As "C:\BACKUP\TEST-BACK.DWG"
In the preceding example,note the "MkDir" statement, which you have probably guessed is used to create a new directory. The "MkDir" and "RemDir" statements add and remove directories.
Setting the Current Directory
In the examples discussed so far, the path has always included the drive and directory. However, as you may recall from using MS-DOS, during the context of your MS-DOS session, you are always "in" a certain directory, which is usually displayed to the left of the MS-DOS cursor. For example, if you type CD \WINDOWS, you can rename, copy, or delete files within the WINDOWS directory without specifying C:\WINDOWS in the pathname. The same concept of a "current directory" applies to Visual Basic. By using the "ChDir" and "ChDrive" statements, you can set the current working directory on each drive and switch between current drives, eliminating the need to specify the full path for each file operation :
'Change to the desired directory and drive and rename file ChDir "C:\DRAWINGS" ChDrive "C:" Name "TEST1.DWG" As "TEST2.DWG" 'Delete a file in the current directory ChDrive "D:" ChDir "D:\DRAWINGS" Kill "OLDTEST.DWG"
Performing deletes can be dangerous if you don't know the current directory. Fortunately, Visual Basic offers a function that provides this value: the "CurDir" function. The syntax of "CurDir" is :
stringvar = CurDir$([Drive])
Note : You can use the "Left$" function to get the current drive letter, as in the following example :
sDriveLetter = Left$(CurDir$( ) , 1)
Okay, let's put this all together into a practical example.
Wouldn't it be nice to be able to backup all your drawings from your working directory into a backup directory, either on another drive or to the network? Have a look at this wee application, remembering to change the directory paths to suit your own.
Open a new module and copy and paste this coding :
'VBA CODING STARTS HERE Option Explicit Sub Backup() Dim sSourceDir As String Dim sBackDir As String Dim sNextFile As String On Error GoTo FileCopyError 'Change these paths to suit sSourceDir = "C:\DRAWINGS\" sBackDir = "D:\BACKUP\" sNextFile = Dir$(sSourceDir & "*.DWG") While sNextFile <> "" FileCopy sSourceDir & sNextFile, sBackDir & sNextFile sNextFile = Dir$ Wend MsgBox "Drawing Backup Complete. Have a Nice Day." Exit Sub FileCopyError: MsgBox "There was a problem copying the Drawings" End End Sub 'VBA CODING ENDS HERE