Solved

Word Automation with SQL server Data

Posted on 2006-11-20
23
493 Views
Last Modified: 2008-01-09
Hello All...
I have been reading about word automation using vba and mail merge.  I am trying to get some help and possibly some code that cant point me in the best direction to accomplish the following...
I want to watch a MS SQL table for new orders...Thinking about running this as part of a DTS job...
If there is a new order ...Output a sql query with order number, qty, customer number,etc.  (Could possibly have multiple records.) Seeing as I am intending to run this every 5 min.
From this query I can output to a temporary table or to a text file....

Either way...What I want is to possibly call a word template to open from the DTS job and populate using the data from the query.....
I need the query to populate each record into a new word file and then save the word file in a predefined location using the order number in variable from the sql data in the filename. Then close the word document and continue processing the information one record at a time until the end of the information.

I hope I have explained this ok....Basically we have electronic orders that are coming in..and we want to create an order confirmation and send it back to the customer..via email or fax...(The delivery part I have already handled..it is just the document creation process that is new to me.)

I have looked at everything from vba mail merge...to DTS ActiveX to populate word....I just want something reliable that will always generate one file per entry, save as , and close.

Hope this makes sense...
As always your help is most appreciated...

0
Comment
Question by:pattersonr
  • 10
  • 7
  • 3
  • +1
23 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17985986
I suggest that you write some VB Script that:

1. Identifies all the unprocessed order numbers (based on a flag in the order records)
2. For each order number, run the Word merge
3. Save the Word file with the order number file name.

Then you can

1. Schedule the ActiveX script through a DTS or directly from a job
2. The Word merge file has no code in it... it is purely to define the template


You willl need to install MS Word on your server.

Alternatively you could probably do all of that in a Reporting Services report... not my area of expertise but at least you don't have to install Word on the server.

I can post some code tommorow when I'm at work.


Another way to do it is code it all up in an ADP (MS Access) and create a Access report. Only problem with that is that the order template is defined in a MS Access report and this may be unacceptable.

Anyway thats three different ways to render your order... have a think about it and I'll post more tommorow.
0
 
LVL 5

Expert Comment

by:RainUK
ID: 17986069
Instead of using a DTS job to watch for new orders, you could try this:

1. Create an INSERT TRIGGER on the table and then iterate the 'inserted table' for the new order numbers and store them in a variable.

2. Create a VB 6 EXE that receives a string of Order Numbers as a command line parameter (Semi-Colon delimeted). This exe should contain the code to do the mail merge for your orders Word.DOC template by using the order numbers passed in the command line and connecting to SQL Server to get the rest of the order information to put on the template.

3. As part of the INSERT TRIGGER you can get SQL to execute your VB6.EXE using sp_OACreate and sp_OADestroy. (Has examples on SQL Online and on the web)

This way you relieve SQL Server from polling and also abstract the complexity of doing a mail merge in SQL into a VB6 application.

By also using the trigger your orders will be responded to immediately and the word DOCs created on the fly in real time.

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 17986079
If you need a simple output with one record producing one Word document without sub-ists from a one-to-many join, then Word's mail merge is advised.

In Word, use the mail merge wizard (via the Tools menu - exact details slightly different, depending on Word version).

Use this to choose your datasource (your query). You will then be able to design your document and place the fields from the query on it. The will be your Main document. It is not a template in Word terms, but it does a similar job to a template in that, once designed, it is kept unmodified and used to produce the output to eMail, Fax, printer or a result document.

This is skeleton code to run the merge:

Sub DoMerge()
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
   
    Set wdApp = New Word.Application
    Set wdDoc = wdApp.Documents.Open("C:\MyPath\MyMainDoc.doc")
    With wdDoc.MailMerge
        .Destination = wdSendToEmail
        .Execute
    End With
    wdDoc.Close False
    wdDoc.Quit
End Sub



0
 
LVL 1

Author Comment

by:pattersonr
ID: 17988766
Wow..Thank you all for the quick response.
Let me see if I can respond to everyone....

