The Office File Dialog Object

To allow the maximum amount of user choice, combined with convenience, I like to put one or two folder selection command buttons on a database's main menu, for selecting folders that will be used throughout the database. In the sample database for this chapter, Files and Folders.accdb, for example, the main menu has a section with two sets of controls for selecting a folder; one has a command button that pops up a Folder Picker dialog for selecting the Input Documents folder (used for storing documents to be loaded into Attachment fields or textboxes on forms), and the other opens an Output Documents Folder Picker for selecting the folder where files saved from attachments are to be stored. After a folder is selected, its name is displayed in the textbox under the command button. Figure 9.1 shows a main menu with these options.

A main menu with a Backup button and button/textbox controls for selecting an Input Documents path and an Output Documents path for use in the database.

S Main Menu

X

j Files and Foiders 1

(2

Browse Contacts

0

Send EMails to Contacts

J Load Data from Text Files

Load Attachments r Save Attachments

1

*

J Backup

3

Exit

[ Input Documents Path... ]

D:\Documents\Contact Documents

[ Output Documents Path... ]

D:\Documents\Saved Attachments

The Input Documents Path and Output Documents Path buttons run procedures that create a FileDialog object. FileDialog objects can be created as a File Picker, or a Folder Picker dialog; in this case the msoFileDialogFolderPicker named constant is used when creating the dialog, to make it a Folder Picker dialog:

Private Sub cmdInputDocsPath_Click()

On Error GoTo ErrorHandler

Create a FileDialog object as a Folder Picker dialog box.

Set fd = Application.FileDialog(msoFileDialogFolderPicker) Set txt = Me![txtInputDocsPath] strPath = GetInputDocsPath()

With fd

.title = "Browse for folder where input documents " _

& "are stored" .ButtonName = "Select"

.InitialView = msoFileDialogViewDetails .InitialFileName = strPath If .Show = -1 Then txt.Value = CStr(fd.SelectedItems.Item(1))

Else

Debug.Print "User pressed Cancel" End If End With

On Error Resume Next

DoCmd.RunCommand acCmdSaveRecord

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

Private Sub cmdOutputDocsPath_Click()

On Error GoTo ErrorHandler

Create a FileDialog object as a Folder Picker dialog box.

Set fd = Application.FileDialog(msoFileDialogFolderPicker) Set txt = Me![txtOutputDocsPath] strPath = GetOutputDocsPath()

With fd

.title = "Browse for folder where saved documents " _

& "should be stored" .ButtonName = "Select"

.InitialView = msoFileDialogViewDetails .InitialFileName = strPath If .Show = -1 Then txt.Value = CStr(fd.SelectedItems.Item(1)) Else

Debug.Print "User pressed Cancel" End If End With

On Error Resume Next

DoCmd.RunCommand acCmdSaveRecord

ErrorHandlerExit: Exit Sub

ErrorHandler:

& "; Description: " & Err.Description Resume ErrorHandlerExit

End Sub

In the cmdInputDocsPath_Click event procedure, the GetInputDocsPath() function is used to get the saved Input Documents path value from tblInfo (if there is one); otherwise the default Documents folder is opened. The user can select another path, or accept the default path; the value selected from the dialog is saved to the txtInputDocsPath textbox on the form, which is bound to the InputDocsPath field in tblInfo. The cmdOutputDocsPath_Click event procedure stores the selected template path to txtOutputDocsPath, which is stored in the OutputDocsPath field in tblInfo.

I use a tblInfo table in most of my databases to store data that is needed throughout the database, such as path information. Although you can use global variables for this purpose, they won't persist from one session to another, and it isn't easy to examine their values, so I prefer to store these values in a table.

The custom Input and Output Documents paths stored in tblInfo are picked up wherever needed in the database, using the GetInputDocsPath() and GetOutputDocsPath() functions, listed next:

Public Function GetInputDocsPath() As String On Error GoTo ErrorHandler Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("tblInfo") rst.MoveFirst strPath = Nz(rst![InputDocsPath])

Add a terminating backslash, if the path doesn't have one.

If Len(strPath) > 1 And Right(strPath, 1) <> Then

GetInputDocsPath = strPath & "\" Else

GetInputDocsPath = strPath End If rst.Close

ErrorHandlerExit: Exit Function

ErrorHandler:

MsgBox "Error No: " & Err.Number & "; Description: " & _

Err.Description Resume ErrorHandlerExit

End Function

Public Function GetOutputDocsPath() As String On Error GoTo ErrorHandler Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("tblInfo") rst.MoveFirst strPath = Nz(rst![OutputDocsPath])

Add a terminating backslash, if the path doesn't have one.

If Len(strPath) > 1 And Right(strPath, 1) <> Then

GetOutputDocsPath = strPath & "\" Else

GetOutputDocsPath = strPath End If rst.Close

ErrorHandlerExit: Exit Function

ErrorHandler:

MsgBox "Error No: " & Err.Number & "; Description: " & _

Err.Description Resume ErrorHandlerExit

End Function

Figure 9.2 shows the Folder Picker dialog for selecting a custom Output Documents path.

To use the FileDialog object in your code, you need to set a reference to the Office object library; that reference is not set by default in a newly created Access 2007 database. Figure 9.3 shows the Office 12.0 reference being checked in the References dialog for the Files and Folders database.

Selecting a custom folder for storing documents in a database.

ii>] Browse for folder where saved documents should he stored

