473,320 Members | 2,071 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,320 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 8611

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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

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.