Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27
  1. #16
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    601
    Thanks
    69
    Thanked 6 Times in 5 Posts
    Attached is a ZIP of both of them.

    The Excel file is exported from a GoogleDocs file, and some of the cell formats are a bit unusual, but overall it seems to work.
    Attached Files Attached Files
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #17
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,946
    Thanks
    0
    Thanked 204 Times in 185 Posts
    Hi Lou,

    The field code {HYPERLINK "{ MERGEFIELD "Reunion_Web_Site" }"} does indeed create a hyperlink, but you need to update the fields afterwards (eg Ctrl-A, then F9).

    However, although the correct addresses are embedded in the HYPERLINK fields, they always resolve to the first merged hyperlink address. It seems there's something terribly flakey about the way Word processes the field code.

    Sorry.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #18
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    601
    Thanks
    69
    Thanked 6 Times in 5 Posts
    Oh, well -- we gave it an intercontinental try.

    That whole situation is flakey, and there are too many variables affecting what you see and how you see it. Can't complain, though -- Word gives us a tremendous mail merge capability.

    Thanks for your help.

    In the end, I'll only have a few hundred records max, each of which is good for a year or so. I've got a keyboard macro program that can help me manually turn those addresses into links.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  4. #19
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    3,946
    Thanks
    0
    Thanked 204 Times in 185 Posts
    Hi Lou,

    After the merge, you can format both the email addresses and web addresses as hyperlinks with:

    Sub ApplyHyperlinks()
    ActiveDocument.Range.AutoFormat
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #20
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    601
    Thanks
    69
    Thanked 6 Times in 5 Posts
    Tried it. It worked. It's a miracle! ;-)

    Thanks!
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  6. #21
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    601
    Thanks
    69
    Thanked 6 Times in 5 Posts
    Even miracles have their drawbacks. Autoformat changes the formatting of EVERYTHING, not just the hyperlinks. That's bad in my application.

    How do I exercise more control over Autoformatting? I've consulted a reference book about the Tools > AutoCorrect... menu item in word, and experimented a bit, but I still don't have perfect control. Maybe there's something in VBA. I can probably puzzle it out, but I use this stuff infrequently, so any tips from others are invaluable.

    I'd welcome any guidance you may have on gaining good control of the Autoformat part of the macro above.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  7. #22
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts
    How about something like:

    Code:
    Sub ApplyHyperlinks()
    
    Dim fld As Field
        
        For Each fld In ActiveDocument.Fields
            If fld.Type = wdFieldMergeField Then fld.Result.AutoFormat
        Next fld
    
    End Sub
    I haven't tested this, so please use with caution.

  8. #23
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    601
    Thanks
    69
    Thanked 6 Times in 5 Posts
    It also looks as though the original "simple" version above doesn't reformat paragraphs that aren't in the 'Normal' format.

    So far, I'm thinking that the info I've picked up on my own and from the lounge will be enough to make my project do what I want it to do.

    Truly, we live in a time of miracles.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  9. #24
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    111
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by Lou Sander View Post
    With this project, for some reason the mail merge document has trouble finding its data source. I'll get it set up to use the Excel worksheet, and all is well. But sometimes when I re-open it, instead of immediately linking to Excel, it gives me a dialog box that seems to want to find an Access database. I click through it a time or two, and things ultimately settle down, and the document uses the Excel worksheet as its data source. This is NOT something that happens with other documents, though I may have seen it a time or two.
    A couple of things caught my attention. The first is that you said that the Excel document is actually a GoogleDocs document. I presume you are working from a local copy of the Google document. In any case, might it be that some slight incompatibility between GoogleDocs and genuine Excel documents is at the root of the problem?

    The second thing I noticed is that you said that Word sometimes gets disconnected from the Excel document. Assuming that you actually have Excel, even if the document in question is maintained on Google, I have had much better luck with connections to Excel documents staying connected (and working flawlessly) when I choose DDE, instead of ODBC. DDE may be "old hat," but it still works, and isn't going away any time soon.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  10. #25
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    601
    Thanks
    69
    Thanked 6 Times in 5 Posts
    The data source document IS in Excel 2003. Its data originates with a Google Docs online form, though. When someone posts a new entry to the online form, it ends up in a Google Docs worksheet. I periodically export the Google worksheet to my desktop, then copy the new lines to an Excel worksheet where I can edit them (and they won't be overwritten by a subsequent export). Once they're edited, I use the Excel worksheet as the data source for my Word mailmerge 'directory'. That's when the (non-repeatable) data connection problem happens.

    If the unwanted (and mysterious, to me) dialog box about Access connections comes up, I just keep dismissing it until it goes away and lets me choose my Excel data source. In future, I'll look for a DDE choice there.

    Experimentally I've learned that the unwanted dialog box only seems to come up when I have other Excel worksheets open. At least so far, if I close all Excel worksheets (including my data source), I don't get the unwanted dialog box.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  11. #26
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Dallas, Texas, USA
    Posts
    111
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by Lou Sander View Post
    The data source document IS in Excel 2003. Its data originates with a Google Docs online form, though. When someone posts a new entry to the online form, it ends up in a Google Docs worksheet. I periodically export the Google worksheet to my desktop, then copy the new lines to an Excel worksheet where I can edit them (and they won't be overwritten by a subsequent export). Once they're edited, I use the Excel worksheet as the data source for my Word mailmerge 'directory'. That's when the (non-repeatable) data connection problem happens.
    Thanks for clarifying that point, and, thereby, eliminating Google from the equation.


    Quote Originally Posted by Lou Sander View Post
    If the unwanted (and mysterious, to me) dialog box about Access connections comes up, I just keep dismissing it until it goes away and lets me choose my Excel data source. In future, I'll look for a DDE choice there.
    After you indicate that your data source is an Excel document, you should be offered a choice of connecting to Excel via ODBC or DDE. I've always chosen DDE, and been delighted with its fast and reliable performance.


    Quote Originally Posted by Lou Sander View Post
    Experimentally I've learned that the unwanted dialog box only seems to come up when I have other Excel worksheets open. At least so far, if I close all Excel worksheets (including my data source), I don't get the unwanted dialog box.
    I believe you just identified the key. I know this is true if the connection type is ODBC. However, I don't think this happens if the connection is via DDE, though I can't remember for sure, and it's been several months since I did a mail merge with an Excel document.
    David Gray, Chief Wizard
    WizardWrx
    Irving, Texas, USA

    WizardWrx Web - Technical Articles and Free Software
    You are more important than any technology we may employ.

  12. #27
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    601
    Thanks
    69
    Thanked 6 Times in 5 Posts
    I looked the last time it happened, and I wasn't offered a DDE choice. If it happens again, I'll post what I AM offered.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Page 2 of 2 FirstFirst 12

Posting Permissions

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