Avatar of Rob4077
Rob4077
Flag for Australia asked on

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

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.
Microsoft Access

Avatar of undefined
Last Comment
Rob4077

8/22/2022 - Mon
Rey Obrero (Capricorn1)

try using

application.FileDialog(msoFileDialogOpen)
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jim Dettman (EE MVE)

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.
Rob4077

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Joe Howard

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)

<<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.
Rob4077

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jim Dettman (EE MVE)

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.
Rob4077

ASKER
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.