473,574 Members | 2,264 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to send query to formated word document

24 New Member
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
  7. On Error GoTo Err_Form_Open
  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
  16. Set wdApp = CreateObject("Word.Application")
  18. wdApp.Documents.Open "c:\gregw\Statement.doc" 
  20. Call findword("INVOICE")
  21. st = Forms!Orders1!invno
  22. wdApp.selection.typetext st
  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
  37. cn = Me!info.ListCount
  39. For ct = 0 To cn - 1
  40. Me![info].Selected(ct) = True
  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
  48. wdApp.Application.Visible = True
  50. Set wdApp = Nothing
  51. Set thisdoc = Nothing
  53. DoCmd.Close
  54. Exit_Form_Open:
  55.     Exit Sub
  57. Err_Form_Open:
  58.     MsgBox err.description
  59.     Resume Exit_Form_Open
  61. End Sub
  64. Private Sub findword(st As String)
  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
  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] ;
Now I open the document by using
Set wdApp = CreateObject("W ord.Application ")
wdApp.Documents .Open "c:\gregw\State ment.doc"

now we start to have some fun,

Call findword("INVOI CE")
st = Forms!Orders1!i nvno
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,po st 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.ListCou nt 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

Nov 7 '06 #1
0 8653

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

Similar topics

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 show the stored data uncut( Means the fieldlength is the maximum required length found in all items of a column ) ?
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 query in my VBA code, like this: If (result of the query > 0) then do something
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 farther along than I was when I started. All I want to do is link a dozen or so records (data type memo) from a query containing a single field into a...
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 to do this with my db. Here is my code: Private Sub MergeButton_Click() On Error GoTo MergeButton_Err
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 default, because I didn't do this in the program), but there is no actual formatting (italics, color, etc.) in the message body, which is passed to from...
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 WordApp = new Word.ApplicationClass(); ..... // Let's get the content from the document Word.Paragraphs DocPar = Doc.Paragraphs; // Count number of...
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 send it on it's way. it works perfectly for text files. However, I can't open a word document like a text file and have it give me the result I...
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 from Access are automatically filled into the Word document. The query could be anywhere from 0-5000 names, one cover letter per name. AND to this...
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 pull all related fields from Access to the Word document. My goal is to develop a Word template that pulls certain fields from an Access query or...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.