nmc...
The ActiveX through DTS doesnt sound like a bad plan.  Can you point me in the direction of some code to run the sql query and pass the fields to a word template?  I havent really pursued access or reporting services because we currently have word already installed and some vendor supplied software is already performing some word template automation.  So I thought although I cant use their procedure exactly, it would be familiar enough for others to support.  Although if the word thing ends up not suiting my purposes, I might switch to access. However, I really would like to give this word idea the good ol' college try first.

rainuk...
I did think about a trigger, there are some definate pluses there.  However, my concern was two fold with a trigger.  1.  This order table is written to constently by many users.  I didnt want to slow that process down with a trigger checking every insert.  I thought just polling every few min would be less overhead on the server.  If you think otherwise let me know.  I am certainly not a die hard sql admin.  2. When we upgrade our vendor software that is the main application on this sql server...part of their upgrade process is to drop and readd every trigger.  So that would mean that every upgrade I perform I would have to readd the custom trigger.  Not impossible...just requires extra administration.  If it is a scheduled job, then it wont be deleted by the upgrade procedure.

Also...my skill set is somewhat limited with VB.  I have done a decent amount of playing in vba and I am sure that vb isn't too far off. But I definitely would need some additional help to get a vb app working.

Graham....

So the code posted above.. Is that in the mail merge document? In order to save the mail merge document with one of the sql fields as a part of the file name...how can I accomplish that?

How can I process through multiple entries and create a seperate document for each one?
I have been playing with the mail merge functionality and I think it works for my process...I just cant get my head yet wrapped around...How to fire it and then how to deal with multiple records and save each record as a unique file.

Thank you all so much for your input...

Looking forward to your responses..:)
0
 
LVL 1

Author Comment

by:pattersonr
ID: 17988787
rainuk...
I just realized that I didnt explain that I am only wanting to export edi orders which will be a very small subset of the total amt of new orders.
That is why I made the comment about a lot of people accessing and inserting records into the order table that might not have anything to do with my needs.  But would defiantely still be interested if you think a trigger would be less overhead.

0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17994336
To do a Word merge and actually split into individual files you need to basically run one word merge per record.

It might actually be more efficient to forget about using a Word merge and actualy stuff the data directly into a Word file.




Running along these lines, you have two ways to do it (though of course there are many solutions not using this particular architecture):


1. Using word merge. This requires 2 x n + 1 trips to the database for n orders.

A. Cursor through new orders, picking up orderid  (once off db trip)
  a. Open a word merge template
  b. point it at the order id and run the word merge (once per document)
  c. Save with an order name
  d. Update the order to indicate the document has been created (once per document)
B. Repeat till no empty orders left



2. Stuffing it into word. This requires n+1 trips to the database for n orders
 

A. Cursor through new orders, picking up all details  (once off trip)
  a. Open a blank word template file
  b. Fill in the order details directly
  c. Save with the order name
  d. Update the order to indicate the document has been created  (once per document)
B. Repeat till no empty orders left


So basically option 2 would require less db trips but possibly more time at step b... you'd have to try it and see.


Before I start cutting code I am interested in other peoples thoughts on this. I think with this kind of automation you are really going to have to watch how you do this because if you get a sudden flood of orders with an inefficient order rendering method you could crash your server.
0
 
LVL 5

Expert Comment

by:RainUK
ID: 17994427
Triggers can be quick or slow, depends on how much processing you do in them.

As long as you set it to run the VB6.exe outside of the SQL Server process then it should be very quick. So essentially you are just launching an .exe without waiting for it to complete.

But from what you say about only processing a small subset of the actual orders being inserted into the table, then you could just stick to polling it every 5 minutes. It would not be such a big overhead on CPU time.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 17994835
The code is intended to be VB (early binding), since this is the VB TA. However it could be VBA in any application type (e.g. Excel or Access). If it were in Word you wouldn't need to create an application:

Sub DoMerge()
    'Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
   
    'Set wdApp = New Word.Application
    'Set wdDoc = wdApp.Documents.Open("C:\MyPath\MyMainDoc.doc")
    Set wdDoc = Documents.Open("C:\MyPath\MyMainDoc.doc")
    With wdDoc.MailMerge
        .Destination = wdSendToEmail
        .Execute
    End With
    wdDoc.Close False
    'wdDoc.Quit
