Multiple Database rows into document - Merge? 2

[virtualjon]

Hello all,

I want to represent some info from a database into a Word table. Each row in the table would represent a record in the database table.

Is it possible to do this using a merge type functionality in VB, where the extra record would create another row (as opposed to another document)?

Please help - this is doing my head in!

Thanks in advance.

 

[johnwm]

I would use the 'ConvertToTable' functionality in the Word object model.

Pseudocode solution:

Open recordset rst
Do While Not rst.EOF
For a = 0 to rst.Fields.Count - 1
myStr = myStr & rst.Fields(a) & "#"
next a
mystr = Left(myStr, Len(myStr) - 1)) & vbCRLF
' now you've got a # separated set of fields for a record
rst.MoveNext
Loop
' by now you've got the whole set as separate lines (by vbCRLF)
'Now with your wordoc open go to the place to insert the table
Set mydoc = ActiveDocument
Set myrange = ActiveDocument.Bookmarks("tableplace&quot.Range
' then post the text
myrange.InsertAfter mystr
' and use the 'convert function
With myrange
.ConvertToTable Separator:="#", _
NumColumns:=5, Format:=wdTableFormatGrid7, ApplyBorders:=True _
, ApplyShading:=True, ApplyFont:=True, ApplyColor:=True, ApplyHeadingRows _
:=False, ApplyLastRow:=False, ApplyFirstColumn:=False, ApplyLastColumn:= _
False, AutoFit:=True
End With

' and you're done

Don't forget to close the doc and close the wordapp and set it to Nothing when you're finished

All the functions are documented in Word VBHelp


________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[virtualjon]

Thanks very much johnwm ... will give that a try.

 

[PeterWallace]

johnwm

With the solution you have provided ....

The word document ... Is it a Template with the
Table already In Place or Is the Table generated by the code ?

 

[johnwm]

Table is generated by the code given. I show the use of a bookmark in the document to locate the table.

The example above is extracted from a 'jobs dispatched' report, so the prepared doc template contains header info and a bookmark for the table

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[PeterWallace]

Thamk You ....

if you do not mind

A 2 further questions

a>
If the table is Longer than the Page Length is there a mechanisn to *Next Page * ... with Headers etc.

b>

if one wanted to generate a table for a customer ( e.g. Receipts and Payments for month ) above would be fine... but how do you handle doig same thng for several customers

generate new table .. do above .. but would that not mean a seperate word document for each ?

Thank You

Peter Wallace

 

[johnwm]

a. No you will have to code it in.

b. Yes you can. Just use the same doc template. Do a query for the list of customers to process, then loop through the list using their name or cutomerID (or whatever) to generate the table info for that particular document

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[PeterWallace]

johnwm


Thanks for the help....

I have struck another problem ....

When I put in a Merged Table ...

How the *bad words* do I get to the end of it so I can
in sert some more text ...

at moment

S/B

bbbbbb lllllllll aaaaaaaaa hhhhhhhhhh
bbbbbb lllllllll aaaaaaaaa hhhhhhhhhh
bbbbbb lllllllll aaaaaaaaa hhhhhhhhhh
bbbbbb lllllllll aaaaaaaaa hhhhhhhhhh
bbbbbb lllllllll aaaaaaaaa hhhhhhhhhh

THE NEXT LINE

I GET

bbbbbb THE NEXT LINE hhhhhhhhhh
bbbbbb lllllllll aaaaaaaaa hhhhhhhhhh
bbbbbb lllllllll aaaaaaaaa hhhhhhhhhh
bbbbbb lllllllll aaaaaaaaa hhhhhhhhhh
bbbbbb lllllllll aaaaaaaaa hhhhhhhhhh
bbbbbb lllllllll aaaaaaaaa hhhhhhhhhh

Peter

 

[johnwm]

I'm not sure I entirely understand the problem, but if you have separate text to insert, you need to move the insert point before adding the next string. Easiest way is to move to a new bookmark.

See the Range object in WordVB Help - it gives some good examples

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'