pattersonr
asked on
Word Automation with SQL server Data
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...
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...
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.
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.
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:\M
With wdDoc.MailMerge
.Destination = wdSendToEmail
.Execute
End With
wdDoc.Close False
wdDoc.Quit
End Sub
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:\M
With wdDoc.MailMerge
.Destination = wdSendToEmail
.Execute
End With
wdDoc.Close False
wdDoc.Quit
End Sub
ASKER
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..:)
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..:)
ASKER
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.
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.
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.
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.
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.
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.
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:\M
Set wdDoc = Documents.Open("C:\MyPath\
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:\M
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.
Sub DoMerge()
'Dim wdApp As Word.Application
Dim wdDoc As Word.Document
'Set wdApp = New Word.Application
'Set wdDoc = wdApp.Documents.Open("C:\M
Set wdDoc = Documents.Open("C:\MyPath\
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:\M
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.
ASKER
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
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
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:\D
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("Ad
Debug.Print "SaveAs " & strMergeResultsFolder & .DataSource.DataFields("Ad
wdResultDoc.Close False
End If
Next wdResultDoc
Next r
End With
wdDoc.Close False
wdApp.Quit
End Sub
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:\D
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("Ad
Debug.Print "SaveAs " & strMergeResultsFolder & .DataSource.DataFields("Ad
wdResultDoc.Close False
End If
Next wdResultDoc
Next r
End With
wdDoc.Close False
wdApp.Quit
End Sub
>> 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
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.
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
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.
ASKER
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?
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?
Strange. If you have set up a Main document, it should maintain the datasource.
Can you use it manually to produce Result document?
Can you use it manually to produce Result document?
ASKER
yes
ASKER
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?
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?
That will be it. Here is Microsoft's explanation and get round.
ASKER
Graham...
was there supposed to be a link with your post?
was there supposed to be a link with your post?
ASKER
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?
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?
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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("Emai
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 :)
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("Emai
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 :)
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.