Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Christchurch, Canterbury
    Posts
    122
    Thanks
    27
    Thanked 1 Time in 1 Post

    VBA mail merge stopped working (Word 2002)

    I've spent quite a bit of time writing VBA code to take 70 odd variables in through a VBA form in Word, update an Excel spreadsheet, then run a mail merge of up to 20 documents. Wrote it, tested it , and now writing user documentation <img src=/S/thewave.gif border=0 alt=thewave width=225 height=33>

    This morning it stopped working <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>

    When it gets to the merge (as below) - I get a "select table" box with nothing in it.

    Set myDoc = Documents.Open(strTemplateDir & strDocNames(i))
    Set myMerge = myDoc.MailMerge
    myMerge.OpenDataSource Name:=strConveyancingDir & strExcelWorkbook, _
    Revert:=False, _
    SQLStatement:="SELECT * FROM `Data$` WHERE Key = " & strClientNumber & strMatterNumber

    If I change the open data source to myMerge.OpenDataSource Name:=strConveyancingDir & strExcelWorkbook (ie without revert and sql) - then I can pick the right sheet.

    Manual merge needless to say works fine (but not a real option).

    So far I have:
    Tried another PC in the office - same thing
    Went back to last week's code - same thing
    Backed out KB929338- same thing
    Did a system restore to Monday morning - same thing

    <img src=/S/help.gif border=0 alt=help width=23 height=15> <img src=/S/please.gif border=0 alt=please width=31 height=23>

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA mail merge stopped working (Word 2002)

    I have very limited familiarity with merges, but I'll take a stab in the dark. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

    I've seen a number of threads where changing the method of accessing the data source from ODBC to DDE fixed some problems. What I wonder is whether the default for this might have changed somehow in your configuration of Word/Excel. Perhaps the setting is sticky, and at some point prior to this morning you ran a manual merge using the other setting??

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

    Re: VBA mail merge stopped working (Word 2002)

    Does it help if you add
    <code>
    , SubType:=wdMergeSubTypeWord2000
    </code>
    to the OpenDataSource statement?

  4. #4
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Christchurch, Canterbury
    Posts
    122
    Thanks
    27
    Thanked 1 Time in 1 Post

    Re: VBA mail merge stopped working (Word 2002)

    Thanks - that one didn't help. I did manual merges at the start to get the feel - worked fine. Tried again to "unstick" - but didn't help. Thanks.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Christchurch, Canterbury
    Posts
    122
    Thanks
    27
    Thanked 1 Time in 1 Post

    Re: VBA mail merge stopped working (Word 2002)

    Thanks Hans - this got it sort of working (the users never saw the nice slick version - so the extra ugly steps will be just our secret). But why did it break? The merge code has been untouched for weeks - it's been the user interface I have been tweaking <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

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

    Re: VBA mail merge stopped working (Word 2002)

    Are you sure you've been using the same version of Word? Microsoft revamped the way mail merge works between Word 2000 and Word 2002 (aka XP). The default method for connecting to the data source in Word 2002 and later is OLE DB and this is in many ways dumber than the older methods (typical, isn't it?). The extra argument tells Word to use the older method. I had to add this to all my mail merge code when the company I worked for upgraded from Office 97 to Office XP.

  7. #7
    2 Star Lounger
    Join Date
    Nov 2003
    Location
    Christchurch, Canterbury
    Posts
    122
    Thanks
    27
    Thanked 1 Time in 1 Post

    Re: VBA mail merge stopped working (Word 2002)

    That's what so confused me. One day working - the next day not. No Word upgrades (been on XP for years). Microsoft.

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

    Re: VBA mail merge stopped working (Word 2002)

    Strange - I cannot explain that. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

Posting Permissions

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