First of all, the "CommonDialog Class" doesn't even work on the 32-bit version of Office. It gives the same OleDb error. As one commenter notes, this is not the kind of control you should use. And while there may be another ActiveX control that you can use, there really is no guarantee that it will be available on all the machines on which you want to deploy your database. My developer block has Visual Studio 6, VS 2008, and VS 2010, in addition to Office and other programs, all of which provide ActiveX DLLs that cannot be expected from a regular user. In addition, many of these libraries are not distributed, or create unique installation barriers that may just not be worth the trouble.
To date, the easiest and most universal solution is to call the "Open" dialog box from the Windows API. . It is located in comdlg32.dll, which is available for every version of Windows, and you can target it and does not impose any dependencies on comdlg32.ocx. It also provides better performance than using an ActiveX control because it does not require an additional module to load into memory.
The required code is also not too complicated. You need to provide a declaration for the GetOpenFileName function, which creates the Open dialog box. It takes one parameter, an instance of the OPENFILENAME structure, which contains the information used to initialize the dialog box, as well as obtaining the path to the file selected by the user. Therefore, you also need to provide a declaration of this structure. The code in VBA will look something like this:
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 Private Declare Function GetOpenFileName Lib "comdlg32.dll" _ Alias "GetOpenFileNameA" (ByRef lpofn As OPENFILENAME) As Long
There are also several constants that you can pass as flags to customize the behavior of the dialog. For completeness, here is the full list:
Private Const OFN_ALLOWMULTISELECT As Long = &H200 Private Const OFN_CREATEPROMPT As Long = &H2000 Private Const OFN_ENABLEHOOK As Long = &H20 Private Const OFN_ENABLETEMPLATE As Long = &H40 Private Const OFN_ENABLETEMPLATEHANDLE As Long = &H80 Private Const OFN_EXPLORER As Long = &H80000 Private Const OFN_EXTENSIONDIFFERENT As Long = &H400 Private Const OFN_FILEMUSTEXIST As Long = &H1000 Private Const OFN_HIDEREADONLY As Long = &H4 Private Const OFN_LONGNAMES As Long = &H200000 Private Const OFN_NOCHANGEDIR As Long = &H8 Private Const OFN_NODEREFERENCELINKS As Long = &H100000 Private Const OFN_NOLONGNAMES As Long = &H40000 Private Const OFN_NONETWORKBUTTON As Long = &H20000 Private Const OFN_NOREADONLYRETURN As Long = &H8000& Private Const OFN_NOTESTFILECREATE As Long = &H10000 Private Const OFN_NOVALIDATE As Long = &H100 Private Const OFN_OVERWRITEPROMPT As Long = &H2 Private Const OFN_PATHMUSTEXIST As Long = &H800 Private Const OFN_READONLY As Long = &H1 Private Const OFN_SHAREAWARE As Long = &H4000 Private Const OFN_SHAREFALLTHROUGH As Long = 2 Private Const OFN_SHAREWARN As Long = 0 Private Const OFN_SHARENOWARN As Long = 1 Private Const OFN_SHOWHELP As Long = &H10 Private Const OFS_MAXPATHNAME As Long = 260
And for convenience, I wrapped this whole mess inside a helper function that you can call from VBA. It takes as parameters those properties that you most often need to set for an open file dialog, process a call to the Windows API itself, and then return either the full path to the file selected by the user or an empty string ( vbNullString ) if the user clicked the Cancel button. You can check the return value in the calling code to determine which course of action to take.
'This function shows the Windows Open File dialog with the specified ' parameters, and either returns the full path to the selected file, ' or an empty string if the user cancels. Public Function OpenFile(ByVal Title As String, ByVal Filter As String, _ ByVal FilterIndex As Integer, ByVal StartPath As String, _ Optional OwnerForm As Form = Nothing) As String 'Create and populate an OPENFILENAME structure 'using the specified parameters Dim ofn As OPENFILENAME With ofn .lStructSize = Len(ofn) If OwnerForm Is Nothing Then .hwndOwner = 0 Else .hwndOwner = OwnerForm.Hwnd End If .lpstrFilter = Filter .nFilterIndex = FilterIndex .lpstrFile = Space$(1024) & vbNullChar & vbNullChar .nMaxFile = Len(ofn.lpstrFile) .lpstrFileTitle = vbNullChar & Space$(512) & vbNullChar & vbNullChar .nMaxFileTitle = Len(.lpstrFileTitle) .lpstrInitialDir = StartPath & vbNullChar & vbNullChar .lpstrTitle = Title .flags = OFN_FILEMUSTEXIST End With 'Call the Windows API function to show the dialog If GetOpenFileName(ofn) = 0 Then 'The user pressed cancel, so return an empty string OpenFile = vbNullString Else 'The user selected a file, so remove the null-terminators ' and return the full path OpenFile = Trim$(Left$(ofn.lpstrFile, Len(ofn.lpstrFile) - 2)) End If End Function
Wow that turned out to be long. There are many declarations that you will need to copy and paste into the module, but the interface you are really dealing with is surprisingly simple. Here is an example of how you could use this in your code to open the open file dialog and get the file path:
Public Sub DoWork() 'Set the filter string (patterns) for the open file dialog Dim strFilter As String strFilter = "Text Files (*.txt)" & vbNullChar & "*.txt*" & vbNullChar & _ "All Files (*.*)" & vbNullChar & "*.*" & vbNullChar & vbNullChar 'Show the open file dialog with the custom title, the filters specified ' above, and starting in the root directory of the C: drive. Dim strFileToOpen As String strFileToOpen = OpenFile("Choose a file to open", strFilter, 0, "C:\") 'See if the user selected a file If strFileToOpen = vbNullString Then MsgBox "The user pressed the Cancel button." Else MsgBox "The user chose to open the following file: " & _ vbNewLine & strFileToOpen End If End Sub
The longest part of writing and testing this solution was actually trying to find how to open a VBA editor and write a macro in Access. The tape can be a great invention for people who use the main menu for "Paste" and "Save", but what a pain. I have been using software all day and I still can’t find the material. [/ Bombastic] sub>