m

• Htten 1 'it.1'in. , Documents .

•VI Search

P -1

1 1

% OrganËe *

Viewi ~ ft Mew Foldei

--■ !

Name Date niodified

Favorite Links ¡k Computer Computer S] Desktop E. Desktop 1^1) Documents Qi Network Shortcuts [J Recent | $ Recent Places ® Contact Letters \£ Startup Ç} Office Book

Fofders

Name Date niodified

I f ! Pelaez f ' Peisonal jj Pictures {I Piogress Reports ¿1 Q&A Mail Messages ¿I Reports

Roma it and Susan iïjr Saved Attachments [¿j Scieen Shots {^SharePoint Drafts I ¿1 Sy niantec ! ¿1 Tlmeslieets }_ : Toshiba f TurboTax |P Videos

Ijj Vista Bug Reports I f Web Site

Folder name: Saved Attachments

FIGURE 9.3

Setting a reference to the Office 12.0 object library.

References - Files and Folders

Available References:

U Microsoft MIMEEDIT Type Library 1,0 [ : Microsoft Office 12 Authorization Control 1.0 Type Lit icrosoft Office [^Microsoft Office

□ Microsoft Office

□ Microsoft Office

□ Microsoft Office U Microsoft Office

□ Microsoft Office

□ Microsoft Office

□ Microsoft Office

□ Microsoft Office Microsoft Office m

Document Imaging 12.0 Type Library Euro Converter Object Library InfoPath Designer 2,0 Type Library Outlook View Control Runtime 1,0 Type Library SharePoint Designer 12.0 Internal Lit SharePoint Designer 12.0 Page Obje SharePoint Designer 12.0 Web Authc SharePoint Designer 12.0 Web Objec Soap Type Library v3.0 Soap Winlnet Connector Tvoe Librar

Priority

Help

Microsoft Office 12,0 Object Library

Location: C:\Program Files\Common Files\Microsoft 5hared\OFFICE12\M Language: Standard

The FileDialog object has several useful methods and properties:

■ Filters.Add — Lets you specify the filter(s) for displaying files, such as fd.Filters.Add "Word documents", "*.doc"

■ InitialFileName — The default file name, for a File Picker dialog; the default path for a Folder Picker dialog

■ ButtonName — The button caption (in case you want something other than "Select").

■ DialogType — A selection of File Picker, Folder Picker, Open, or Save As dialog type (from the MsoFileDialogType enum, which can be viewed in the Object Browser).

■ InitialView — Lets you set the view for the dialog (Details, Large Icons, Preview, and so on). Look at the MsoFileDialogView enum in the Object Browser for the full selection.

■ AllowMultiSelect — If set to True, lets users select multiple files in the dialog (for File Picker dialogs only).

Figure 9.4 shows the MsoFileDialogView enum, with all the options for setting the view for the dialog.

FIGURE 9.4

Viewing the MsoFileDialogView enum in the Object Browser.

Object Browser

Search Results

Library

1 Classes

I Members of'MsoFileDialogView'

tap MsoFarEastLineBreakLanguaç

*

m

msoFileDialogViewDetails

Ici5 MsoFeaturelnstall

m

msoFileDialogViewLargelcons

dp MsoFileDialogType

m

msoFileDialogViewList

lall MsoFileDialogView

©

msoFileDialogViewPreview

i^P MsoFileNewAction

m

msoFileDialogViewProperties

MsoFileNewSection

III

m

msoFileDialogViewSmalllcons

MsoFiNType

Q

msoFileDialogViewThumbnail

MsoFilterComparison

ÜD

msoFileDialogViewTiles

¿P MsoFilterConjunction

LU

msoFileDialogViewWebView

¡¿p MsoFlipCmd

-

Enum MsoFileDialogView Member ol Office

Enum MsoFileDialogView Member ol Office

0 0

Post a comment