Output information to a Word table?

Topic

New Reply

Is there syntax to output information to Word in such a way as it will go into a table? My client wants the info to be in a grid with lines. I don’t have any problems outputting to Word using the Word object, I just want to get a handle on what is involved to put it in table form.

Reply | Quote

Replies

Is there syntax to output information to Word in such a way as it will go into a table? My client wants the info to be in a grid with lines. I don’t have any problems outputting to Word using the Word object, I just want to get a handle on what is involved to put it in table form.

It is possible to do, but the automation code to do it is quite complex as you have to construct the table in VBA and then keep track of where you are with indexes and the like. If I were doing it, I would probably export the data to Excel and then paste the Excel cells into Word as a table. Neither approach is trivial, but it is definitely doable.

Reply | Quote

Here is an example. It uses the following variables:

Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim wrdTbl As Word.Table
Dim c As Integer
Dim n As Integer
Dim r As Integer
Dim rst As DAO.Recordset

I will assume that wrdApp and wrdDoc have already been set, and that the recordset rst has already been opened. The following code creates a table in the document with as many columns as there are fields in the recordset. It fills the first row with the field names, then adds a row for each record.

Code:

n = rst.Fields.Count
' Create table
Set wrdTbl = wrdDoc.Tables.Add(Range:=wrdApp.Selection.Range, NumRows:=1, NumColumns:=n)
r = 1
' Fill the first row with field names
For c = 1 To n
  wrdTbl.Cell(r, c).Range.Text = rst.Fields(c - 1).Name
Next c
' Loop through the records
Do While Not rst.EOF
  r = r + 1
  ' Add a row
  wrdTbl.Rows.Add
  ' Fill from record
  For c = 1 To n
	wrdTbl.Cell(r, c).Range.Text = rst.Fields(c - 1).Value
  Next c
  rst.MoveNext
Loop

Followed by the usual cleanup (close the recordset etc.)

Reply | Quote

Many thanks Hans. This should be enough to get me started. I’ll post back if I run into any snags.

Reply | Quote

Here is an example. It uses the following variables:

Dim wrdApp As Word.Document
Dim wrdDoc As Word.Document
Dim wrdTbl As Word.Table
Dim c As Integer
Dim n As Integer
Dim r As Integer
Dim rst As DAO.Recordset

Hans, I was just looking at this. In your declarations your first one is wrdApp as Word.Document. Are both wrdApp and wrdDoc documents or should wrdApp be Word.Application? It certainly makes more sense to me given your naming of the variable.

Reply | Quote

Hans, I was just looking at this. In your declarations your first one is wrdApp as Word.Document. Are both wrdApp and wrdDoc documents or should wrdApp be Word.Application? It certainly makes more sense to me given your naming of the variable.

You are entirely correct. It should be

Dim wrdApp As Word.Application

I have corrected my reply. Thanks for pointing out my mistake!

Reply | Quote

OK. I have got this far.

The next thing I am trying to do is open a template and set the insertion point at a specific spot – bkmk1 (bookmark1) and then insert the table there.

In fact, since I already know what the column headings will be, just not how many rows there will be, what I would ideally like to do is have a table set up in the template with the column headings already there and formatted then start adding the data into the second row (either create the second row and start adding from there or start at a blank second row and add in additional ones as needed). I assume this would only mean dumping the three lines of code that insert the field names.

The problem I have with the current code is that it doesn’t seem to actually go to the bookmark, or if it does, it then goes back to the beginning and inserts the table there. I am very limited in my understanding of the Word Object and ranges so I can only assume that

Set wrdTbl = wrdDoc.Tables.Add(Range:=Selection.Range, NumRows:=1, NumColumns:=n)

is going back to the beginning.

Code:

Option Compare Database
Option Explicit
Private Const m_strDIR		  As String = "C:Documents and SettingsPNMy DocumentsDataDump"

Public Sub WordClin()
Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim wrdTbl As Word.Table
Dim c As Integer
Dim n As Integer
Dim r As Integer
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim dteStart As Date
Dim dteEnd As Date

Set wrdApp = New Word.Application
wrdApp.Documents.Open (m_strDIR & "NIS - Template.DOC")
Set wrdDoc = Word.ActiveDocument
	
dteStart = #4/1/2009#
dteEnd = #6/30/2009#

Set dbs = CurrentDb()
   
	Set qdf = dbs.QueryDefs("aaa")
	qdf.Parameters("[forms]![frmISAPDates]![txtStartDate]") = dteStart
	qdf.Parameters("[forms]![frmISAPDates]![txtEndDate]") = dteEnd

Set rst = qdf.OpenRecordset(dbOpenDynaset)
n = rst.Fields.Count

