Results 1 to 12 of 12
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Linked Path and Mail Merge (2003)

    For automated merges I store the merge templates in the same location as the backend data file, but in a subfolder. So the path to the templates becomes a slightly longer version of the path to the data.
    Sometimes this path can become quite long, and on two occasions this has caused an unexpected error.

    After the merge has occurred (merge to new document), I close the template in code with:

    appWord.Documents(strWordDoc).Close SaveChanges:=wdDoNotSaveChanges

    strWordDoc holds the fullpath/name of the template.

    Earlier references to strWordDoc have been accepted, and the merge has occurred, but closing the file produces the attached error.

    By using a mapped drive (instead of the UNC path) I can shorten the path, and in both cases this has solved the problem.
    I am aware of the arguments against using mapped drives, so I generally avoid them.

    I am just wondering if anyone else has encountered this problem, and had any alternative solutions.
    Attached Images Attached Images
    Regards
    John



  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Linked Path and Mail Merge (2003)

    Is there a way to assign a UNC path to a letter "on the fly"?

    This would also assume that you could find an unused letter.

    This is "pie in the sky" stuff of course. Sticking my neck out here.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Linked Path and Mail Merge (2003)

    Thanks Pat

    I don't know how to assign a drive letter on the fly (if it can be done).

    I encountered this again today within a School of a University. Fortunately the whole School uses M drive for the same purpose, and using that shortened the path enough.
    Regards
    John



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

    Re: Linked Path and Mail Merge (2003)

    You can use code like this:
    <code>
    Dim wshNetwork As Object
    Set wshNetwork = CreateObject("WScript.Network")

    On Error Resume Next
    wshNetwork.RemoveNetworkDrive "L:"
    On Error GoTo 0

    wshNetwork.MapNetworkDrive "L:", "serversharefolder"

    Set wshNetwork = Nothing</code>

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Linked Path and Mail Merge (2003)

    Thanks Hans

    It is not clear to me that there are any advantages in mapping the drive on the fly rather than just fixing permanently it to a letter.

    Perhaps it would be better to have a second string variable that just holds the file name, rather than the full path and name, and just use that in the line that closes the file.
    I will try that.
    Regards
    John



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

    Re: Linked Path and Mail Merge (2003)

    If you can use a permanent drive letter, that's much more convenient (and if using just the file name works, that's even easier). I posted the code mostly in reaction to Pat's question; perhaps it will come in handy for someone else in the future.

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Linked Path and Mail Merge (2003)

    Thanks Hans

    I just did a couple of test merges using just the filename for the close, and they worked OK, so I will pursue that option.
    Regards
    John



  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Linked Path and Mail Merge (2003)

    Nifty code Hans, could you save the L: drive's path first before reassigning it, then restore the L: drive's path after the merge.

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

    Re: Linked Path and Mail Merge (2003)

    See Re: FileCopy not copying (VBA (Access 2000)) for code you can use to retrieve the UNC path of a drive letter.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Linked Path and Mail Merge (2003)

    That's great, that means i can save any letters path, then use it for something, then restore it.

    Powerful stuff.

    Then John doesn't need to worry how long the UNC path is, he can use this idea.

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Linked Path and Mail Merge (2003)

    Pat

    Wouldn't it be better to just find a drive letter that is not being used, rather than disrupt then restore an existing one? Of course if the whole alphabet is used already, you might need to.

    It looks like I can just use the filename to close the document. I suppose there is a slight risk that the filename might match the name of another document the user happens to have open in Word.
    Regards
    John



  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Linked Path and Mail Merge (2003)

    It would be a lot better to use an existing drive letter that is the same all over the organisation.

    However if that is not available, then i like the idea of grabbing one, using it and restoring it. Give ammo to your arsenal.

    Just my opinion John.

Posting Permissions

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