Method 'FileDialog' of Object '_Application' failed 1

[josephwalter]

A button on one of my forms is supposed to open the File Open dialog box. It works fine on user's computer when I have Access installed (as part of Office XP Professional), but it doesn't work with just Access Runtime. User gets the error "Method 'FileDialog' of Object '_Application' failed."

Company policy will not allow me to keep a full version of Access on user's computer. What do I need to install in order to make my dialog box work? Below is the code I'm using:

Const USER_CLICKED_BUTTON As Integer = -1

Private Sub btnBrowse_Click()

    On Error GoTo Proc_Error

    ' These procedures references the Microsoft Office 10.0 Object Library.

    Dim objFileDialog As FileDialog
    Dim strFilePath As String
    
    strFilePath = "J:\path\to\company\files"

    Set objFileDialog = BuildCommonDialogBox(msoDialogType:=msoFileDialogOpen, _
        strTitleCaption:="Select a File", _
        strFileName:=strFilePath, _
        msoFileViewType:=msoFileDialogViewList, _
        strButtonCaption:="OK")
        
    With objFileDialog
        .Filters.Clear                              ' remove all file types
        .Filters.Add "Word Documents", "*.doc", 1   ' show only Word documents

        If .Show = USER_CLICKED_BUTTON Then
            ' remove the file path from the value returned by dialog box.
            ' put that file name in the FileName field.
            Me.FileName.Value = Replace(.SelectedItems.Item(Index:=1), strFilePath, "")
        End If
    End With
    
Proc_Exit:
    Exit Sub
    
Proc_Error:
    MsgBox Err.Description
    Resume Proc_Exit
    
End Sub

Public Function BuildCommonDialogBox(ByVal msoDialogType As MsoFileDialogType, _
    ByVal strTitleCaption As String, _
    ByVal strFileName As String, _
    ByVal msoFileViewType As MsoFileDialogView, _
    ByVal strButtonCaption As String, _
    Optional ByVal blnSelectMultipleFiles As Boolean = False) _
    As FileDialog
    
    ' This function references the Microsoft Office 10.0 Object Library.
    
        Dim obj As FileDialog
        
        Set obj = Application.FileDialog(DialogType:=msoDialogType)
        
        With obj
            .Title = strTitleCaption
            .InitialFileName = strFileName
            .InitialView = msoFileViewType
            .ButtonName = strButtonCaption
            .AllowMultiSelect = blnSelectMultipleFiles
        End With
        
        Set BuildCommonDialogBox = obj
    
End Function

 

[CajunCenturion]

One approach that you might want to consider is not to rely on the FileDialog method of the Application object given your situation. That method is simply a wrapper for the GetOpenFileName API which you can use directly.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[josephwalter]

Thank you, CajunCenturion. With your suggestion, I was able to hunt down some code that did the job. I've posted it here for others who may need it:

This went into it's own module:

Option Compare Database
Option Explicit

Public Type OPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    lpstrFilter As String
    lpstrCustomFilter As String
    nMaxCustomFilter As Long
    nFilterIndex As Long
    lpstrFile As String
    nMaxFile As Long
    lpstrFileTitle As String
    nMaxFileTitle As Long
    lpstrInitialDir As String
    lpstrTitle As String
    flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    lpstrDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Public Const OFN_FILEMUSTEXIST = &H1000
Public Const OFN_HIDEREADONLY = &H4
Public Const OFN_PATHMUSTEXIST = &H800

Public Declare Function GetOpenFileName Lib "comdlg32.dll" _
    Alias "GetOpenFileNameA" (lpofn As OPENFILENAME) As Long

Public Function PromptFileName(Title As String, Filter As String, InitialDir As String) As String
    Dim filebox As OPENFILENAME  ' open file dialog structure
    Dim fname As String          ' filename the user selected
    Dim result As Long           ' result of opening the dialog
    
    ' Configure how the dialog box will look
    With filebox
        ' Size of the structure.
        .lStructSize = Len(filebox)
        ' Handle to window opening the dialog.
        .hwndOwner = 0 'Me.Hwnd
        ' Handle to calling instance (not needed).
        .hInstance = 0
        
        ' File filters to make available: Access Databases and All Files
        '.lpstrFilter = "Access Databases (*.mdb)" & vbNullChar & "*.mdb" & _
        '    vbNullChar & "All Files (*.*)" & vbNullChar & "*.*" & _
        '    vbNullChar & vbNullChar
        .lpstrFilter = Filter
        
        '.lpstrCustomFilter is ignored -- unused string
        .nMaxCustomFilter = 0
        ' Default filter is the first one (Text Files, in this case).
        .nFilterIndex = 1
        ' No default filename.  Also make room for received
        ' path and filename of the user's selection.
        .lpstrFile = Space(256) & vbNullChar
        .nMaxFile = Len(.lpstrFile)
        ' Make room for filename of the user's selection.
        .lpstrFileTitle = Space(256) & vbNullChar
        .nMaxFileTitle = Len(.lpstrFileTitle)
        
        ' Initial directory is C:\.
        '.lpstrInitialDir = "C:\" & vbNullChar
        .lpstrInitialDir = InitialDir
        
        ' Title of file dialog.
        '.lpstrTitle = "Select a File" & vbNullChar
        .lpstrTitle = Title
        
        ' The path and file must exist; hide the read-only box.
        .flags = OFN_PATHMUSTEXIST Or OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY
        ' The rest of the options aren't needed.
        .nFileOffset = 0
        .nFileExtension = 0
        '.lpstrDefExt is ignored -- unused string
        .lCustData = 0
        .lpfnHook = 0
        '.lpTemplateName is ignored -- unused string
    End With
    
    ' Display the dialog box.
    result = GetOpenFileName(filebox)
    If result <> 0 Then
        ' Remove null space from the file name.
        fname = Left(filebox.lpstrFile, InStr(filebox.lpstrFile, vbNullChar) - 1)
        'Debug.Print "The selected file: "; fname
    Else
        fname = ""
    End If
    
    'return the string of the file name
    PromptFileName = fname
    
End Function

And here's the code behind my Browse button:

Private Sub btnBrowse_Click()

    On Error GoTo Proc_Error

    Dim str As String
    
    str = "J:\path\to\company\files\"

    str = PromptFileName("Select a File", "Microsoft Word (*.doc)" & vbNullChar & "*.doc" & vbNullChar, str)

    If str <> "" Then
        
        str = Right(str, Len(str) - LastInStr(str, "\"))
        Me.FileName.Value = str

    End If
    
Proc_Exit:
    Exit Sub
    
Proc_Error:
    MsgBox Err.Description
    Resume Proc_Exit
    
End Sub