Microsoft KB Archive/112394

XL5: Using the Windows OpenFile Dialog Box Article ID: Q112394 Revision Date: 13-SEP-1996

The information in this article applies to:


 * Microsoft Excel for Windows, version 5.0, 7.0

SUMMARY This article describes how to access the OpenFile dialog box using a Visual Basic for Applications procedure. The OpenFile dialog box can be used to prompt the user for the name of a file. This article assumes that you are familiar with Visual Basic for Applications and with the programming tools provided with Microsoft Excel.

MORE INFORMATION In Microsoft Excel, you can access the OpenFile common dialog box by using Windows GetOpenFileName application programming interface (API) function. This function creates a system-defined dialog box, familiar throughout Windows, that makes it possible for the user to select a file to open. This function will return a valid file name to the programmer that is fully qualified with the path name. Using this function will simplify programming issues for the developer. A developer can customize the way the system will handle specific situations, such as specifying that the file must exist when the user wants to save a file, through the use of flags. Additionally, multiple files can be selected and returned using this function (which is not true of the built-in function Application.GetOpenFilename, which will only return a single file name). The OpenFile common dialog routines are stored in a file called COMMDLG.DLL, which is supplied with Microsoft Windows versions 3.1 and later. Note: Microsoft Excel versions 5.0 and 7.0 do not use COMMDLG.DLL–they use SDM.DLL. This may result in minor differences in behavior.

Visual Basic Procedure Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided ‘as is’ and Microsoft does not guarantee that they can be used in all situations. While Microsoft AnswerPoint Engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400. To use the code below, paste the function and declarations into a Visual Basic module. You may have some Windows API functions defined in an existing Microsoft Excel module; therefore, your declarations may be duplicates. If you receive a duplicate procedure name error, remove the Declare statement from your code or comment it out. The code supplied below is functional, however as a better programming practice, these API calls should be declared with explicit return types for clarity instead of using the shortcuts (which may not work in the future). For example:

Declare Function GetOpenFileName Lib…. As Integer Declare Function lstrcpy Lib…. As Long

’——————————————————-

’ Global Declaration Section

’——————————————————-

Option Explicit

Type tagOPENFILENAME lStructSize As Long hwndOwner As Integer hInstance As Integer lpstrFilter As Long lpstrCustomFilter As Long nMaxCustFilter As Long nFilterIndex As Long lpstrFile As Long nMaxFile As Long lpstrFileTitle As Long nMaxFileTitle As Long lpstrInitialDir As Long lpstrTitle As Long Flags As Long nFileOffset As Integer nFileExtension As Integer lpstrDefExt As Long lCustData As Long lpfnHook As Long lpTemplateName As Long

End Type

Declare Function GetOpenFileName% Lib “COMMDLG.DLL” (OPENFILENAME As tagOPENFILENAME) Declare Function lstrcpy& Lib “KERNEL” (ByVal lpDestString As Any, ByVal lpSourceString As Any) Declare Function GetModuleHandle Lib “KERNEL” (ByVal App As String) As Integer

Dim OPENFILENAME As tagOPENFILENAME

Global Const OFN_READONLY = &h1

Global Const OFN_OVERWRITEPROMPT = &h2

Global Const OFN_HIDEREADONLY = &h4

Global Const OFN_NOCHANGEDIR = &h8

Global Const OFN_SHOWHELP = &h10

Global Const OFN_ENABLEHOOK = &h20

Global Const OFN_ENABLETEMPLATE = &h40

Global Const OFN_ENABLETEMPLATEHANDLE = &h80

Global Const OFN_NOVALIDATE = &h100

Global Const OFN_ALLOWMULTISELECT = &h200

Global Const OFN_EXTENSIONDIFFERENT = &h400

Global Const OFN_PATHMUSTEXIST = &h800

Global Const OFN_FILEMUSTEXIST = &h1000

Global Const OFN_CREATEPROMPT = &h2000

