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 "c" string
    Filter$ = "Excel Files(*.XL*)" & Chr$(0) & "*.XL*" & Chr$(0)
    Filter$ = Filter$ & "Text(*.txt)" & Chr$(0) & "*.TXT" & 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$ = "My File Open Dialog" & Chr$(0)

    ' If the user does not specify an extension, append TXT.
    DefExt$ = "TXT" & 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$ = "The file you chose was " + RTrim$(FileName$)
    Else
        Message$ = "No file was selected"
    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.
