Common File Dialog (A2K)

Topic

New Reply

Does Access offer the common file dialog box available in Excel or Word?

I have to allow users to import an Excel file into a table which will then be appended to another table there is some small processing of the intermediate table involved before it is appended. One option to require them to use a standard XL file name and location, it would be nicer if they could be prompted for a file name and path, the way you can through the “Application.GetOpenFileName” function in Excel (and in Word, IIRC).

I can’t find any reference to similar functions in Access 2K, although it seems that all Office XP applications will be able to obtain the same result through a FileDialog object. In fact, I think I recall reading that Access doesn’t offer such functionality. If so, what alternatives have people identified, or am I stuck with “You must save your Excel file as “XXYYZZ.xls” and save it in “C:blahblah…” I can think of a couple of ways to attack the problem, but they all seem to be much more trouble than they are worth.

Thanks in advance!

Reply | Quote

Replies

The common file dialog was introduced in Access 2002. You can get it using a Windows API call in previous versions. I have attached a demo database (Access 97, zipped). Copy the two modules (a standard module and a class module) to your database. The form in the demo database demonstrates how to use the code.

Reply | Quote

Hans – thank you very much. I won’t pretend to understand API calls (although I will whack away at it) – but the attached file does exactly what I needed.

Reply | Quote

There is no need to understand the API calls; the class module acts as a wrapper around the ugly details.

Reply | Quote

There is no need to understand the API calls; the class module acts as a wrapper around the ugly details.

Reply | Quote

Hans – thank you very much. I won’t pretend to understand API calls (although I will whack away at it) – but the attached file does exactly what I needed.

Reply | Quote

Hi Hans: I was doing a search, looking for being able to open an Excel Worksheet via VBA. This is as close as I could find. I tried your example in Post 304,810 also and added to the references to include the Excel 10.0 Object Library. My question is while using the DB you attached, I was able to get to the location of the worksheets and was able to select the one I wanted to open. The example showed that I had selected a worksheet , but it did not actually open it. What else do I need to do? As always, TIA.

Reply | Quote

The OpenDialog and SaveDialog are only intended to let the user specify a file name, they don’t do anything with the file, that is up to the programmer. This is rather confusing initially, because it seems reasonable to expect that an “open dialog” actually opens a file. But if you think about it a bit longer, you’ll realize that a VBA program doesn’t know what you intend to do with the file unless you tell it so.

In your example, you don’t need a reference to the Excel object library if you want to open the workbook. You can use code like this:

…
If .OpenDialog() = True Then
Application.FollowHyperlink .FileName
Else
…

If you want to manipulate the workbook in code, you would indeed need the reference to the Excel object library. Post back if you need that.

Reply | Quote

Right on the money………..again. Thanks a million

Reply | Quote

The common file dialog was introduced in Access 2002. You can get it using a Windows API call in previous versions. I have attached a demo database (Access 97, zipped). Copy the two modules (a standard module and a class module) to your database. The form in the demo database demonstrates how to use the code.

Reply | Quote