File Management

Before opening a file in Word, Excel, PowerPoint, or Access, you need to determine the filename of the document to be opened. When you save a document, you need to specify a filename. When you work with the Office applications, you use the Open and Save As dialog boxes to navigate through the file system on your machine, your network, or your Web server to open or save a document. In your Visual Basic programs, to find files and folders you can use a combination of Visual Basic for Applications built-in functions, Windows application programming interfaces (APIs), and the Microsoft Scripting Runtime object library.

In addition to using code to find files and folders programmatically, you can use Windows APIs to display Windows system dialog boxes that allow the user to specify a file or folder. This section describes how to display the system file dialog box and the folder dialog box that allows your users to browse for a specific folder. The following table briefly describes the code you need in order to do common file management tasks, including parsing a string representing a filename:

Task	Code Description
Check if a file exists	Len(Dir$(sFileName))
Check if a folder exists	Len(Dir$(sFileName, vbDirectory))
Get the application data folder	Use the Windows API SHGetSpecialFolderLocation.
Get the Temp folder on the user's system	Use the Windows API GetTempPath.
Remove a file	Use the built-in VBA function Kill(Pathname).
Parse through a filename and retrieve the filename without the path	In a custom function, use the built-in InStrRev function to return the position of the first occurrence of the backslash ("\") within the filename from the end of the filename string. The position returned by InStrRev is used in the built-in Mid$ function to return the characters of the filename string to the right of the backslash ("\").
Parse through a filename and retrieve the path	In a custom function similar to the one described immediately above, you use the built-in Mid$ function to return the characters of the filename string to the left of the backslash ("\").
Browse for a file	Import the code module modFlDlg.bas from the practice folders.
Browse for a folder	Import the code module modBrwse.bas from the practice folders.
(All tasks listed in this table.)	Use the FileSystemObject object in the Microsoft Scripting Runtime object library.

Sometimes you need to determine if a file exists before you attempt to open it or overwrite it. Using a combination of the Len and Dir functions built into the Visual Basic for Applications language, you can easily check if a file exists and take the appropriate action if it does or doesn't. In this scenario, you use the Dir function to return a string representing the name of a file that matches a specified file. You can also use the Dir function to return a string representing a directory, as the next example describes.

Start the Visual Basic Editor in any Office application and click Module on the Insert menu. Copy the following code and after placing the cursor in the procedure, press F5. If the folder "C:\Temp" or the file Test.txt does not exist, a message box indicating the file does not exist will be displayed. Change the filename or folder assigned to the variable sFileName to a file that exists on your system and a message indicating the file exists will be displayed.

Sub CheckIfFileExists() Dim sFileName As String sFileName = "C:\Temp\Test.txt" If Len(Dir$(sFileName)) Then MsgBox "File exists." Else MsgBox "File does not exist." End If End Sub

In this procedure, the first argument of the Dir function takes a string expression representing the name of the file being checked to see if it exists. If it isn't found, the Dir function returns a zero-length string (""). Instead of checking if the Dir function equals a zero-length string in the If…Then statement in the procedure, you use the Len function to determine the length of the string. The Len function returns a value of type Long representing the number of characters in a specified string. If Len returns a value greater than zero, the file exists.

The second argument, attributes, of the Dir function, is an optional constant or numeric expression whose combined value specifies file attributes. You can set the Attributes argument to one or a sum of the VbFileAttribute constants. In the following procedure, you set the attributes argument to the value vbDirectory, specifying that the Dir function should determine if the filename specified by the first argument of the Dir function can be found.

Start any Office application and in a standard code module in the Visual Basic Editor, copy the following code, place the cursor in the procedure, and press F5. If the folder "C:\TempFoo" does not exist, a message box indicating that the folder does not exist will be displayed. Change the folder assigned to the variable sPath to a folder that exists on your system and a message indicating the folder exists will be displayed.

Sub CheckIfFolderExists() Dim sPath As String sPath = "C:\TempFoo\" If Len(Dir$(sPath, vbDirectory)) Then MsgBox "Folder exists." Else MsgBox "Folder does not exist." MkDir sPath End If End Sub

Note the differences between this procedure and the preceding one. You set the variable sPath in this procedure to a folder name instead of a filename. And unlike in the preceding example, you specify the second argument of the Dir function. The last line in the If…Then…Else statement in the procedure above uses the Visual Basic for Applications language's built-in MkDir function. The MkDir function creates a new folder. If the folder didn't exist, you'd use the MkDir function to create it.

If you use the Windows Explorer on your machine to scroll through the Temp folder, you'll find that Windows applications have created a lot of files and folders to temporarily store data. You may find in your Visual Basic for Applications programs that you also need to create a temporary text file, for example, to store data while your program executes. At a later point in your program, you can clean up any temporary data files using the Kill function that's built into the Visual Basic for Applications language. In order to store data temporarily, you need to determine where the Temp folder is located on the user's machine. Although the Visual Basic for Applications language and Office offer no simple ways to retrieve the Temp folder path, both Windows and the Microsoft Scripting Runtime object library do. Later in this chapter, you'll learn how to use the Microsoft Scripting Runtime object library. This section tells you how to use the function GetTempPath that's built into Windows.

Windows provides a very extensive set of application programming interfaces, or APIs, that allows developers to create applications for the Windows platform. Although the Visual Basic for Applications language, along with objects, methods, and properties found in Office, provide ways to set and retrieve information, in some cases functionality isn't provided. Using Windows APIs, you can access a lot of the same functionality but often can go well beyond that offered by the Visual Basic for Applications language.

Public Declare Function GetTempPath Lib "kernel32" _ Alias "GetTempPathA" ( _ ByVal nBufferLength As Long, _ ByVal lpBuffer As String _ ) As Long Function GetTempDir() Dim sPath As String * 255, lTempPathLength As Long lTempPathLength = GetTempPath(255, sPath) GetTempDir = Mid$(sPath, 1, lTempPathLength) Debug.Print GetTempDir End Function

In a new standard code module in the Visual Basic Editor in any Office application, copy the public declaration of the function GetTempPath at the top of the code module. The code in the custom GetTempDir function calls the Windows API GetTempPath. Place the cursor directly in the GetTempDir function and press F5. The Temp folder path is printed to the Immediate window in the Visual Basic Editor. Whenever you want to retrieve the Temp folder path, call the GetTempDir function from anywhere in your code.

In Office 2000, files that are created or modified by the user, or created by default, are saved in the user's profile in the Application Data folder. The subfolders under the Application Data folder contain configuration preferences and options for each user. If the administrator for the user's machine set the user up with a roaming profile, information under the Application Data folder will roam as the user logs onto one Windows 2000 machine after another. Information under this folder is part of the user's profile. For example, on a typical installation of Office 2000, user-customized templates will be saved to a folder called Templates:

%windir%\Profiles\<username>\Application Data\Microsoft\Templates

The folder path above is typical of Windows NT4 or Windows 2000 users. If the user is using a Windows 95 or Windows 98 operating system, the folder will appear as:

%windir%\Application Data\Microsoft\Templates

The file Normal.dot that's used by Word is stored under this folder. If you click Save As on the File menu in PowerPoint and chose the Design Template (*.pot) file type in the Save as Type drop-down list, the 'Save in' folder changes to the folder specified above. If you also want to give your users the choice of saving files and data to a subfolder under the Application Data folder, you can use the Windows API SHGetSpecialFolderLocation.

Start the Visual Basic Editor of any Office application, right-click the Project Explorer, select the Import File command on the shortcut menu, and in the Import File dialog box, navigate to this book's practice folders. In the Chapter 4 practice folder, select the code module file SHGetFdr.bas. This module contains the declarations and custom function GetSpecialFolder that retrieves any special folder defined by Windows. Place the cursor in the Main procedure and press F5. All special folders defined by the user's operating system are printed to the Immediate window. In another code module, you can retrieve a specific folder by copying the following procedure to a new code module.

Sub GetUserTemplateFolder() Dim sAppDataPath As String, sUserTemplatePath As String sAppDataPath = GetSpecialFolder(CSIDL_APPDATA) sUserTemplatePath = sAppDataPath & "Microsoft\Templates\" Debug.Print sUserTemplatePath End Sub

This procedure retrieves the Templates folder that Office applications use to store user-created or user-modified Office templates. Before running this procedure, make sure you've imported the file SHGetFdr.bas from the Chapter 4 practice folder. The path to the Templates folder, similar to the paths specified above, will be printed to the Immediate window. In the file SHGetFdr.bas, you'll find a number of constants defined for each special folder. The constant names begin with the prefix "CSIDL_". Type each of the following lines in the Immediate window in Visual Basic Editor and press ENTER to retrieve the user's desktop folder and the user's My Documents folder.

?GetSpecialFolder(CSIDL_DESKTOP) ?GetSpecialFolder(CSIDL_PERSONAL)

You'll often find that your code will retrieve a filename that contains both the name of the file and the path to the file. In many cases, you may need to do one of the following with the filename:

• Strip off the path to the file to retrieve only the file's name
• Strip off the file's name to retrieve only the path
• Determine the file extension

Using Visual Basic code, you can write custom functions that take a filename and return only the necessary portion of the filename string. Using the new InStrRev function built into the Visual Basic for Applications language (it's not available in Office 97), you can retrieve the position of the backslash ("\") character, starting from the end of the string. Once you know this position, you can use the Mid$ function built into the Visual Basic for Applications language to retrieve the portion of the string you require.

The first argument of the InStrRev function specifies the string to be searched, while the second argument specifies the string to be searched for in the first argument. The procedure below, TestGetPathAndFileName, calls the three custom functions, GetFileName, GetFileExtension, and GetPath and prints the return values of the custom functions to the Immediate window.

Note that the only difference between the custom functions GetFileName and GetPath is the second and third argument of the Mid$ function. In the GetFileName function, you set the Start argument to the character position of the backslash plus one. You set the Start argument specified in the Mid$ function in the GetPath function to 1, but you set the Length argument to include all characters up to the position of the backslash.

Sub TestGetPathAndFileName() Debug.Print GetFileName("C:\Temp\12345Foo.txt") Debug.Print GetFileExtension("C:\Temp\12345Foo.txt") Debug.Print GetPath("C:\Temp\12345Foo.txt") End Sub Function GetFileName(sFileName As String) As String Dim iPosn As Integer iPosn = InStrRev(sFileName, "\") GetFileName = Mid$(sFileName, iPosn + 1) End Function Function GetFileExtension(sFileName As String) As String Dim iPosn As Integer iPosn = InStrRev(sFileName, ".") GetFileExtension = Mid$(sFileName, iPosn + 1) End Function Function GetPath(sFileName As String) As String Dim iPosn As Integer iPosn = InStrRev(sFileName, "\") GetPath = Mid$(sFileName, 1, iPosn - 1) End Function

The results printed to the Immediate window will be "12345Foo.txt", "txt", and "C:\Temp", respectively. If you want GetPath to return the path and include the backslash at the end of the path string, change the line setting GetPath to the following, which excludes the subtraction of one in the Mid$ function.

GetPath = Mid$(sFileName, 1, iPosn)

In Office 97, the built-in InStrRev function doesn't exist in the Visual Basic for Applications language. Therefore, in the custom function GetFileName, defined in the previous example, your code needs to iterate through each character of the filename starting from the right end of the filename string.

Function GetFileName97(sFileName As String) As String Dim sChar As String, i As Integer For i = Len(sFileName) To 1 Step -1 sChar = Mid$(sFileName, i, 1) If sChar = "\" Then GetFileName97 = Mid$(sFileName, i + 1) Exit For End If Next i End Function

When you find the first backslash ("\"), use the built-in Mid$ function to return the characters of the filename string to the right of the backslash ("\"). The GetFileName97 function will appear as listed above when typed into a code module in the Visual Basic Editor in Office 97. Type ?GetFileName97 ("C:\Temp\12345Foo.txt") in the Immediate window and the same result is printed. This custom function works the same in the Visual Basic Editor in Office 2000. You can easily modify this function to return the path and file extension.

In addition to using code to find files and folders programmatically, you can use Windows APIs to display Windows system dialog boxes that allow the user to specify a file or folder. Two common system dialog boxes are the File dialog box, which is used to open, save, or browse for files on your computer or computer network, and the Browse For Folder dialog box, which is used to specify a folder name. This section describes how to display to your users the Windows system File dialog box and Browse For Folder dialog box so they can locate a document or find a folder to store a document.

Starting with Windows 95 and Windows NT version 4.0, the Windows File dialog box provided user-interface features that are similar to the Windows Explorer. Windows 98 expands the functionality of the Windows File dialog box by providing a shortcut to the Desktop folder and letting the user increase the size of the dialog box by clicking at its bottom-right and dragging it to an appropriate size.

In Windows 2000, the Windows File dialog box looks very similar to the Office File dialog box, providing a bar (similar to the look and style of the Outlook bar in Outlook) of folder shortcuts at the left.

The main functionality of the Windows File dialog box is to enable the user to select a file to open or insert or to specify a filename and folder to save a file. You can display the Windows File dialog box by using one of the following two Windows application programming interfaces (API): GetOpenFileName and GetSaveFileName. These APIs return a valid file to the programmer that is fully qualified with the pathname. If no file is selected or specified, they return an empty string, indicating that the user clicked Cancel in the dialog box.

In the Project Explorer in the Visual Basic Editor for any Office application, right-click any VBA project and click Import File on the shortcut menu. In the Import File dialog box, change to the Chapter 4 practice folder, select the file modFlDlg.bas, and click Open.

The file modFlDlg.bas contains code that allows you to display the Windows system File dialog box, which is the same dialog box as the Import File dialog box you just used to import the module. In the Properties window of the Visual Basic Editor, the name of the imported standard module is modFileDialog, and it contains a user-defined type that you can use to set properties such as the caption in the title bar of the File dialog box. To use the code in the modFileDialog module, insert a new code module by clicking Module on the Insert menu and add the following procedure. After adding the procedure, place the cursor in the procedure and press F5 to run. The system File dialog box will be displayed.

Sub GetFileWithSystemFileDialog() Dim sFileName As String Dim udtFileDialog As FileDialog With udtFileDialog .CustomFilter = "Text Files (*.txt)" _ & Chr$(0) & "*.txt" & Chr$(0) _ & Chr$(0) .DefaultExt = "*.txt" .Title = "Browse" .InitialDir = "C:\" sFileName = modFileDialog _ .WinFileDialog(udtFileDialog, 1) End With If Len(sFileName) > 0 Then Debug.Print sFileName End If End Sub

You declare the variable udtFileDialog as the user-defined type FileDialog, which you declare as public in the modFileDialog module. For more information on user-defined types, see the online Visual Basic for Applications Help file in the Visual Basic Editor. You use the user-defined type to set the custom filters that are displayed in the Files of Type drop-down list in the File dialog box.

You then set the default extension to the filter extension that's to be initially displayed in the Files of Type drop-down list. The filter that's used when the procedure is executed is the Text Files type. You set the title bar caption of the File dialog box to the text "Browse." The InitDir specifies what folder is displayed when the dialog box is displayed. You can use any of the techniques described in this chapter to retrieve a folder path and set it to InitDir.

You then set the value of the string variable sFileName to the value returned by the function WinFileDialog, which displays the File dialog box. The function WinFileDialog is defined in the imported code module modFileDialog. The If…Then condition block determines whether Cancel is clicked in the File dialog box or a valid file is selected and Open is clicked. If a valid file is selected, its full path is printed to the Immediate window in the Visual Basic Editor. If Cancel is clicked in the File dialog box, the value of sFileName is an empty string.

File filter and extension The value of CustomFilter is a string containing pairs of a display string and extension(s), separated by a Null character, represented in code as Chr$(0). The entire string set to CustomFilter is terminated by two Null characters. For example:

"Text File" & Chr$(0) & "*.txt" & Chr$(0) & Chr$(0)

or

"Word Files" & Chr$(0) & "*.doc;*.dot" & Chr$(0) _ & "Text Files" & Chr$(0) & "*.txt" & Chr$(0) & Chr$(0)

The value set to DefaultExt is the default extension that will be added to the filename if the user doesn't specify one. The preceding procedure, GetFileWithSystemFileDialog, would add the file extension "txt" to the filename.

Displaying more than one file type in the same filter in the Files of Type drop-down list To display more than one file type in the Files of Type drop-down list, change the two lines setting the value of CustomFilter and DefaultExt to the following:

.CustomFilter = "Web Pages (*.htm; *.html)" & _ Chr$(0) & "*.htm;*.html" & Chr$(0) _ & Chr$(0) .DefaultExt = "*.htm; *.html"

Displaying more than one filter in the Files of Type drop-down list In order to display more than one filter in the Files of Type drop-down list, change the two lines setting the value of CustomFilter and DefaultExt to the following:

.CustomFilter = "Access Database (*.mdb)" & _ Chr$(0) & "*.mdb" & Chr$(0) & _ "PowerPoint Presentation (*.ppt)" & _ Chr$(0) & "*.ppt" & Chr$(0) & _ Chr$(0) .DefaultExt = "*.mdb"

The default extension value, DefaultExt, is set to the first file type *.mdb, though it could have been *.ppt.

In the Project Explorer in the Visual Basic Editor of any Office application, right-click any VBA project and click Import File on the shortcut menu. In the Import File dialog box, change to the Chapter 4 practice folder, select the file modBrwse.bas, and click Open.

The file modBrwse.bas contains code that allows you to display the Windows system Browse For Folder dialog box. In the Visual Basic Editor's Properties window, the imported standard module's name is modBrowseFolder. To use the code in the modBrowseFolder module, insert a new code module by clicking Module on the Insert menu and add the following procedure:

Sub GetFolderSpecifiedByUser() Dim sPath As String sPath = modBrowseFolder.BrowseForFolder If Len(sPath) > 0 Then Debug.Print sPath End If End Sub

The function BrowseForFolder in the modBrowseFolder module displays the Windows system Browse For Folder dialog box. The If…Then condition block determines whether Cancel is clicked in the Browse For Folder dialog box or a folder is selected and OK is clicked. If a folder is selected, its path is printed to the Immediate window in the Visual Basic Editor. If Cancel is clicked in the Browse For Folder dialog box, the value of sPath is an empty string.

When you click Open or Save As on the File menu in most Office applications, you see the Office File dialog box. Like the Windows File dialog box, it most commonly enables the user to select a file to open, to specify a filename and folder to save a file to, and to browse for a file to insert into a document.

As previously noted, Chapter 5 discusses how you can display the Office "Save As" File dialog box. This dialog box allows you to handle scenarios such as determining what filename a user specified in the Save As dialog box. If the document does not have certain properties set and the filename indicates the file will be saved to a specific folder location on a network, for example, you can cancel the document Save event. Chapter 5 provides the samples for this scenario in Word and Excel.

Remember that you can't display the File dialog box in PowerPoint, Access, or Outlook through code. However, you can still customize the Office File dialog box so that no matter when and in what application it's displayed, the user's experience will be the same. The one part of the Office File dialog box you can customize is the places bar at the left of the dialog box. The places bar is similar in look and style to the Outlook bar in Outlook.

The places bar commonly contains up to five positions that you use to display an icon and a caption that provide a shortcut to a folder on the user's machine, network share, or Web server share. By default, the places bar displays icons in order from top to bottom for the folders History, My Documents, Desktop, Favorites, and Web Folders.

Office uses the Windows Registry to determine if these shortcut icons appear on the places bar. Under the Windows Registry key, HKEY_CURRENT_ USER \Software\Microsoft\Office\9.0\Common\Open Find\Places, there are two subkeys: StandardPlaces and UserDefinedPlaces.

Each of the subkeys under StandardPlaces corresponds to the shortcut icons displayed by default in the Office File dialog box. The Publishing subkey corresponds to the Web Folders shortcut icon in the dialog box, and the Recent subkey corresponds to the History shortcut icon. If you want to display a user-defined place in the places bar, you need to complete the following steps. In short, user-defined places are displayed only if a position is available in the places bar and one or more StandardPlaces aren't visible.

1. To turn off the display of any of the default shortcut icons on the places bar, under the appropriate subkey under the StandardPlaces key, add a DWORD value to the subkey, name it Show, and then set its value to 0.

If the Show value already exists, change its value from 1 to 0. To insert a DWORD value, click New on the Edit menu in the Windows Registry and then click DWORD value on the submenu. You can edit the name of the value when the DWORD value is added, or you can right-click the value and click Rename on the shortcut menu. To change the value of the DWORD value, double-click it and type the appropriate value in the Value Data text box in the Edit DWORD Value dialog box.

2. Under the UserDefinedPlaces key, add a subkey with any name.
3. Add two string values, Name and Path, under the new subkey.
4. For the Name value, add a string that will be used as the caption for the user-defined place in the places bar. For the Path value, add a string that the user-defined place will navigate the user to in the Office File dialog box.

The following graphic shows the History, Favorites, and Web Folders turned off and three user-defined places displayed.

You can also specify two other DWORD values, Index and SortAscending, under the user-defined place subkey. Index indicates the position of the user-defined place in the places bar with respect to other user-defined places. For example, you can sort the places sequentially, regardless of the order of keys in the registry. If Index isn't specified, the user-defined places are sorted in the order of the keys in the registry. The registry key order is defined by the order in which the key is added under the UserDefinedPlaces key.

The SortAscending value determines whether the display of files in the Office File dialog box should be in alphabetical order or reverse alphabetical order. A value of 1 indicates alphabetical order and a value of 0 indicates reverse alphabetical order.

The following list explains the behaviors of the places bar in the Office File dialog box:

• If Name or Path doesn't exist, the place doesn't show up in the dialog box.
• If Path is defined but contains an empty string, it does show up in the dialog box, but clicking on the place doesn't change the folder view in the dialog box.
• If Name is defined but contains an empty string, the generic icon for a user-defined place is displayed but no caption is displayed. Clicking on the place still changes the folder view in the dialog box, as long as Path has a valid string.

The ShellExecute Windows application programming interface (API) allows your program to open any file. ShellExecute parses the filename passed to it and determines what the file string represents. The following list describes the common types of files that the file string represents, and which the ShellExecute API opens:

• Executable files such as Notepad.exe or any Office application
• Any document file on your computer or network share
• Any Web page or file over the Intranet or Internet
• URL (*.url) shortcuts
• Desktop (*.lnk) shortcuts
• E-mail name

The following LoadMiscFiles procedure calls the custom procedure LoadFile to load each of the different files specified. Because the first is an HTTP file, ShellExecute will launch whatever browser is registered to open Web pages. As you'll see in the description of the SaveAs method later in this chapter, you use ShellExecute to display saved Web pages from Word, Excel, or PowerPoint in the Web browser.

The procedure's second line passes the mailto syntax that's commonly used in Web pages to load your machine's default e-mail application and to open a new message. The third line launches Notepad, and the last line displays the Windows Explorer showing the contents of the Temp folder on the C drive.

Declare Function ShellExecute Lib "shell32.dll" _ Alias "ShellExecuteA" ( _ ByVal Hwnd As Long, _ ByVal lpOperation As String, _ ByVal lpFile As String, _ ByVal lpParameters As String, _ ByVal lpDirectory As String, _ ByVal nShowCmd As Long _ ) As Long Sub LoadFile(FileName As String) ShellExecute 0, "Open", FileName, "", "", 1 End Sub Sub LoadMiscFiles() LoadFile "http://www.microsoft.com" LoadFile "mailto:mspress@microsoft.com" LoadFile "notepad.exe" LoadFile "C:\Temp" End Sub

As you'll see in the description of the Print method later in this chapter, you can use ShellExecute to print files. The only difference from the code above is in the line ShellExecute in the LoadFile procedure, where the string "Open" is replaced with "Print."

What you've learned so far about finding files and folders through code has involved the use of Windows application programming interfaces (APIs) and built-in functions in the Visual Basic for Applications language, or both. Microsoft, however, also provides an object library that you can reference in your Visual Basic programs, and it provides objects, methods, and properties you can use to access the file system where your program is executed.

In the Visual Basic Editor, click References on the Tools menu to display the References dialog box. Scroll down the list of Available References and select the item Microsoft Scripting Runtime. Click OK to set a reference to the Microsoft Scripting Runtime object library.

The filename of the Microsoft Scripting Runtime object library is scrrun.dll, and it should be installed in the Windows system folder in a typical installation of Office 2000.

As you'll see in the following procedures, by using the FileSystemObject and other objects defined in the Microsoft Scripting Runtime object library, you can replace most of the file management tasks discussed earlier in this chapter. So when should you use one or the other? If you don't want to depend on the Microsoft Scripting Runtime object library, you can do most file management tasks using Windows APIs and built-in functions in the Visual Basic for Applications language.

If you only need to determine if a file or folder exists a few times in your code, it's not worth depending on the Microsoft Scripting Runtime object library. If your program does a lot of file management and if depending on the Microsoft Scripting Runtime object library isn't an issue for your solution, you'll find it's much easier to write and understand the code to handle file management tasks by using the FileSystemObject in the Microsoft Scripting Runtime object library.

This procedure is similar to using the combination of the built-in functions Len and Dir in the Visual Basic for Applications language. The main difference is in the expression evaluated in the If…Then statement. In the following procedure, the FileExists method of the FileSystemObject returns True if the specified file exists and False if it doesn't.

Sub CheckIfFileExists() Dim fileSystemObj As New Scripting.FileSystemObject Dim sFileName As String sFileName = "C:\Temp\Test.txt" If fileSystemObj.FileExists(sFileName) Then MsgBox "File exists." Else MsgBox "File does not exist." End If End Sub

This procedure is also similar to using the combination of the built-in functions Len and Dir in the Visual Basic for Applications language. As above, the main difference is in the expression evaluated in the If…Then statement. In the following procedure, the FolderExists method of the FileSystemObject returns True if the specified folder exists and False if it doesn't. In addition, you use the CreateFolder method in place of the MkDir function built into the Visual Basic for Applications language.

Sub CheckIfFolderExists() Dim fileSystemObj As New Scripting.FileSystemObject Dim sPath As String sPath = "C:\TempFoo\" If fileSystemObj.FolderExists(sPath) Then MsgBox "Folder exists." Else MsgBox "Folder does not exist." fileSystemObj.CreateFolder sPath End If End Sub

You can easily retrieve the Temp folder by using the GetSpecialFolder method on the FileSystemObject object and specifying the SpecialFolderConst constant value TemporaryFolder. Instead of having to use a module-level declaration and at least two procedure-level variable declarations when you use the GetTempFolder Windows API, you only need two lines when using the FileSystemObject. (The sTempPath doesn't need to be declared and set to the return value of the GetSpecialFolder method.) The Temp folder path in this procedure is printed to the Immediate window in the Visual Basic Editor.

Sub GetTempDirUsingFileSystemObject() Dim fileSystemObj As New Scripting.FileSystemObject Dim sTempPath As String sTempPath = fileSystemObj.GetSpecialFolder(TemporaryFolder) Debug.Print sTempPath End Sub

Unlike the Windows API SHGetSpecialFolderLocation described earlier in this chapter, the FileSystemObject object only lets you retrieve three special folders. They're defined as SpecialFolderConst constants: SystemFolder, TemporaryFolder, and WindowsFolder. In the previous procedure, change the value specified in the GetSpecialFolder method to either SystemFolder or WindowsFolder. In this case, using the Windows API SHGetSpecialFolderLocation is more robust because it allows you to return folders like the user's Desktop and Favorites folders.

Unlike using the built-in InStrRev function in the Visual Basic for Applications language, you can use the GetFileName, GetExtensionName, and GetParentFolderName methods on the FileSystemObject to return the filename without the path, the file extension, or the path without the filename, respectively. Instead of creating separate custom functions, you need only two lines of code in each case. The result of running each procedure below is printed to the Immediate window and the values will be "12345Foo.txt", "txt", and "C:\Temp," respectively.

Sub GetFileNameUsingFileSystemObject() Dim fileSystemObj As New Scripting.FileSystemObject Debug.Print fileSystemObj _ .GetFileName("C:\Temp\12345Foo.txt") End Sub Sub GetFileExtensionUsingFileSystemObject() Dim fileSystemObj As New Scripting.FileSystemObject Debug.Print fileSystemObj _ .GetExtensionName("C:\Temp\12345Foo.txt") End Sub Sub GetPathUsingFileSystemObject() Dim fileSystemObj As New Scripting.FileSystemObject Debug.Print fileSystemObj _ .GetParentFolderName("C:\Temp\12345Foo.txt") End Sub