Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Unhappy Unanswered: Help! Word Macro using excel "database"

    I have recently been thusted in to the position of fixing the errors in our mail merge document. THe problem is this. It was originally designed using word 97/200 (don't know which). The office now uses 2002 (XP). With this change came an onslought of issues. I have resolved most of the but not this one. The word macro opens an excel files to create a series of form letters based on conditions. If the entry donated x amount use this letter, if the entry is for a staff member use this letter, etc. This is all done by using query string in the VB code. When I run the script, it generates an error message "Word could not merge the main document with the data source because the data records were empty or no data records matched your query options." IF the user hits ok, the program resumes. This message will pop up at least twice for all 6 documents we have. At the end of the macro, it completes and all is fine. The problem is that the macro can take ~5 minutes to run. If the user has to stay and keep clicking "ok" they get pissed and call me. If I rem out the query statement, the merge works, but it generates the letters for every entry without disgression. I have determined that the cause fo the error is a the query line the first of which is as follows...

    ActiveDocument.MailMerge.DataSource.QueryString = _
    "SELECT * FROM K:\MergeData.xls WHERE ((Gifts_1_Amount = 0) AND (Subcategory <> 'Staff') AND (Gifts_1_Giftcode <> 'SC') )"

    the K drive is the location to the shared files. Stargely enough, I tried using MSQuery to generate a query statement, but it said the table was a hidden system one. Could that have something to do with it? If so, how do I change it. I should also tell you that the excel files is generated by the macro too. The original data source is a dat file export from our Member tracking software.

    I am not a expert in VB (more of a network guru) but I am sure the error is incorrect syntax in the query. OH, by the way, there is a call before the query that opens the excel file for use...

    With ActiveDocument.MailMerge
    .OpenDataSource Name:="K:\MergeData.xls", _
    SubType:=wdMergeSubTypeWord2000

    Once the database has been opened by the first call, do I have to reference if from that point using the full path to the file? Seems kinda strange. I really appreciate any help you guys can give me. I have inherited this code and am wading through the waters of source code and documentation.

    Bill
    Last edited by billprozac; 01-23-04 at 17:52.

  2. #2
    Join Date
    Jan 2004
    Posts
    3
    Ok, after doing some debugging, it turns out that the error is not with the query, the query is fine. The problem is, sometimes, the dataset does not contain any entries that match the query. That is why it generates the error. Can I have word stop generating the error? I don't think that the wdAlertNone command will nork in 2002. AM I wrong?

  3. #3
    Join Date
    Feb 2004
    Posts
    3
    Originally posted by billprozac
    Ok, after doing some debugging, it turns out that the error is not with the query, the query is fine. The problem is, sometimes, the dataset does not contain any entries that match the query. That is why it generates the error. Can I have word stop generating the error? I don't think that the wdAlertNone command will nork in 2002. AM I wrong?
    GoTo arent the greatest but they do the job in VB
    ----------------------------------------------------------
    Use the OnError Command....such as

    Sub yourSub()

    On Error GOTO Skip

    **all your code**

    Skip:
    **closing code**
    end sub

Posting Permissions

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