Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Common File Dialog (A2K)

    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!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Common File Dialog (A2K)

    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.
    Attached Files Attached Files

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Common File Dialog (A2K)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Common File Dialog (A2K)

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

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Common File Dialog (A2K)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Common File Dialog (A2K)

    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.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Common File Dialog (A2K)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •