Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forgetting source on 'non-simple' SQL (2002 SP3)

    When I open a mail merge document, Word tells me something like "Opening this document will run the following SQL command: SELECT * FROM 'Sheet3$' ...". It doesn't tell me the actual DataBase that it'll be using but hey - can't have everything!

    Anyway, in my merge I only want records that have something in the "Name" field. So I go to the "Mail Merge Recipients" dialog and using the Filter drop down arrow, select (Nonblanks). I can then perform the merge.

    BUT!!! If I save the main document with (Nonblanks) selected, when I subsequently open it, I see the new SQL statement as "SELECT * FROM 'Sheet3$' WHERE ('Name' IS NOT NULL And 'Name' <> '')" as expected BUT!!! it then tells me that it cannot find the data source, and I have to go through the file dialogs to link it to the source once again, and in the process it looses the (Nonblanks) selection once again.

    If I save it with the "simple" "SELECT * FROM 'Sheet3$'" (i.e. no filters applied to the Recipients) then it remembers the data source, if I have a filter applied then it remembers the filter but forgets the source.

    So, how can I get it to remember BOTH the filter AND the source?

    Any ideas?

    TIA
    Paul Lautman

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

    Re: Forgetting source on 'non-simple' SQL (2002 SP3)

    I haven't been able to find anything on this, and I cannot reproduce the problem - if I save a merge document with a WHERE part in the SQL string, it works correctly when I reopen it. Could you attach a zip file with a small workbook and document to illustrate the problem?

  3. #3
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forgetting source on 'non-simple' SQL (2002 SP3)

    Hello Hans,
    thank you for looking at my problem.

    The attached zip file should be unpacked in the root directory of the C drive. It will then re-create the original directory structure which I created for this test and which is very simlar to the structure that I actually use.

    When unpacked you will find in the directory C:$user1HSCPool two Word files and one Excel file.

    Both the Word files were linked to the Excel file when they were saved.

    Pool Log Mail Merge Man2s.doc is a "simple" SQL query whilst Pool Log Mail Merge Man2w.doc contains the WHERE clause.

    When I open Pool Log Mail Merge Man2s.doc, it is ready to go. When I open Pool Log Mail Merge Man2w.doc I have to go and re-link it to the database (the Excel file).

    Thanks once again for taking a look at this.

    TIA
    Attached Files Attached Files

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

    Re: Forgetting source on 'non-simple' SQL (2002 SP3)

    I get the same error with your files. I haven't been able to determine what the essential difference is between the correctly functioning example I created and your files, but I'll keep trying.

  5. #5
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forgetting source on 'non-simple' SQL (2002 SP3)

    Thank you Hans, this is very much appreciated.

    Maybe you can send me your working examples and I'll try working in the other direction?

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

    Re: Forgetting source on 'non-simple' SQL (2002 SP3)

    I had used an existing workbook with proprietary data. While trying to construct a new example, I ran into the problem again, but I have found a workaround.

    Select Tools | Options... (in Word), then activate the General tab. Tick the check box Confirm Conversion at Open, and click OK.
    When you open the merge document, and get the message that the data source cannot be found, click Find Data Source...
    After you have selected the workbook, you'll be prompted for a method to use (see screenshot).
    Do NOT select the default method (OLE DB Database Files), but select MS Excel Worksheets via DDE or an ODBC data source instead.
    If you pick ODBC, you'll have to click Options... in the next dialog and tick all check boxes.
    You can now specify the worksheet, then filter the data etc.
    If all goes well, you'll be able to reopen the document with the filter intact.
    Attached Images Attached Images
    • File Type: png x.png (6.0 KB, 0 views)

  7. #7
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forgetting source on 'non-simple' SQL (2002 SP3)

    When I try DDE I get the error "Word could not parse your query options into a valid SQL string." and it then simply ignores the filter and reads all the data.

    However the ODBC option seems to work.

    Obviously a bug in Word with OLE DB somewhere.

    Thanks for your assistance.

  8. #8
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    One more thing Hans, if you will be so kind

    I tried recording the setting of a Merge filter, so that I could geta clue as to the VBA code required to automate it. But the subroutine produced contained no code at all.

    Where do I find the classes/methods/properties associated with this and get a clue as to the correct construction?

    TIA
    Paul

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

    Re: One more thing Hans, if you will be so kind

    Look up OpenDataSource and QueryString in the Word VBA help and in the Object Browser (press F2 in the Visual Basic Editor)

  10. #10
    3 Star Lounger
    Join Date
    Sep 2004
    Location
    Portsmouth, Hampshire, England
    Posts
    200
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One more thing Hans, if you will be so kind

    Thank you Hans, you're a star <img src=/S/music.gif border=0 alt=music width=97 height=29> (of course if word recorded the steps properly then I wouldn't have had to ask you!!!)

Posting Permissions

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