Results 1 to 12 of 12
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Open Windows Explorer from VBA (Access 2003)

    I'd like to click a command button and open Windows Explorer, showing a specific folder.

    Anybody know how?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Open Windows Explorer from VBA (Access 2003)

    In general, any Access-related question, including VBA coding questions, can be asked in the Access forum The implementation of VBA in Access is enough different from that in other Office products and in VB itself that you might get more Access-related answers in the Access forum and you can often find that the answers are already there. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> Your question has come up and been answered many times, including in this thread.
    Charlotte

  3. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Open Windows Explorer from VBA (Access 2003)

    Thanks for the advice! I'm just getting into Access and VBA for Access, and it IS a different world. I have periodic contact with a young guru who works with Access every day, so that helps a lot. But when I try to do things on my own, I quickly run into walls. (There is SO much to learn!)

    The thread you referred to might REALLY be of help to me. My Access project has a command button that creates a one-line table containing all the data about one person. I use that as the data source for a variety of Word mail merge documents, the object being to be able to print a customized document to send to the person -- there are registration forms, form letters, etc.

    I'd LIKE to show the user a Word File Open dialog box, or a Windows Explorer screen, etc., where he can see the various documents that are available to him. But every way I've tried it, the mail merge documents lose track of their source data. Everything works fine if the user opens the File Open dialog box by himself. If the computer does it, the mail merge problem comes up.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

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

    Re: Open Windows Explorer from VBA (Access 2003)

    <hr>I'd LIKE to show the user a Word File Open dialog box, or a Windows Explorer screen, etc., where he can see the various documents that are available to him. But every way I've tried it, the mail merge documents lose track of their source data. Everything works fine if the user opens the File Open dialog box by himself. If the computer does it, the mail merge problem comes up.<hr>
    We'd have to know what code you are using.

  5. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Open Windows Explorer from VBA (Access 2003)

    I've tried lots of things, and I'm reluctant to ask somebody to look at a lot of different newbie stuff that doesn't work.

    One of them is the code from this post that opens Windows Explorer. Explorer comes right up, but when I click on one of the mail merge documents it lists, I get messages from Access. There's a Datalink Properties dialog box, and ultimately a message that says "the database has been placed in a state that prevents it from being opened or locked." The bottom line is that I can't open the mail merge document properly from the Windows Explorer window that the code calls up.

    The code I'm using now works fine, but Word opens without a blank document, and of course the user has to do File Open to get at the mail merge documents he's interested in. (They work fine once he gets there.) I'd like to either have Word open with the File Open box showing (and no disruption of mail merge links), or with a non-mail merge document already open. That document could contain instructions. Here's the code:

    ' Open Word
    Dim appWord As Object 'Declare object variable
    Set appWord = CreateObject("word.application") 'Start Word
    appWord.Visible = True 'Make sure it's visible

    ' Change the path for the Open dialog box
    ' ### It would be nice to find a way to activate the Open dialog box
    ' ### without clobbering the MMDs' source documents

    With appWord.Dialogs(wdDialogFileOpen)
    appWord.ChangeFileOpenDirectory CurrentProject.Path & "Documents"
    End With

    Set appWord = Nothing
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

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

    Re: Open Windows Explorer from VBA (Access 2003)

    I never store the data source information in the Word document. Instead, I initiate the mail merge from Access using Automation, and set the data source information on the fly using objDoc.MailMerge.OpenDataSource where objDoc is a Word.Document object. This makes the mail merge very flexible. It isn't dependent on the path of the database, and you can create the SQL for the mail merge in code. See for example <post#=376450>post 376450</post#>, <post#=375939>post 375939</post#> or <post#=263603>post 263603</post#>.

  7. #7
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Open Windows Explorer from VBA (Access 2003)

    Good info, as was your previous about using a query in Access vs. trying to do an OR in the Word mail merge document. I've printed it all out for use in the future.

    Right now, I'm reluctant to dig into a big redesign of my mail merge documents, which have evolved over about two years into something that's nearly perfect as far as the printed version is concerned, but a bit clunky as far as the steps required before printing. For the moment, I hope to keep things as they are, accept a certain amount of clunkiness, and to polish things up as revisions are required.

    I'm moving from an Excel workbook and Word to an Access database and Word. The new is MUCH more easy and powerful than the old, but the old was pretty highly developed and optimized. The conversion has gone well so far, as the screen shot hopefully shows:
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

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

    Re: Open Windows Explorer from VBA (Access 2003)

    Um, I don't know if the info in the screenshot is real. If so, do you really want all this to be public?

  9. #9
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open Windows Explorer from VBA (Access 2003)

    <P ID="edit" class=small>(Edited by AlanMiller on 25-Apr-05 11:08. Wrong application! ... should learn to read.)</P>Fairly easy in terms of code - something like:

    Dim retval
    retval = Shell("explorer.exe /e,/root,D:111", 1) ' Opens folder D:111

    But have a look at the Windows Explorer Flags to get things displaying the way you'd like.

    Alan

    <img src=/S/woops.gif border=0 alt=woops width=58 height=36>Didn't see the Access 2003 reference

  10. #10
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Open Windows Explorer from VBA (Access 2003)

    That one was just a fantasy. THIS is the real one:
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  11. #11
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Open Windows Explorer from VBA (Access 2003)

    Hans - Today I saw what can be done with Word documents and bookmarks, as opposed to mail merge. A fellow I work with had done this, and it's a lot easier than it sounded to me when you suggested it. So pretty soon, I'm off to follow your suggestion.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  12. #12
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Re: Open Windows Explorer from VBA (Access 2003)

    Hans,

    Today my other guru and I implemented your suggestion to use bookmarks instead of mail merge. VERY slick. VERY simple, once you get the hang of it. Overall, it wasn't very difficult to make the conversion.

    Thanks for your suggestion and all your help.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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