24
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
- Private Sub Form_Open(Cancel As Integer)
- Dim xcount As Integer
- Dim thisdoc As Object
- Dim st As String
- Dim ct, cn As Integer
- On Error GoTo Err_Form_Open
- Me.clientname = Forms!Orders1!clientname.Value
- Me.street = Forms!Orders1!bstreet.Value
- Me.suburb = Forms!Orders1!Bsuburb.Value
- Me.state = Forms!Orders1!bstate.Value
- Me.postcode = Forms!Orders1!bpostcode.Value
- Me.orderweek = Forms!Orders1!orderweek.Value
- Set wdApp = CreateObject("Word.Application")
- wdApp.Documents.Open "c:\gregw\Statement.doc"
- Call findword("INVOICE")
- st = Forms!Orders1!invno
- wdApp.selection.typetext st
- Call findword("name")
- Set thisdoc = wdApp.activedocument
- st = Me.clientname + vbNewLine
- wdApp.selection.typetext st
- st = Me.street + vbNewLine
- wdApp.selection.typetext st
- st = Me.suburb + vbNewLine
- wdApp.selection.typetext st
- st = Me.state + "," + Me.postcode
- wdApp.selection.typetext st
- ' fill in gap here -- eeerrr for the rest of the statement that is
- Set thisdoc = wdApp.activedocument
- cn = Me!info.ListCount
- For ct = 0 To cn - 1
- Me![info].Selected(ct) = True
- thisdoc.tables(3).cell(2 + ct, 1).range.Text = Me!info.Column(2)
- thisdoc.tables(3).cell(2 + ct, 3).range.Text = Me!info.Column(0)
- st = Format(Me!info.Column(3), "##,##0.00")
- thisdoc.tables(3).cell(2 + ct, 4).range.Text = "$" & st
- Next ct
- wdApp.Application.Visible = True
- Set wdApp = Nothing
- Set thisdoc = Nothing
- DoCmd.Close
- Exit_Form_Open:
- Exit Sub
- Err_Form_Open:
- MsgBox err.description
- Resume Exit_Form_Open
- End Sub
- Private Sub findword(st As String)
- wdApp.selection.Find.ClearFormatting
- wdApp.selection.Find.Replacement.ClearFormatting
- With wdApp.selection.Find
- .Text = st
- .MatchWildcards = False
- .Replacement.Text = "no name"
- .Wrap = wdFindStop
- .Forward = True
- End With
- wdApp.selection.Find.Execute ' Replace:=wdReplaceAll
- End Sub
Expand|Select|Wrap|Line Numbers
- 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] ;
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
