I need a File Open routine that will work on Access 2007 32 bit and 2010/2013 64 bit

Rob4077
Rob4077 used Ask the Experts™
on
I I need a File Open routine that will work on Access 2007 32 bit and 2010/2013 64 bit in a similar fashion to Excel:
FileName = Application.GetOpenFilename(fileFilter:="Excel Files (*.xls), *.xls")

I have been using code originally courtesy of: ' Microsoft Access 95 How-To ' Ken Getz and Paul Litwin ' Waite Group Press, 1996 ' Revised to support multiple files: ' 28 December 2007, but that won't work on a 64 bit system.

Can anyone suggest a way of doing it? I was once told I could use the excel function by creating a reference to Excel but I am not sure how to do that.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
try using

application.FileDialog(msoFileDialogOpen)
Top Expert 2016
Commented:
Sub FileOpen()
Dim fd As Object
Set fd = Application.FileDialog(1)
With fd
    .Title = "Select File"
    .InitialFilename = CurrentProject.Path
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xlsx,*.xls"
    If .Show Then
        Debug.Print .SelectedItems(1)
    End If
End With
End Sub
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
If you don't use the built-in office dialog and want to stick with the API calls to the Windows open file dialog, then your going to need to modify your calls and use compiler directives if you want to stick with a single version that runs in both 32 and 64 bit Office.

The following is all worth a read through and will help you determine what might need to be changed:

Microsoft Office Code Compatibility Inspector user's guide
http://technet.microsoft.com/en-us/library/ee833946.aspx

also read:

 Compatibility Between the 32-bit and 64-bit Versions of Office 2010
http://msdn.microsoft.com/en-us/library/ee691831(office.14).aspx

and the section "Introducing the VBA 7 Code Base" for the general overview.

All the new 64 bit calls are here:
http://www.microsoft.com/download/en/confirmation.aspx?displaylang=en&id=9970

a list of all the calls that were modified for 64 bit:
http://msdn.microsoft.com/en-us/library/aa383663(VS.85).aspx

Jim.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi Jim, thanks very much for the references. They will be fantastic for another application that I need to work on. I didn't know where to start but your references will really help.

For this question, Rey has provided the perfect answer so I will pass the points to Rey - but I really appreciate your references Jim.

Thanks again to both of you
From the "Tool" menu in the VBE, select "References". Check the box next to "Microsoft Excel XX.X". Now the FileName = Application.GetOpenFilename(fileFilter:="Excel Files (*.xls), *.xls") line will work.

Note, if you are going to be using the code on 2007 and 2010, instead of the above do this:
Add this code in the beginning of the sub:
Dim oExcel As Object
Set oExcel = CreateObject("Excel.Application")

Open in new window

Add the oExcel qualifier before Application.GetOpenFilename, i.e.
FileName = oExcel.Application.GetOpenFilename(fileFilter:="Excel Files (*.xls), *.xls") 

Open in new window


Add this code to the end of the sub:
oExcel.Quit
Set oExcel = Nothing

Open in new window

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<For this question, Rey has provided the perfect answer so I will pass the points to Rey - but I really appreciate your references Jim.>>

 Just be aware that the Office dialog and the Windows dialog one are not one in the same.  You won't have the same features/functionality that you had with your previous code.

Jim.

Author

Commented:
Thanks very much for the additional comments. Looks like there are many solutions to this. Not sure what additional features/functionality I lose with this code.

In this application all I really want to do is allow the user to select a file (accdb) to open (I have modified the filter to suit). I am using it in my welcome window where the user chooses which backend to connect to. I have tried the supplied code in Access 2007 and it works. I assume it will work in 2010
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
You'll be fine. ...Office dialog mainly has the Outlook style bar down the side.  There's some other differences, but I don't remember off-hand.

One thing noteworthy; if your distributing your app, users will need a full copy of Access.  It's not part of the runtime version.

Jim.

Author

Commented:
Thanks Jim.  That limitation doesn't make a difference now but it will do next time I need to use this functionality. Maybe I should go to the Excel option next time. I am surprised that such a common function is so poorly supported.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial