Is there an Open File dialog for Access 2010 64bit?

Question

How do I get an Open File dialog for Access 2010 64bit? Normally I would use the common dialog control but that is 32bit and cannot be used with Access 2010 64 bit.

Deathly

I can't imagine that this doesn't work. I've used open file dialogs in 64-bit apps plenty of times. You can't run the 64-bit version of Office on a 32-bit version of Windows, so you should be dealing with 64-bit versions of commdlg. – Spelaean

Whenever I try to add the common dialog control and drag into an MS Access form it gives me an "OleDb Error". This is a 64 bit Windows 7 PC with MS Access 64bit. It is odd to me as well. – Deathly

What's wrong with GetOpenFileName or am I missing something? – Millinery

@Cody I think Greg is referring to CreateObject("UserAccounts.CommonDialog"). – Millinery

Uh, dragging any old control off the list of installed ActiveX controls is very often going to fail, since there's no guarantee they will work with Access. Certainly, as Tony says below, I've never used a control for FileOpen -- I always use the Windows API call, which will work on every version of Windows. – Mafaldamafeking

@David: Except that nonsense doesn't even work on 32-bit platforms, so that seems unlikely. It's a hack that only exists in Windows XP. GetOpenFileName is the solution, but not everyone knows the Windows API like the back of their hand. ;-) – Spelaean

What nonsense doesn't work? I couldn't use the Windows API without copying it from my existing codebase, but I have a module with my file open code in it that I import into every one of my Access apps. You don't need to have the API call memorized -- you just need the code to use it and the understanding of how it is used. The Access web has had that code available for more than a decade. – Mafaldamafeking

@David: Sorry, I was referring to the other David [Heffernan] and his comment about CreateObject("UserAccounts.CommonDialog"). Yes, the API works fine, and wrapper libraries have been floating around for a while. Obviously you don't need to have it memorized, but you do need to know what function to call to get the functionality that you desire. Anyway, there's a couple of workable solutions (or at least they work on my machine) posted now that do call the Windows API. Sorry for the confusion. – Spelaean

Answer 1

This guy has a tool that generates code that is 64 bit compatible for opening a file. It is freeware.

http://www.avenius.de/en/index.php?Products:IDBE_Tools

This was the only thing that worked.

Deathly

David, I saw the long pointer article above earlier and attempted to make Tony's code work. I found the avenius generator before I could get Tony's code working. Thankfully the code that it generates works from Access 2000 to Access 2010 64 bit. It is being used now in AccessDiff: kellermansoftware.com/p-33-accessdiff.aspx The key thing that detects the 64 bit version of Access 2010 is: #If Win64 = 1 And VBA7 = 1 Then – Deathly

Answer 2

You can use the built in file dialog. It been there since access 2003.

Dim f    As FileDialog 
Set f = Application.FileDialog(msoFileDialogFilePicker) 
f.Show 
MsgBox "file choose was " & f.SelectedItems(1) 

You can late bind if you wish:

above needs: Microsoft Office 14.0 Object library

If you remove the reference to the 14.0 object library, then the following code will work without any references:

Dim f    As Object 
Set f = Application.FileDialog(3) 
f.AllowMultiSelect = True 
f.Show 

MsgBox "file choosen = " & f.SelectedItems.Count 

So, above works in runtime or regular edition from 2003 onwards, and also works for either 32 or 64 bit editions of access 2010.

Phobe

I would never use this, as I fear they'll do the same thing they did with the FileSearch Object, and take it out of a future version. Since I have the direct API code in place already, there's not much need for me to use a wrapper like this, though certainly it would avoid the 64-bit code conversion problem with the API code. – Mafaldamafeking

FileSearch don't believe was included with runtime and thus could not always be assumed. FileDialog been there since access 2003, always worked with the runtime, and works without having to set any references (late bind). The dialog is part of all of office and works in 64 bit edition (as you noted). I do agree it could be removed, but so far historically this choice would have worked since 2003 with less hassles then the api and this includes the new 64 bit version. I quite much adopted this, and if I eat crow on this, I will do so in public, but I think it is the best choice right now. YMMV. – Phobe

The FileSearch object was a member of the Access Application object, so it required no reference. I suspect it is available in the runtime, given that it's a member of the top-level Application object. So far as I can tell, it was implemented identically to the way FileDialog is implemented, and yet it was removed in A2007. I think that's because MS wanted to replace their old search techniques with ones based on the new search infrastructure, so it was more politics than anything else. I admit it's hard for me to imagine a similar politically motivated reason to remove FileDialog. – Mafaldamafeking

This does not work with MS Access 2010 64 bit. When I run the code above with a reference to the Microsoft Object 14.0 I get a "User Defined Type Not Defined" error. – Deathly

I got the same error as Greg on the "Dim f As FileDialog" line. Adding "Microsoft Office 15.0 Object Library" as a reference fixed the error and allowed me to use this code without issue. Thank you for posting that solution Albert! – Holub

