Export Query into Existing Table In Word

[naigy via AccessMonster.com]

Hi I have a word document which is used as a template for parts used on a
repair for a particular unit. This template has a table in it. I need to
export a query into this table in this word template to comply with company
policy. As such I have tried various code on different websites to do this
but they all seem to export the data into the one cell in this table
(including queryname and column headings which I don't want. The way I am
referencing the table in word is using a bookmark in the first cell on the
2nd row. The first row of the table is used for headings but these do not
match the field names of the related data in my database. Below is the code I
am currently using. Your assistance would be appreciated. Please note I did
post a query for this info on another site around a week ago and did not get
a response hence the reason I am requesting help here. The site was
http://www.dbforums.com/showthread.php?t=1212107 if you would like to check
before replying. Once again thanks for any assistance.

Private Sub Command62_Click()
On Error GoTo Err_Command62_Click
Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim PathDocu As String

Set oWord = CreateObject("Word.Application")
PathDocu = CurrentProject.Path
Set oDoc = oWord.Documents.Open(PathDocu & "\Writeoff.dot")
oWord.Visible = True

DoCmd.OpenQuery ("QWriteOffSummary")
DoCmd.RunCommand acCmdSelectAllRecords
DoCmd.RunCommand acCmdCopy
oDoc.Bookmarks("wot").Range.Paste

Exit Sub

Err_Command62_Click:
MsgBox "Error Printing Writeoff Request Form"

End Sub

 

[John Nurick]

I don't think there's any really easy way of doing this.

Probably the simplest is:

A:

1) Modify your query so the field names it returns are the ones you need
as headings in the Word table.

2) Replace the Word table with a { DATABASE } field that gets its data
from the query.

Otherwise, the general approaches are

B: Reference the table by using a bookmark that encloses it. Then you
can use something like

Dim oTable As Word.Table

Set oTable = oDoc.Bookmarks("wot").Range.Tables(1)

You can now open a recordset on the query and transfer the data field by
field with stuff like this (air code):

For R = 1 to rsR.RecordCount
If R >= oTable.Rows.Count Then
'Append a row to the Word table
End If
For j = 0 to rsR.Fields.Count
oTable.Rows(R+1).Fields(j+1).Range.Text = rsR.Fields(j).Value
Next j
rsr.MoveNext
Next

C: Copy and paste as you are trying now, but paste into a
suitably-shaped Range. This may need to be an entire row of the table,
or maybe a block of cells with enough columns and rows for your dataset.

 

[Tim Ferguson]

This template has a table in it. I need to
export a query into this table in this word template to comply with
company policy.

I have to confess that I would approach this the old-fashioned way, by
writing directly into the cells in the rows of the table:

this is **AIR CODE** so I haven't looked up any of the properties, but
you should get the picture...

' leave the titles row, scrap the rest
for rowNum = 1 to myTable.Rows.Count-1
myTable.Rows(rowNum).Delete
next rowNum

do while not myRecordset.EOF
with myTable.Rows.Add(wdAtTheBottom)
' formatting
.cells(0).Style = "IDNumberBold"

' insert the text
.cells(0).Range.Text = myRecordset.Fields("RecordID")
.cells(1).Range.Text = myRecordset.Fields("CatalogueName")
.cells(2).Range.Text = myRecordset.Fields("Description")

myRecordset.Movenext
end with

loop

The other advantage of this is that it doesn't screw up whatever your
user has carefully stored on the clipboard. Don't be Evil!!

Hope that helps


Tim F

 

[naigy via AccessMonster.com]

Thanks for your replies. At this stage Tims response looks the easiest if it
will work in my application the way I need it to. However do I need to
declare anything? Do I still use a bookmark in the table? If so where? and
how do I reference it into this code. Also how do I define the query which I
want it to run the code on and where do I put it in this code example. The
last question at this stage is the there our four columns in my table in word
but I need to populate col 1, 2 & 4. Is this just a matter of changing the
cells(x) to cells(4) for the last one. Column 3 is populated using bookmarks
that relate to fields on the form. After I get that working I will change the
code slightly to add the required number of blank rows. This company policy
is the biggest pain in the neck

Also what is this line for? In particular what does IDNumberBold relate to?
..cells(0).Style = "IDNumberBold"

I am sorry for being such a novice but I am in way over my head with this
project but my work has seen the start of it and are now on me to do these
few things. Thanks for your help

 

[Tim Ferguson]

However do I need to declare anything?

VBA is a (fairly) stongly typed language so you do need to Dim everything
before you use it.

Do I still use a bookmark in the table?

Up to you. The document has a Tables collection so if you know which
table you are looking for you can find it easily enough. On the other
hand, if it's already bookmarked, that makes it even quicker.

