473,224 Members | 1,369 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 software developers and data experts.

How to send query to formated word document

southoz
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
0 8597

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

Similar topics

0
by: Rolf Kemper | last post by:
Dear Experts, I want to send a formated query result by mail. I'm running SQL Server 2000 1) How can I achieve that the printed length of a field is based on the minimum required length to...
6
by: Nicolae Fieraru | last post by:
Hi All, I have a query, Select Count(BoolField) from tblMyTable, Where BoolField = true. If I run the query by itself, it returns the number of true records I want to use the result of that...
1
by: joeygun | last post by:
I'm trying to do something I thought would be easy, and maybe it is, but I've spent the afternoon now reading about bookmarks, templates, Word mail merge, VBA, automation and am seemingly no...
1
by: moelleni | last post by:
Hi, I made an Access 2002 Database and wanted to automate it to sent the current record to Word 2002. So readed the article "How to send the current record to Word 2000 with automation" I tried...
8
by: John Brock | last post by:
I am currently using a VB.NET program to send out e-mails with plain text bodies (plus one attachment). The emails are being received as Rich Text messages (probably just my personal Outlook...
3
by: sb Luis | last post by:
I Use the following code to read from MS Word, but I couldent read Formated Text (RTF). How Can I read formated text from MS Word Doc without using clipboard. thanks. Word.ApplicationClass...
10
by: Aj Blosser | last post by:
Hey guys, I have a question for you, I have a setup where I'm sending files through the POST to a php web page, I read the file contents, put that file contents as text into the POST string, and...
8
by: babyangel43 | last post by:
Hello, I have a query set up in Access. I run it monthly, changing "date of test". I would like this query to be merged with a Word document so that the cover letter is created in Word, the fields...
2
by: mburns | last post by:
Hello all- I was wondering if it is possible to link an Access query or table to a Word document and pull specific information into Word by manually entering a unique identifier, which would then...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.