How to show File Picker dialog in Access 2007?

Question

I want to show a dialog where the user can pick a file, click OK, and then the path to the file will be saved in the database.

I have just one problem, I can't figure out how tho show the dialog window. Do you?

Answer 1

You can use the WinAPI for that. Import

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

You also have to import the OPENFILENAME structure.

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter 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

Then you fill out the structure and call GetOpenFileName.

 Dim of As OPENFILENAME

of.lStructSize = Len(of)
of.hwndOwner = Access.hWndAccessApp
of.hInstance = vbNull
of.lpstrFilter = m_strFilter ' *.doc for example
of.nFilterIndex = 1
of.lpstrFile = String(257, 0)
of.nMaxFile = Len(of.lpstrFile) - 1
of.lpstrFileTitle = of.lpstrFile
of.nMaxFileTitle = of.nMaxFile
of.lpstrInitialDir = m_strDirectory ' Folder to start
of.lpstrTitle = m_strTitle ' Title of dialog window
of.Flags = 0

If GetOpenFileName(of) <> 0 Then
    filename = VBString(of.lpstrFile)
end if

Where VBString is a helper function to convert a null-terminated string.

Private Function VBString(str As String) As String
   Dim pos As Integer
   pos = InStr(1, str, Chr(0), vbTextCompare)
   VBString = Left(str, pos - 1)
End Function

Answer 2

Similar to @dwo's answer: How to display the Common 'File-Open' Dialog to Choose a File

Create a new module and paste the code in your new module.
In the above link there is also an example on how to use it.

Answer 3

Do not forget the fileDialog object, easy, allows multiple selection, fileOpen, folder selection, etc, to be used this way:

Dim m_fileList As FileDialog, _
    i as long

'my choice here: pick up multiple files. Other options are available'
Set m_fileList = Application.FileDialog(msoFileDialogFilePicker)
m_fileList.AllowMultiSelect = True
m_fileList.InitialFileName = myDefaultFolder
m_fileList.InitialView = msoFileDialogViewDetails
m_fileList.Title = yourTitle
m_fileList.InitialFileName = myDefaultFileName

'we can add a file extension filter serie'
'm_fileList.filters(i) = ...'

If m_fileList.Show = -1 And m_fileList.SelectedItems.Count > 0 Then
    For i = 1 To m_fileList.SelectedItems.Count
        debug.print m_fileList.selectedItems(i).
    Next i
End If

Answer 4

This code did it.