How to print on word document

[vnk]

Hi guys!

I have written a word macro to access the sqlserver database
and retrieve data from a table. It looks like it works
fine with out any errors except it does not print the
retrieved records on to word document.

Here is the code,


Sub Macro()

Set objMstConn = CreateObject("ADODB.Connection&quot
objMstConn.Open "DSN=dsn;UID=id;PWD=pd"
Set objRS2 = CreateObject("ADODB.Recordset&quot
SQLRoleUser = "select col1, col2 from table1"
objRS2.Open SQLRoleUser, objMstConn
Do While Not objRS2.EOF
FieldValue1 = Trim(objRS2("col1&quot)
FieldValue2 = Trim(objRS2("col2&quot)

objRS2.MoveNext
Loop
End Sub

What am I missing here, SqlServer Gurus's help me out here.
Thanks In Advance.
VNK

 

[JJayUK]

I think that Word probably is retrieving the records but from your example it seems you're not telling it to print the records. Word doesn't do it automatically for you.

Try 'Selection.TypeText Text:=FieldValue1'
'Selection.TypeText Text:=FieldValue2'

You'll need to mess around with Word Basic to get the formatting right and this can be a hassle, but MSDN contains all the info you need on manipulating Word documents automatically. It can be found in the Office Developer Section.

After you've retrieved all the records.

 

[vnk]

Thanks JJayuk,

Its working but the problem is it doesn't print in a
tabular form. It is printing a chunk of data.
Any solution ?

Thanks,
Naveen.

 

[JJayUK]

You will need to continue to use Word Basic to add a table to the document and other formatting features.

To get you started this piece of code will create a table and add your data to each column.

Dim myRange As Range
Set myRange = ActiveDocument.Range(0, 0)

Documents(1).Tables.Add myRange, 1, 2
Documents(1).Tables(1).Cell(1, 1).Select
Selection.TypeText (FieldName1)
Documents(1).Tables(1).Cell(1, 2).Select
Selection.TypeText (FieldName2)

If, when using Macro editor in Word, you press F2 you will get the object browser which lists every object in word that can be controlled using macros along with every property and function. I recommend you try looking at some of the Table object information. There are nearly always examples as well.

Again, MSDN has a wealth of information.

 

[JJayUK]

...Another handy hint.... When in Visual Basic editor, go to tools and select references.

Scroll down until you see 'Microsoft ActiveX Data Objects' and check the box. Once that's done you don't have to use CreateObject() when creating you ADODB.Connection you can just declare an object of that type eg. 'Dim A as new ADODB.Connection'.