Global Const OFN_SHAREAWARE = &h4000

Global Const OFN_NOREADONLYRETURN = &h8000

Global Const OFN_NOTESTFILECREATE = &h10000

Global Const OFN_SHAREFALLTHROUGH = 2

Global Const OFN_SHARENOWARN = 1

Global Const OFN_SHAREWARN = 0

’——————————————————-

’ Open Common Dialog Function

’——————————————————-

Function OpenCommDlg Dim Message$, Filter$, FileName$, FileTitle$ , DefExt$ Dim Title$, szCurDir$ , APIResults%

' Define the filter string and allocate space in the &quot;c&quot; string Filter$ = &quot;Excel Files(*.XL*)&quot; & Chr$(0) & &quot;*.XL*&quot; & Chr$(0) Filter$ = Filter$ & &quot;Text(*.txt)&quot; & Chr$(0) & &quot;*.TXT&quot; & Chr$(0) Filter$ = Filter$ & Chr$(0)

' Allocate string space for the returned strings. FileName$ = Chr$(0) & Space$(255) & Chr$(0) FileTitle$ = Space$(255) & Chr$(0)

' Give the dialog a caption title. Title$ = &quot;My File Open Dialog&quot; & Chr$(0)

' If the user does not specify an extension, append TXT. DefExt$ = &quot;TXT&quot; & Chr$(0)

' Set up the defualt directory szCurDir$ = CurDir$ & Chr$(0)

' Set up the data structure before you call the GetOpenFileName

OPENFILENAME.lStructSize = Len(OPENFILENAME) OPENFILENAME.hwndOwner = GetModuleHandle(Application) OPENFILENAME.lpstrFilter = lstrcpy(Filter$, Filter$) OPENFILENAME.nFilterIndex = 1 OPENFILENAME.lpstrFile = lstrcpy(FileName$, FileName$) OPENFILENAME.nMaxFile = Len(FileName$) OPENFILENAME.lpstrFileTitle = lstrcpy(FileTitle$, FileTitle$) OPENFILENAME.nMaxFileTitle = Len(FileTitle$) OPENFILENAME.lpstrTitle = lstrcpy(Title$, Title$) OPENFILENAME.Flags = OFN_FILEMUSTEXIST Or OFN_READONLY OPENFILENAME.lpstrDefExt = lstrcpy(DefExt$, DefExt$) OPENFILENAME.hInstance = 0 OPENFILENAME.lpstrCustomFilter = 0 OPENFILENAME.nMaxCustFilter = 0 OPENFILENAME.lpstrInitialDir = lstrcpy(szCurDir$, szCurDir$) OPENFILENAME.nFileOffset = 0 OPENFILENAME.nFileExtension = 0 OPENFILENAME.lCustData = 0 OPENFILENAME.lpfnHook = 0 OPENFILENAME.lpTemplateName = 0

' This will pass the desired data structure to the Windows API, ' which will in turn use it to display the Open Dialog form.

APIResults% = GetOpenFileName(OPENFILENAME)

If APIResults% <> 0 Then

' Note that FileName$ will have an embedded Chr$(0) at the ' end. You may want to strip this character from the string. Message$ = &quot;The file you chose was &quot; + RTrim$(FileName$) Else Message$ = &quot;No file was selected&quot; End If

MsgBox Message$

' Return the file selected OpenCommDlg = RTrim$(FileName$) End Function

This function can be called as shown in the example below.

Sub OpenCommDlgTest Dim FileSelected ’ Calls the OpenCommDlg function (listed above) and places ’ return value in FileSelected variable FileSelected = OpenCommDlg MsgBox FileSelected

End Sub

REFERENCES “Microsoft Windows Software development Kit Programmer’s Reference,” Volume 2: Functions, version 3.1

KBCategory: kbprg kbcode KBSubcategory:

Additional reference words: 5.00 COMMDLG.DLL linked listbox list box

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1996.