Answer 3

I've never used a control for the open File dialog as it's just a wrapper for the API call anyhow. Call the standard Windows File Open/Save dialog box In addition there can be distribution and versioning problems with controls so I do my best to avoid them.

Unionism

The code in the link you posted will probably work, but it's oh-so-messy. And the real WTF is the use of the Variant type. There's just no excuse for that when you don't absolutely have to use it. – Spelaean

The code Tony cited doesn't work? I've got it working on 64-bit Win7, and it's never been an issue -- it worked without alteration the first time it was called. – Mafaldamafeking

The code Tony cited does not even compile on Access 2010 64 bit version. Even after adding the PtrSafe attribute it does not work. It will certainly work with Access 2010 32 bit version under Windows 7 64 bit. – Deathly

Answer 4

I was working with this problem for a long while...

Everything you have said above works but there is one last piece to add ... under the WITH OFN declaration you need to change

.lStructSize = Len(ofn)

to

.lStructSize = LenB(ofn)

And then everything works.

Sansone

Answer 5

First of all, the "CommonDialog Class" doesn't even appear to work on a 32-bit version of Office. It gives the same OleDb error. As one of the commenters points out, this isn't the control you should be using. And while there might be another ActiveX control you could use, there's really no guarantee that it will be available on every machine that you want to deploy your database on. My dev box has Visual Studio 6, VS 2008, and VS 2010 on it, in addition to Office and other programs, all of which provide ActiveX DLLs that a typical user could not be expected to have. Additionally, many of these libraries are not redistributable, or pose unique installation hurdles that may simply not be worth the trouble.

By far, the simplest, most universal solution is to call the Open dialog from the Windows API. It's located in comdlg32.dll, which is available on every version of Windows you could possibly be targeting, and doesn't impose any dependencies on comdlg32.ocx. It also provides better performance than using an ActiveX control because it doesn't require an additional module to be loaded into memory.

The code that is required isn't very complicated either. You need to provide a declaration for the function GetOpenFileName, which creates the Open dialog box. It takes a single parameter, an instance of the OPENFILENAME structure that contains information used to initialize the dialog box, as well as receiving the path to the file selected by the user. So you'll also need to provide a declaration of this structure. The code in VBA would 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 a couple of constants you can pass as flags to customize the dialog's behavior. For completeness, here's 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've wrapped this whole mess inside of a helper function that you can call from within VBA. It accepts as parameters the properties you will most commonly need to set for the open file dialog, handles calling the Windows API itself, and then returns 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 test 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 ended up being long. There are a lot of declarations you'll need to copy and paste into a module, but the interface you actually have to deal with is surprisingly simple. Here's a sample of how you might actually use this in your code to show the open file dialog and get the path to a file:

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 the VBA editor and write a macro in Access. The Ribbon might be a great invention for people who use the menu primary for "Paste" and "Save", but what a pain. I spend all day using software, and I still can't find stuff. [/rant]}}

Spelaean

This solution does not work against Windows 7 64bit with Microsoft Access 64bit. When I run the code it immediately returns with a "The user pressed the Cancel button." – Deathly

Also, the function declaration was also missing PtrSafe: – Deathly

Answer 6

I missed the 64-bit Access detail. It's very unlikely that you should be running it, but if you are, here is an article for your consideration that explains how you have to alter your API call to work -- you have to use the new long pointer data type:

Compatibility Between the 32-bit and 64-bit Versions of Office 2010

If you alter the API code accordingly, it should work fine on 64-bit Access.

But you should really ask why you're using 64-bit Access. It's really not at all recommended by MS that anyone use 64-bit Office unless they have specific reasons why they need it (such as needing to use the extra memory it provides, particularly for things like complex Excel spreadsheet models). Access is definitely not one of the apps that benefits much from the conversion to 64-bit.

Detailed discussion of the subject:

• Office 2010 - about the 64-bit version - Office Watch
• Installing Office 2010 64-bit - Office Watch
• Office 32-bit or 64-bit - which version is installed? - Office Watch
• Office 32 and 64 bit on the same machine - Office Watch
• Preparing for Office 2010 64-bit - Office Watch
• 64-bit Office - is it worth the trouble? - Office Watch

In short, most people shouldn't be running 64-bit Office, precisely for the reason you encountered -- it causes legacy code with outside dependencies on 32-bit components and APIs to fail.

Mafaldamafeking

Answer 7

I've just been wrestling with resolving this issue in a 64-bit version of Excel 2013.

A combination of...

1. Using the LongPtr data type for 3 of the items (hwndOwner, hInstance, lpfnHook) in the OPENFILENAME structure passed to GetOpenFileNameA
2. Replacing the Len function with the LenB function when obtaining the size of the OPENFILENAME structure (as mentioned by Max Albanese)

...did the trick, thanks to guidance documented here: https://gpgonaccess.blogspot.co.uk/2010/03/work-in-progress-and-64-bit-vba.html

Leopold