End Sub

In merge, you can change the Type as well as the Destination. The Type defines the structure of the output. It can be letter, label, email, envelope or directory (called catalog in older Word versions). Letter type with a destination of New document will produce a single word document divided into Sections with Next Page Section breaks. If you needed to produce separate Word documents, you would have to do the merge one record at a time. This code will merge the third record only.

Sub DoMerge()
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
   
    Set wdApp = New Word.Application
    Set wdDoc = wdApp.Documents.Open("C:\MyPath\MyMainDoc.doc")
    With wdDoc.MailMerge
        .DataSource.FirstRecord = 3
        .DataSource.LastRecord = 3
        .Destination = wdSendToNewDocument
        .Execute
    End With
    wdDoc.Close False
    wdDoc.Quit
End Sub

However you did say the you need to send to fax or email. Merge can do either directly. Just change the .Destination property value. The same shows the email constant. wdSendToFax is the constant for fax.

 
0
 
LVL 1

Author Comment

by:pattersonr
ID: 17998040
Ok..thanks again guys....

nmc...

I think I am definately in favor of the word template and pump the data into word and do until end of new records..option 2
So I have no problem creating a word template with bookmarks....and I have a current DTS job that outputs the the info a need to a temporary table...
So I guess the piece I am missing is how to through DTS (ActiveX) or some other method loop through the records and send the record values to word template and populate the bookmarks. Then save as a file with the ordernumber value in the filename.

rainuk..
Thanks for that info. I think for now I will stick with the polling model..but I will keep the trigger in mind.

graham...

In regards to the email or fax...That is handled in some post processing.  I probably could add that in here to the merge...but I have a pretty good process for handling that.  My biggest problem is the actual document creation and saving.

I guess the other question is how do I establish how many times I need to create the merge loop..based on the number of records...  Also how do I save the newly created file with the appropriate name? In the case of the merge.?

Thanks again for the help...

Have a great Holiday
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 18001801
This shows how to do the mailmerge a record at a time and to save each result with a unique name.

Sub DoMerge()
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    Dim wdResultDoc As Word.Document
   
    Dim r As Integer
    Dim strMainDocPath As String
    Dim strMergeResultsFolder As String
   
    strMainDocPath = "C:\MyPath\MyMainDoc.doc"
    strMergeResultsFolder = "C:\MyResults\"
    Set wdApp = New Word.Application
    wdApp.Visible = True 'optional
    Set wdDoc = wdApp.Documents.Open("C:\Documents and Settings\Graham Skan\My Documents\mmtest.doc")
    With wdDoc.MailMerge
        For r = 1 To .DataSource.RecordCount
            .DataSource.FirstRecord = r
            .DataSource.LastRecord = r
            .Destination = wdSendToNewDocument
            .Execute
            'identify, save and close result document
            For Each wdResultDoc In wdApp.Documents
                If wdResultDoc.FullName <> strMainDocPath Then
                    .DataSource.ActiveRecord = r
                    wdResultDoc.SaveAs strMergeResultsFolder & .DataSource.DataFields("AddressID") & ".doc"
                    Debug.Print "SaveAs " & strMergeResultsFolder & .DataSource.DataFields("AddressID") & ".doc"
                    wdResultDoc.Close False
                End If
            Next wdResultDoc
        Next r
    End With
    wdDoc.Close False
    wdApp.Quit
End Sub
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 18005135
>> the piece I am missing is how to through DTS (ActiveX) or some other method loop through the records and send the record values to word template and populate the bookmarks. Then save as a file with the ordernumber value in the filename.

GrahamSkans code will do all of that. You simply need to:

1. Create a word merge template pointing at your temp table
2. Paste that code in
3. In your sheduled job:
    a. Populate the temp table
    b. Call word from a cmdexec task like this:   Winword.exe /mDoMerge D:\PathToYourMergeTemplate\TemplateFile.DOC
    c. Update your order to indiciate the file has been created