wrdDoc.GoTo what:=wdGoToBookmark, Name:="Bkmk1"

' Create table

 Set wrdTbl = wrdDoc.Tables.Add(Range:=wrdApp.Selection.Range, NumRows:=1, NumColumns:=n)


r = 1
' Fill the first row with field names
For c = 1 To n
  wrdTbl.Cell(r, c).Range.Text = rst.Fields(c - 1).Name
Next c
' Loop through the records
Do While Not rst.EOF
  r = r + 1
  ' Add a row
  wrdTbl.Rows.Add
  ' Fill from record
  For c = 1 To n
	wrdTbl.Cell(r, c).Range.Text = Nz(rst.Fields(c - 1).Value, 0)
  Next c
  rst.MoveNext
  Loop

wrdDoc.SaveAs FileName:=m_strDIR & _
		"NIS - " & FormatDateTime(Date, vbLongDate) & ".DOC"
	wrdDoc.Close
	wrdApp.Quit

	 ' clean up
	Set wrdDoc = Nothing
	Set wrdApp = Nothing
	rst.Close
	Set rst = Nothing


End Sub

Reply | Quote

Never mind about the range question, I changed this

Set wrdTbl = wrdDoc.Tables.Add(Range:=wrdApp.Selection.Range, NumRows:=1, NumColumns:=n)

to this

Set wrdTbl = wrdDoc.Tables.Add(Range:=ActiveDocument.Bookmarks(1).Range, NumRows:=1, NumColumns:=n)

Nuthin I hate worse than being defeated by VBA and obscure, circular help files

However, I still would like to move on to the next bit which is adding data into a pre-existing table with headers.

I assume now that this means the above solution will be redundant since I won’t need to create the table but rather go to a specific range within an existing one

Reply | Quote

Let’s say that your predefined table is the second table in the document (starting from the top). You can replace

Set wrdTbl = wrdDoc.Tables.Add(…)

with

Set wrdTbl = wrdDoc.Tables(2)

Populating the table remains the same.

Reply | Quote

Making splendid progress. Could not have done this without your help.

Next question: Is there a way for Word to be opened either during the output process or at the end of the process so that the outputted document displays as it would if you chose OutputTo for a report?

Reply | Quote

An instance of Word created by Automation is hidden by default. You can make it visible by inserting a line

wrdApp.Visible = True

at the point where you want it (after creating the instance, of course)

Reply | Quote

An instance of Word created by Automation is hidden by default. You can make it visible by inserting a line

wrdApp.Visible = True

at the point where you want it (after creating the instance, of course)

Okay. As usual, a simple solution if you know where to look.

Last item for this test phase which may or may not be a problem for implementation.

Right now, I am running the sub from the Immediate window for testing. The first time I run it, it runs fine and the instance of Winword.exe is not showing up in the task manager after the sub is done. When I go and run it the second time, I get runtime error 642: The remote server machine does not exist or is unavailable. When I click on the debug button, it is hanging on the line Set wrdDoc = Word.ActiveDocument. If I end the sub, go to the task manager and manually kill winword.exe and run it the third time, then it is fine again. This process also leaves a word temp file in the folder where my template is.

So the question: Is this just something annoying to live with in the development phase or is this going to be a problem for implementation? Is there a reason why it is only happening every other time I run the sub?

Reply | Quote

When using Automation to control Word, EVERY Word object that you use must refer directly or indirectly to the Word.Application object that you create, in your code wrdApp, otherwise you’ll create an extra instance of Word that is not controlled by your application and that will remain in memory. In the line

Set wrdDoc = Word.ActiveDocument

Word is a generic object, not your own wrdApp object. You should change it to

Set wrdDoc = wrdApp.ActiveDocument

Or even better, combine the two lines

wrdApp.Documents.Open (m_strDIR & “NIS – Template.DOC”)
Set wrdDoc = Word.ActiveDocument

to

Set wrdDoc = wrdApp.Documents.Open (m_strDIR & “NIS – Template.DOC”)

Reply | Quote

When using Automation to control Word, EVERY Word object that you use must refer directly or indirectly to the Word.Application object that you create, in your code wrdApp, otherwise you’ll create an extra instance of Word that is not controlled by your application and that will remain in memory.

Thanks so much Hans. that got it. a three beer effort!

Reply | Quote

I haven’t done much Word programming recently but if you’re driving the process from Word (not pushing data from Access) have a look at the InsertDatabase method of the Word Range object…

Reply | Quote

I haven’t done much Word programming recently but if you’re driving the process from Word (not pushing data from Access) have a look at the InsertDatabase method of the Word Range object…

Thanks. I’ll look at this. This particular process needs to be driven from inside Access.

Reply | Quote