If
so where? and how do I reference it into this code.

Look up help for the Bookmarks collection; but this time you need to look
in the help for Visual Basic for Word, not for Access.

Also how do I
define the query which I want it to run the code on and where do I put
it in this code example.

I assumed you had already opened some kind of query or recordset. All
data access in Access (or, even, in Word) is managed using them.

The last question at this stage is the there
our four columns in my table in word but I need to populate col 1, 2 &
4. Is this just a matter of changing the cells(x) to cells(4) for the
last one. Column 3 is populated using bookmarks that relate to fields
on the form. After I get that working I will change the code slightly
to add the required number of blank rows. This company policy is the
biggest pain in the neck

I appreciate that I offered a very simplistic example of what text goes
where in your table. As long as you can describe in simple enough terms
the algorithm to get what you want where, then it's probably not too hard
to program.

Also what is this line for? In particular what does IDNumberBold
relate to? .cells(0).Style = "IDNumberBold"

I made that up. In MS Word, the best way to prettify anything is using
Styles rather than direct formatting. If I wanted all the ID Numbers to
look bold, I would create a style called IDNumberBold and apply it to the
cells that contain the ID Numbers. Programmatically, you apply a style to
text by using the .Style property. The syntax is pretty close to that
above, although I did not look it up.

I am sorry for being such a novice but I am in way over my head with
this project but my work has seen the start of it and are now on me to
do these few things. Thanks for your help

Programming one Office application is really no harder than any other;
you just have to know and get used to the object model in each (i.e.
Excel has Sheets and Rows and Columns; Word has Documents and Paragraphs
and (text) Ranges and so on). Programming _across_ applications is no
harder, in theory, although it's much harder to debug because you are
running code in things you can't see.

In fact, in this example, I would almost certainly do the whole thing
inside Word and not use Access at all. You can read your data by creating
a DBEngine and opening the database, and from there on opening recordset
etc is exactly the same as what you are used to. If you want to follow
this path, it's probably better to start in one of the the
microsoft.public.word.vba.general or ...vba.beginners groups. This is
probably not the place to start a whole 101 course in Word programming,
although that is indeed good fun to do. I once programmed Word to tell
the time... it's an analog clock with a second hand that actually ticks!

All the best


Tim F

 

[naigy via AccessMonster.com]

Thanks once again. I am having some difficulty setting the myRecordset
Variable to the query. Could you point me in the right direction with how to
set the query "QWriteOffSummary" to myRecordset it would be appreciated. I
have no problems opening the query with Docmd.OpenQuery ("QWriteOffSummary")
but can't define it to the required variable. Please see code below. All code
is on the access side and nothing in word at this stage. I would prefer to
keep it this way in case work changes the template slightly then I won't need
to recode it. Many thanks.

Private Sub Command62_Click()
Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim PathDocu As String

Set oWord = CreateObject("Word.Application")
PathDocu = CurrentProject.Path
Set oDoc = oWord.Documents.Open(PathDocu & "\Writeoff.dot")
Set oTable = oDoc.Tables(1)

Docmd.OpenQuery ("QWriteOffSummary")

oWord.Visible = True
For rowNum = 1 To oTable.Rows.Count - 1
oTable.Rows(rowNum).Delete
Next rowNum

Do While Not myRecordset.EOF 'THIS LINE ERRORS WITH
OBJECT REQUIRED. RUNTIME 424
With oTable.Rows.Add(wdAtTheBottom)
' formatting
.Cells(0).Style = "IDNumberBold"

' insert the text
.Cells(0).Range.Text = myRecordset.Fields("PartNumber")
.Cells(1).Range.Text = myRecordset.Fields("PartDescription")
.Cells(3).Range.Text = myRecordset.Fields("Quantity")

myRecordset.MoveNext
End With

Loop

Exit Sub

End Sub

However do I need to declare anything?

VBA is a (fairly) stongly typed language so you do need to Dim everything
before you use it.

Do I still use a bookmark in the table?

Up to you. The document has a Tables collection so if you know which
table you are looking for you can find it easily enough. On the other
hand, if it's already bookmarked, that makes it even quicker.

If
so where? and how do I reference it into this code.

Look up help for the Bookmarks collection; but this time you need to look
in the help for Visual Basic for Word, not for Access.

Also how do I
define the query which I want it to run the code on and where do I put
it in this code example.

I assumed you had already opened some kind of query or recordset. All
data access in Access (or, even, in Word) is managed using them.

The last question at this stage is the there
our four columns in my table in word but I need to populate col 1, 2 &

[quoted text clipped - 3 lines]

to add the required number of blank rows. This company policy is the
biggest pain in the neck

I appreciate that I offered a very simplistic example of what text goes
where in your table. As long as you can describe in simple enough terms
the algorithm to get what you want where, then it's probably not too hard
to program.

