Pick the Perfect Location with Get SaveAsFilename

The yin of GetOpenFilename's yang is the GetSaveAsFilename method. This method presents the user with the familiar looking Save As dialog box found in most Windows applications (see Figure 5.4).

As with GetOpenFilename, GetSaveAsFilename merely allows users to indicate a filename and a location to which to save a file without actually saving it. The syntax of GetSaveAsFilename is shown here:

Application.GetSaveAsFilename([InitialFilename], _

[FileFilter], [FilterIndex], [Title], [ButtonText])

The parameters to GetSaveAsFilename are as follows.

InitialFileName This parameter is an optional variant parameter that should be in the format of a string that suggests a filename. The default is the active workbook's name. You can set this to a zero-length string ("") if you do not want to suggest an initial filename.

Figure 5.4

The functionality of the Save As dialog box can be accessed using the GetSaveAsFilename method.

Figure 5.4

The functionality of the Save As dialog box can be accessed using the GetSaveAsFilename method.


FileFilter This parameter is an optional variant parameter that should be in the format of a string that instructs the Save As dialog box to display only files that match the filter supplied. The default is "All Files (*.*),*.*".

Filterlndex This is an optional variant that can be used when multiple filters are supplied as a file filter. The Filterlndex should be a number that specifies which of the parameters is the default filter. By default, the first filter is used.

Title This is an optional variant that should be a string. Whatever you pass as the Title will be displayed in the title bar of the dialog box. By default, the text Save As is displayed.

ButtonText This optional parameter is applicable to Macintosh computers only.

The GetSaveAsFilename method can be used simply; Listing 5.7 shows an example.

Listing 5.7: Basic Use of GetSaveAsFilename

Sub SimpleGetSaveAsFilename() Dim sFile As String Dim lResponse As Long Dim sMsg As String

Do sFile = Application.GetSaveAsFilename sMsg = "You chose: " & sFile & ". Keep experimenting?" lResponse = MsgBox(sMsg, vbYesNo) Loop While lResponse = vbYes End Sub

In Listing 5.7 you have a simple procedure that you can use to experiment with GetSaveAsFilename. Inside a Do.. .Loop, you assign the return value of GetSaveAsFilename to a string variable named sFile. Then you use the MsgBox function to display the full filename that you provided and ask if you want to keep experimenting. If you click Yes, then the loop terminates. Note that GetSaveAsFilename doesn't actually do anything with the filename you provide.

Although you could use a simple call to GetSaveAsFilename to have the user supply a full name, or filename and path, for a file, it is not very safe to do so because two common conditions could cause a run-time error. The first condition is that a workbook with the given name is already open and you can't save a workbook with the same name as another open workbook. The second condition is that a workbook with the given name already exists at the given location. Excel would prompt the user whether or not they want to save the file anyway in this scenario. The problem is that if the user decides not to save the file, a run-time error occurs. As solving these problems is more related to the Workbook object, I'll cover the details of handling these two scenarios in the next chapter, which covers the Workbook object. For now, I'll lay a little more foundation.

Breaking Down Filenames

You frequently need to separate the path component from the actual filename when you're working with files. Both GetOpenFilename and GetSaveAsFilename return full filenames, meaning you get the storage path and the filename. For example, if you had a workbook named MyWorkbook.xls stored in the folder C:\SomeFolder, the full filename would be C:\SomeFolder\MyWorkbook.xls. Listing 5.8 presents a few procedures that work together to isolate the path and filename from a full filename.

NOTE This section does not deal directly with the Application object per se. It is more of a value-added section for dealing with the return values of two of the Application object's most frequently used methods.

Listing 5.8: Breaking Down Filenames into path and filename components

' A simple procedure for testing the ' BreakdownName procedure Sub TestBreakdownName() Dim sPath As String Dim sName As String Dim sFileName As String Dim sMsg As String sFileName = Application.GetSaveAsFilename BreakdownName sFileName, sName, sPath sMsg = "The file name is: " & sName & vbCrLf sMsg = sMsg & "The path is: " & sPath MsgBox sMsg, vbOKOnly End Sub

Function GetShortName(sLongName As String) As String Dim sPath As String Dim sShortName As String

BreakdownName sLongName, sShortName, sPath

GetShortName = sShortName End Function

Sub BreakdownName(sFu11Name As String, _

ByRef sName As String, _ ByRef sPath As String)

Dim nPos As Integer

' Find out where the filename begins nPos = FileNamePosition(sFullName)

If nPos > 0 Then sName = Right(sFu11Name, Len(sFullName) - nPos)


'Invalid sFullName - don't change anything End If End Sub

' Returns the position or index of the first ' character of the filename given a full name ' A full name consists of a path and a filename ' Ex. FileNamePosition("C:\Testing\Test.txt") = 11 Function FileNamePosition(sFullName As String) As Integer Dim bFound As Boolean Dim nPosition As Integer bFound = False nPosition = Len(sFullName)

Do While bFound = False

' Make sure we were not dealt a ' zero-length string If nPosition = 0 Then Exit Do

' We are looking for the first "\" ' from the right.

If Mid(sFullName, nPosition, 1) = "\" Then bFound = True


' Working right to left nPosition = nPosition - 1 End If


If bFound = False Then FileNamePosition = 0


FileNamePosition = nPosition End If End Function

In addition to providing you with a useful way to isolate path- and filename components from a full filename, Listing 5.8 includes one brand new concept, which may not be apparent at first glanceā€”output parameters. Take a look at the declaration of the BreakdownName subroutine.

Sub BreakdownName(sFullName As String, _

ByRef sName As String, _ ByRef sPath As String)

See the ByRef keyword in this declaration? This keyword indicates that when/if the Breakdown-Name function modifies these parameters, the procedure that called BreakdownName will see the changes made to the variables.

Listing 5.8 uses a simple procedure to test the BreakdownName procedure. You simply use the Get-SaveAsFilename method to obtain a full filename and pass the filename to the BreakdownName procedure along with two empty variables, sName and sPath. BreakdownName uses the FileNamePosition function to locate the beginning of the filename component. Once you know that, it is simple to break the name down into the path- and filename components using the VBA Left and Right functions. Both Left and Right take a string variable and return the specified number of characters from either the left or the right respectively. In the following screenshots, you can see how I employed the BreakdownName procedure to figure out the path and filename of the file returned from GetSaveAsFilename.

0 0


Post a comment