There is probably no issue between using bookmarks vs word merge fields, especially for small datasets. I think really you'll need to try it and see. Besides, I just checked out some code that populates bookmarks and it doesn't look pretty!

PS you may need to change

Sub DoMerge()


to

Function DoMerge()


to get the /m switch working from WinWord.EXE.. not sure, try it an see.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Author Comment

by:pattersonr
ID: 18022327
Graham

I am working with your code, but I am evidentally missing something obvious.  I placed your code in a new word document and modified the file paths.
I then created a merge word doc by going through the mail merge steps and then saved the merge document.

However, when I try to step through the code it opens the merge document, but when it gets to the for loop it immediately skips everything and goes to the End of the loop.  I am supposing that it doesnt recognize the datasource or something...

Thoughts?

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 18024175
Strange. If you have set up a Main document, it should maintain the datasource.

Can you use it manually to produce Result document?
0
 
LVL 1

Author Comment

by:pattersonr
ID: 18029425
yes
0
 
LVL 1

Author Comment

by:pattersonr
ID: 18029434
Although I do notice
That when opening the document manually I get a Opening this document will run this SQL command...
I dont see that warning when opening through vba
I wonder if that is causing the problem?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 18029653
That will be it. Here is Microsoft's explanation and get round.
0
 
LVL 1

Author Comment

by:pattersonr
ID: 18029914
Graham...
was there supposed to be a link with your post?
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 18029958
0
 
LVL 1

Author Comment

by:pattersonr
ID: 18030890
ok graham...
Progress has been made...
I have been able to open the main word doc...manually run the sub and create the mail merge...
However,...
I can watch the first itteration of the loop open and create and save the first document...
Then the loop starts a second time and seems to complete...but not additional word document gets created..
Then the loop starts again and I get an error object deleted...

Help?
0
 
LVL 1

Author Comment

by:pattersonr
ID: 18030984
Graham..
I think I figured out the problem...Not sure yet how to fix...
When I run the code manually... The first pass works fine...
It mail merges the first record and creates the new word doc and saves...
However...It then closes that mail merged document...and the code jumps back to the for  each loop...
Now the code is sitting on the actual mail merge document and runs the for each loop and saves and closes the file...
Then the code jumps back to the beginning for r loop but has closed the mail merge file which results in an error....

It seems that it is the interior for loop that is causing the problem...

Thanks for the help


0
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 500 total points
ID: 18031272
It seemed to work OK for me, but perhaps it's worth explicitly exiting the For loop and also setting the result doc object to Nothing. Setting the ActiveRecord before the loop is a bit more elegant. In my mind the inner loop should iterate once only anyway. Perhaps it's to do with the respective order in which the Main and result document are found.
Try this anyway.

         .DataSource.ActiveRecord = r ' set active record
         For Each wdResultDoc In wdApp.Documents
                If wdResultDoc.FullName <> strMainDocPath Then
                    wdResultDoc.SaveAs strMergeResultsFolder & .DataSource.DataFields("AddressID") & ".doc"
                    Debug.Print "SaveAs " & strMergeResultsFolder & .DataSource.DataFields("AddressID") & ".doc"
                    wdResultDoc.Close False
                    Exit For ' found it, so exit
                End If
            Next wdResultDoc
            Set wdResultDoc = Nothing ' make sure that object is destroyed
0
 
LVL 1

Author Comment

by:pattersonr
ID: 18042420
Graham...

Things are moving right along...I can make the merge happen and save as individual files...
What I would like to add is the ability to print the documents before I close them...
The caveat here is I need to be able to search the datafield on each document to find out if it is an email address or if it is a fax number.

Basically....
IF datasource.datafield("EmailorFax").value find @ Then
 print to pdfprinter
ELSE
 print to fax printer
End IF

I have done all of the printing switching before...and I have done some stuff with selection...but I am looking at the whole document there.....
In this case I really only want to look at the value of the mergefield...if I have to search the whole document..I can but that is sort of overkill.
I guess I could also put into a word table and search the content of the cell....but I thought that the datafield approach had to be the best way.

Thanks again...getting much closer :)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now