Also what is this line for? In particular what does IDNumberBold
relate to? .cells(0).Style = "IDNumberBold"

I made that up. In MS Word, the best way to prettify anything is using
Styles rather than direct formatting. If I wanted all the ID Numbers to
look bold, I would create a style called IDNumberBold and apply it to the
cells that contain the ID Numbers. Programmatically, you apply a style to
text by using the .Style property. The syntax is pretty close to that
above, although I did not look it up.

I am sorry for being such a novice but I am in way over my head with
this project but my work has seen the start of it and are now on me to
do these few things. Thanks for your help

Programming one Office application is really no harder than any other;
you just have to know and get used to the object model in each (i.e.
Excel has Sheets and Rows and Columns; Word has Documents and Paragraphs
and (text) Ranges and so on). Programming _across_ applications is no
harder, in theory, although it's much harder to debug because you are
running code in things you can't see.

In fact, in this example, I would almost certainly do the whole thing
inside Word and not use Access at all. You can read your data by creating
a DBEngine and opening the database, and from there on opening recordset
etc is exactly the same as what you are used to. If you want to follow
this path, it's probably better to start in one of the the
microsoft.public.word.vba.general or ...vba.beginners groups. This is
probably not the place to start a whole 101 course in Word programming,
although that is indeed good fun to do. I once programmed Word to tell
the time... it's an analog clock with a second hand that actually ticks!

All the best

Tim F

 

[Tim Ferguson]

@uwe:

Some thoughts...

Set oWord = CreateObject("Word.Application")
PathDocu = CurrentProject.Path

CurrentProject is a ADO property: as you are working with a Jet database,
you'd be better off with DAO. The equivalent command would be
pathDocu = CurrentDB().Name
pathDocu = left(pathdocu, len(pathdocu)-len(dir(pathdocu)))

Set oDoc = oWord.Documents.Open(PathDocu & "\Writeoff.dot")

Do you mean to open the template, rather than base a new document on the
template?

Set oTable = oDoc.Tables(1)
Okay

Docmd.OpenQuery ("QWriteOffSummary")

No: this just opens the query on the desktop for the user to look at. If
you want to get at the data inside, you need to do something like one of
these:

' DAO
dim myRecordset As DAO.Recordset
Set myRecordset = QueryDefs("QWriteOffSummary").OpenRecordset( _
dbOpenSnapshot, dbForwardOnly)

' ADO
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim recordsAffected As Long

Set conn = CurrentProject().Connection
Set rs = conn.Execute("QWriteOffSummary", _
recordsAffected, adCmdStoredProc)




Please note that code further down I suggested was air code, and may not
work!!

Hope that gets you moving a bit anyway. All the best


Tim F

 

[Tim Ferguson]

Please note that code further down I suggested was air code, and may
not work!!

Sorry for replying to my own post, but I've had a little play, and this
seems to work. Please note that I wrote this in Word; you just need to
set a reference to MS Data Access Objects 3.6

Public Sub GetMyData()

Const myDBPath As String = _
"C:\Documents and Settings\Ferguson\My Documents\My
Data\uncle.mdb"
Const queryName As String = "SmallestPNumbers"

Dim dbe As DBEngine
Dim db As Database
Dim rs As Recordset
Dim jetCommand As String

' get the database objects
Set dbe = New DAO.DBEngine
Set db = dbe.OpenDatabase(myDBPath, False, False)
Set rs = db.QueryDefs(queryName).OpenRecordset( _
dbOpenSnapshot, dbForwardOnly)

' now set up the word components
Dim tbl As Table
Dim currentRow As Row
Dim colNumber As Integer

' attach to first table in the document
Set tbl = ThisDocument.Tables(1)
' keep the headers but scrap everything else
For Each currentRow In tbl.Rows
If currentRow.Index > 1 Then currentRow.Delete
Next currentRow

' now go through the recordset
Do While Not rs.EOF
' add a new row to the table
Set currentRow = tbl.Rows.Add()
' just copy fields to the cells. Note that the
' cells starts at 1 and the fields starts at 0
' note: there is no checking for NULL values!
For colNumber = 1 To 3
currentRow.Cells(colNumber).Range.Text = _
rs.Fields(colNumber - 1).Value
Next colNumber

' don't forget to move on to the next record
rs.MoveNext

Loop

' tidy up
rs.Close
db.Close

End Sub



Hope that helps


Tim F

 

[naigy via AccessMonster.com]

