By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,665 Members | 1,576 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,665 IT Pros & Developers. It's quick & easy.

How to send query to formated word document

southoz
P: 24
Good ay all ,
I'm fairly new to access(a little over 5 weeks now). Since I'v started I have picked up a lot of useful information from forums such as this and in doing so will share that information with others. I have seen many request for information on mail merging an how to send data to word documents this is something I have put together with the answers given by others and I hope this will help a few newbies , I know there are easier ways (and a lot of the older members wil of cause have better answers) but once again this is from a newbie to newbies to help then get started. First of all I use a modified template for the word document this can be downloaded from the office template stite , I have modified the document by replacing the #100 text on the document with the word "INVOICE" ,second I have removed the merge fields and just replaced then with the text "name" (this will be explained later).
I have a form called Orders1 (the name of the form is ofcause self explanitory)
on the form I have a command button that asks if you want invoices or statements for this example I'll be using statements ( weekly statements), on activation of the statements form ( i use the Form_Open sub routine) I use the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. Dim xcount As Integer
  3. Dim thisdoc As Object
  4. Dim st As String
  5. Dim ct, cn As Integer
  6.  
  7. On Error GoTo Err_Form_Open
  8.  
  9. Me.clientname = Forms!Orders1!clientname.Value
  10. Me.street = Forms!Orders1!bstreet.Value
  11. Me.suburb = Forms!Orders1!Bsuburb.Value
  12. Me.state = Forms!Orders1!bstate.Value
  13. Me.postcode = Forms!Orders1!bpostcode.Value
  14. Me.orderweek = Forms!Orders1!orderweek.Value
  15.  
  16. Set wdApp = CreateObject("Word.Application")
  17.  
  18. wdApp.Documents.Open "c:\gregw\Statement.doc" 
  19.  
  20. Call findword("INVOICE")
  21. st = Forms!Orders1!invno
  22. wdApp.selection.typetext st
  23.  
  24. Call findword("name")
  25. Set thisdoc = wdApp.activedocument
  26. st = Me.clientname + vbNewLine
  27. wdApp.selection.typetext st
  28. st = Me.street + vbNewLine
  29. wdApp.selection.typetext st
  30. st = Me.suburb + vbNewLine
  31. wdApp.selection.typetext st
  32. st = Me.state + "," + Me.postcode
  33. wdApp.selection.typetext st
  34. ' fill in gap here -- eeerrr for the rest of the statement that is
  35. Set thisdoc = wdApp.activedocument
  36.  
  37. cn = Me!info.ListCount
  38.  
  39. For ct = 0 To cn - 1
  40. Me![info].Selected(ct) = True
  41.  
  42. thisdoc.tables(3).cell(2 + ct, 1).range.Text = Me!info.Column(2)
  43. thisdoc.tables(3).cell(2 + ct, 3).range.Text = Me!info.Column(0)
  44. st = Format(Me!info.Column(3), "##,##0.00")
  45. thisdoc.tables(3).cell(2 + ct, 4).range.Text = "$" & st
  46. Next ct
  47.  
  48. wdApp.Application.Visible = True
  49.  
  50. Set wdApp = Nothing
  51. Set thisdoc = Nothing
  52.  
  53. DoCmd.Close
  54. Exit_Form_Open:
  55.     Exit Sub
  56.  
  57. Err_Form_Open:
  58.     MsgBox err.description
  59.     Resume Exit_Form_Open
  60.  
  61. End Sub
  62.  
  63.  
  64. Private Sub findword(st As String)
  65.  
  66. wdApp.selection.Find.ClearFormatting
  67. wdApp.selection.Find.Replacement.ClearFormatting
  68. With wdApp.selection.Find
  69.     .Text = st
  70.     .MatchWildcards = False
  71.     .Replacement.Text = "no name"
  72.     .Wrap = wdFindStop
  73.     .Forward = True
  74. End With
  75. wdApp.selection.Find.Execute ' Replace:=wdReplaceAll
  76.  
  77. End Sub
From the above code you can see that the statement form is being filled with the data from the orders1 form . On a list box (named info) I use a query for the source and use the following as the row source argument
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT statement.OrderNumber,statement.clientname,statement.pickupdate,statement.subprice,statement.orderweek  FROM statement  WHERE [statement.orderweek] = Forms![Orders1]![orderweek] AND [statement.clientname] = Forms![Orders1]![clientname] ;
  2.  
Now I open the document by using
Set wdApp = CreateObject("Word.Application")
wdApp.Documents.Open "c:\gregw\Statement.doc"

now we start to have some fun,

Call findword("INVOICE")
st = Forms!Orders1!invno
wdApp.selection.typetext st

by using the findword subroutine we find the word "INVOICE" on the word document an highlite it , the string st is filled with the data from the invno textbox on the orders1 form (invno being the invoice number). the wdApp.selection.typetext command then replaces the word INVOICE with string st (dont forget that invoice is highlited , this is the same as highliting text on a document an pasting over it). Now we look for the word "name" on the document (the word name has replaced the mergefields from the org document)
I do the same as I did with the invoice number but this time I have added + vbNewLine on the end of the st string. This will make the the cursor on the document to go down to the next line so that the next line will be filled in with the company address . This is repeated until all the proper address formats are on the document (eg : street , suburb,state,post code). all on a seperate lines , the next thing to do is start getting data from our query in the list box called info.
to do this I have used cn = Me!info.ListCount to count the rows of the list box then start a loop (For ct = 0 To cn - 1) to go through each row, to select the row needed I use Me![info].Selected(ct) = True , this now gives us access to each row an column in the list box so the cells in the formated word document can start being filled. I do this by using ' thisdoc.tables(3).cell(2 + ct, 1).range.Text = Me!info.Column(2) ' tables(3) is ofcause is the third table on the document with cell(2 + ct, 1) being the selected cell . This cell is filled in with the data from my statement form (Me!) and the Info.Column(2) being the selected column of the selected row(long winded I know) ,as you can see by the code you can use this method to fill in any table with any information from a list box query (and others) it's easy to do, an the document can be formated any way you want it to be. As I have said I'm new at this myself but once again hope it can help others that have had problems with learning how these things work.
If you need any other info on this I have a working demo database that can be downloaded from my site , just send a PM to get it
all the best

southoz
Nov 7 '06 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.