ADH automating Word as ReportWriter - -- ConvertToTable issues

  • Thread starter Thread starter pietlinden
  • Start date Start date
P

pietlinden

I'm using Access & Word 2002 and basically using Word as a report
writer in a manner fairly similar to what is done in the book. Don't
think it matters terribly, but I'm using the MultiPik form to select
and order the queries for "export".

The major differences in my case are:
1. I am sending several recordsets to Word (based on the queries)
2. I am sending each recordset to a single cell in a Word table and
*then* [attempting] to convert the contents to a table.

I can open the recordsets fine and send the results to Word, but I
can't seem to figure out how to code the Word automation to convert
the contents of the cell to a nested table.

Here's my code... complete with chunks that don't work...

Private Sub cmdCreateWordReport_Click()
Dim appWord As Word.Application
Dim docWord As Word.Document
Dim wrdRange As Word.Range
'Dim fldAny As ADODB.Field
Dim strFieldList As String
Dim strData As String
'Dim rsAny As New ADODB.Recordset
Dim lngRow As Long
Dim bytQueryType As Byte

Dim aSelected() As Variant ' to put the selected items into the
array
Dim varItem As Variant ' to process the listbox items

' Launch Word and load the invoice template
Set appWord = New Word.Application

appWord.Documents.Open Application.CurrentProject.Path &
"\TestingTable.doc"
appWord.Visible = True

Set docWord = appWord.ActiveDocument

' Get details from database and create a table
' in the document
' Get an array filled with the selected items.
aSelected = mmp.SelectedItems

For Each varItem In aSelected
'-- just return an ADO recordset
If DBEngine(0)(0).QueryDefs(varItem).Type = dbQSelect Then
strData = Select_to_rsADO(varItem)
ElseIf QueryType(varItem) = "Crosstab" Then
strData = XTB_to_rsADO(varItem)
End If

With docWord.Tables(1)
.Cell(.Rows.Count, 1).Range = varItem
.Cell(.Rows.Count, 3).Range = strData
'test for ubound(aSelected)
.Rows.Add
.Rows.Add
End With
Set wrdRange = docWord.Tables(1).Cell(lngRow, 3).Range

'-- doesn't work!
'With wrdRange
' .Start = 1
' .End = .End - 1

'---THIS IS THE PROBLEM LINE.....
' .ConvertToTable vbTab, , , , , True

' End With

lngRow = docWord.Tables(1).Rows.Count + 1

Next varItem


' Apply formatting
'.AutoFormat wdTableFormatProfessional
'.AutoFitBehavior wdAutoFitContent

' Fix up paragraph alignment
'.Range.ParagraphFormat.Alignment = wdAlignParagraphRight
'.Columns(1).Select
'objWord.Selection.ParagraphFormat.Alignment =
wdAlignParagraphLeft
'objWord.Selection.MoveDown

Set docWord = Nothing
Set appWord = Nothing
End Sub

The only part I am hung up on is the ConvertToTable piece. The rest
works fine. Do I need the recordset to be visible to the Word
document/application, because it seems that I need to pass in the
dimensions of the table (rs.RecordCount, rs.Fields.Count) in order for
the delimited string to be converted correctly.

Any idea how to get this to work?

Thanks!
Pieter
 
The other odd thing I can't figure out... I have a function that
returns an ADO recordset (so I can see the column names and use
GetString and all that to convert it to a table in Word), but when I
try to return a recordset to a function, for some reason the
"receiving" recordset fails...

For example...

Function TurnIntoRecordset (byval strQName as string) As
ADODB.Recordset
dim bytQueryType as byte
dim rsReturn as ADODB.Recordset
Set rsReturn= new adodb.recordset

bytQueryType=dbengine(0)(0).Querydefs(strQname).Type

if bytQueryType=dbQSelect then
rsReturn.Open(strQName,currentproject.connection)
elseif bytQueryType=dbQCrosstab then
DbEngine(0)(0).Execute "DELETE * FROM tblXTB", dbFailOnError
DBEngine(0)(0).Execute "qappXTB_to_tbXTB"
rsReturn.Open("tblXTB")
End If

Set TurnIntoRecordset=rsReturn

End Function

but when I call the function to return the recordset, I get nothing.
If I remember right, I get an invalid assignment error.

Any idea how to do this right?

Thanks!
Pieter
 
Back
Top