Okay I think I am getting close. I can get this to work with a standard query
which doesn't rely on user input and it is outputing the data into the word
document the way I want. However when I try and run it on the query I need to
which looks up a value in the open form it fails with a runtime 3061. I have
found elsewhere that you need to specify the query parameters in the vba code.
However the only way I seem to be able to do that is with a querydef instead
of a recordset. I have tried to convert my code but can't quite get it right.
The fields in my parameter query are PartNumber, PartDescription, Quantity &
ServiceReportNumber. ServiceReportNumber is the field I need to equal the
value on the open form.

I am sorry for being a pain but I know I am so close now. The code below as
mentioned works on a version of the query which doesn't rely on a parameter.

Private Sub Command62_Click()
Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim PathDocu As String
Dim oTable As Table
Dim rowNum As Integer

Dim StartPos As Integer
Dim Position As Integer
Position = 2

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("QWriteOffSummary")

Set oWord = CreateObject("Word.Application")
PathDocu = CurrentDb().Name
PathDocu = Left(PathDocu, Len(PathDocu) - Len(Dir(PathDocu)))
Set oDoc = oWord.Documents.Open(PathDocu & "\Writeoff.dot")
Set oTable = oDoc.Tables(2)

oWord.Visible = True

For rowNum = 1 To oTable.Rows.Count - 1
' oTable.Rows(rowNum).Delete
Next rowNum

Do While Not rs.EOF
With oTable.Rows.Add
' formatting
'.Cells(0).Style = "IDNumberBold"

' insert the text

oTable.Cell(Position, 1).Range.Text = rs.Fields("PartNumber")
oTable.Cell(Position, 2).Range.Text = rs.Fields("PartDescription")
oTable.Cell(Position, 4).Range.Text = Nz(rs.Fields("Quantity"), 0)
Position = StartPos + Position + 1
rs.MoveNext
End With
Loop
Exit Sub
End Sub

 

[naigy via AccessMonster.com]

Okay I am now sorted. Thanks for all your assistance. Below is the code I
have used for your reference. The only thing I would like to change is the
following line to something that instead of adding a line to the table it
will edit the line in the table instead.

With oTable.Rows.Add


Sub PrintWriteOff_Click()
On Error GoTo Err_PrintWriteOff_Click
Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim PathDocu As String
Dim oTable As Table
Dim rowNum As Integer

Dim StartPos As Integer
Dim Position As Integer
Position = 2

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select * From QWriteOffSummary Where
ServiceReportNumber =" & """" & [Forms]![ServiceReport]![ServiceReportNumber]
& """")

Set oWord = CreateObject("Word.Application")
PathDocu = CurrentDb().Name
PathDocu = Left(PathDocu, Len(PathDocu) - Len(Dir(PathDocu)))
Set oDoc = oWord.Documents.Open(PathDocu & "\Writeoff.dot")
Set oTable = oDoc.Tables(2)

oWord.Visible = True

For rowNum = 1 To oTable.Rows.Count - 1
' oTable.Rows(rowNum).Delete
Next rowNum

Do While Not rs.EOF
With oTable.Rows.Add
'.Cells(0).Style = "IDNumberBold"

oTable.Cell(Position, 1).Range.Text = rs.Fields("PartNumber")
oTable.Cell(Position, 2).Range.Text = rs.Fields("PartDescription")
oTable.Cell(Position, 4).Range.Text = Nz(rs.Fields("Quantity"), 0)

Position = StartPos + Position + 1
rs.MoveNext
End With
Loop

oDoc.Bookmarks("Serial").Range.Text = Nz(Me.Serial.Value, "")
oDoc.Bookmarks("SDate").Range.Text = Nz(Me.ServiceDate.Value, "")
oDoc.Bookmarks("PartsFrom").Range.Text = Nz(Me.Origin.Value, "")
oDoc.Bookmarks("SReport").Range.Text = Nz(Me.ServiceReportNumber.Value, "")
oDoc.Bookmarks("Customer").Range.Text = Nz(Me.Owner.Value, "")
oDoc.Bookmarks("Technican").Range.Text = Nz(Me.Technican.Value, "")
oDoc.Bookmarks("Technican2").Range.Text = Nz(Me.Technican.Value, "")
oDoc.Bookmarks("Technican3").Range.Text = Nz(Me.Technican.Value, "")
Exit Sub

Err_PrintWriteOff_Click:
MsgBox "There has been an error while trying to print the write off
request form"
End Sub

 

[Tim Ferguson]

Okay I am now sorted. Thanks for all your assistance. Below is the
code I have used for your reference.

First comment is that it's always a good idea to code with Option Explicit
enabled.

The only thing I would like to
change is the following line to something that instead of adding a
line to the table it will edit the line in the table instead.

In that case, just iterate the Cells collection of the appropriate Row
object. How you find which is the correct row is, of course, up to you!
Further details probably should be taken up in a Word programming group
rather than here, because it's not a database question really.

Well done
All the best


Tim F