Link to home
Start Free TrialLog in
Avatar of Rob4077
Rob4077Flag 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.
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

try using

application.FileDialog(msoFileDialogOpen)
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of 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
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

<<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.
Avatar of 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
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.
Avatar of 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.