Teedee Access VBA Teedee 2010
File Dialog (API)
Home Tutorial Code Snippets

File Dialog - Using API Call

The Common Dialog comdlg32.dll can be accessed via API calls. This file should be located in the System32 folder. For 64 bit systems refer to the Microsoft Knowledgebase reference opposite. No references need to set but check that comdlg32 is present.

Requirements

comdlg32.dll is required & must be registered. More info here: http://support.microsoft.com/kb/161286/e n-us

Code Snippet

Option Compare Database Option Explicit 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     lpstrInitialDirectory 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
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _ "GetOpenFileNameA" (pOpenFileName As OPENFILENAME) As Long Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _ "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _ "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long Private Const BIF_RETURNONLYFSDIRS = &H1 Public Function GetFile(sDefFileType As String, AllowMultiSelect As Boolean, _ Optional Title As String, Optional InitialPath As String) As String     'Returns the name of a selected file.     'Requires MS Office 11 Object Library         Dim dlg As FileDialog     Dim sFilter As String     Dim f As Variant     Dim sList As String         Set dlg = Application.FileDialog(msoFileDialogOpen)     With dlg         .AllowMultiSelect = AllowMultiSelect         .InitialView = msoFileDialogViewDetails         If IsMissing(Title) Then             .Title = "Select file to open"         Else             .Title = Title         End If         If Not IsMissing(InitialPath) Then .InitialFileName = InitialPath                 Select Case sDefFileType         Case "access", "access databases"             .Filters.Add "Access Databases", "*.mdb;*.mde"         Case "access workgroup"             .Filters.Add "Access Workgroup", "*.mdw;*.mda"         Case "graphics"             .Filters.Add "JPEG", "*.jpg; *.jpeg"             .Filters.Add "Bitmaps", "*.bmp"             .Filters.Add "GIF", "*.gif"         Case "jpeg", "jpg"             .Filters.Add "JPEG", "*.jpg; *.jpeg"         Case "gif"             .Filters.Add "GIF", "*.gif"         Case "bitmap", "bmp"             .Filters.Add "Bitmaps", "*.bmp"         Case "excel"             .Filters.Add "Excel Files", "*.xls"         Case "text"             .Filters.Add "Text Files", "*.txt"         Case "dll"             .Filters.Add "DLLs", "*.dll"         Case "olb"             .Filters.Add "Olb Files", "*.olb"         Case "tlb"             .Filters.Add "Tlb Files (Type Libraries)", "*.tlb"         Case "ocx"             .Filters.Add "OCX Files", "*.ocx"         End Select         .Filters.Add "All Files", "*.*"         dlg.FilterIndex = 0         If dlg.Show Then                     sList = ""             For Each f In .SelectedItems                 If sList = "" Then                     sList = f                 Else                     sList = sList & ";" & f                 End If             Next             GetFile = sList         Else             GetFile = ""         End If     End With End Function
The code from BOTH these boxes should be placed in a single module.

Note

In VBA code you can split a line of code to multiple lines using the underscore. For example the following is a single line of code distributed over three lines Private Declare Function SHBrowseForFolder Lib _ "shell32.dll" Alias "SHBrowseForFolderA" (lpBrowseInfo As _ BROWSEINFO) As Long