passing data from Access and converting it to a nested table

[Piet Linden]

I'm using Access 2002 and Word 2002 (Word 10)

I'm doing this from Access, if it matters. I'm opening a recordset
and using GetString to convert it to a delimited string. Once that's
done, I'm stuffing that into a cell in a Word table. I then want to
convert the delimited string in the cell to a table. It almost works
but I get a 1-row by 2-column table at the top of my Table Cell in
Word. What do I need to do to fix that?

Here's the code... I'm basically passing the contents of an Access
table to Word. (It's a small table!)

Get the data:
Private Sub cmdWriteWord_Click()
' This works as is.
' Date created: 2/8/2009 1:46:56 AM
' Purpose: Writes a single recordset to a cell in a word document
and converts it to a table.
Dim rsResult As ADODB.Recordset
Dim strResult As String
Dim strHeader As String
Dim intField As Integer


Set rsResult = New ADODB.Recordset
rsResult.Open "TEMP_XTB_RESULTS", CurrentProject.Connection,
adOpenForwardOnly, adLockReadOnly

For intField = 0 To rsResult.Fields.Count - 1
If intField > 0 Then
strHeader = strHeader & vbTab & rsResult.Fields
(intField).Name
Else
strHeader = rsResult.Fields(intField).Name
End If
Next intField


strResult = rsResult.GetString(adClipString, -1, vbTab, vbCrLf,
"")
strResult = strHeader & vbCrLf & strResult

Call WriteWord(Me.txtWordFile, 3, 3, strResult)

rsResult.Close
Set rsResult = Nothing
MsgBox "Job's done..."
End Sub

Anything noticeably wrong? I sure can't figure it out! Thanks!
Pieter

 

[Doug Robbins - Word MVP]

It would help if you would show the code for your WriteWord()

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Piet Linden]

It would help if you would show the code for your  WriteWord()

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Sorry about that! Getting tired and myopic....

Private Sub WriteWord(ByVal strDoc As String, ByVal lngRow As Long,
ByVal lngColumn As Long, ByVal varWriteSomething)
Dim appWord As Word.Application
Dim docWord As Word.Document

Set appWord = New Word.Application
Set docWord = appWord.Documents.Open(strDoc)

docWord.Tables(1).Cell(lngRow, lngColumn).Range.Text =
varWriteSomething

docWord.Close True
Set docWord = Nothing
appWord.Quit
Set appWord = Nothing
End Sub

Do I need the .RecordCount and .Fields.Count properties of the
recordset object for this? Maybe I'm just working in circles now...

Thanks!

Pieter

 

[Doug Robbins - Word MVP]

I do not see anything in your code that is attempting to convert the text
into a table. Try the following modified code

Private Sub WriteWord(ByVal strDoc As String, ByVal lngRow As Long,
ByVal lngColumn As Long, ByVal varWriteSomething)
Dim appWord As Word.Application
Dim docWord As Word.Document
Dim docRange as Word.Range
Set appWord = New Word.Application
Set docWord = appWord.Documents.Open(strDoc)

With docWord.Tables(1).Cell(lngRow, lngColumn)
.Range.Text = varWriteSomething
Set docRange = .Range
With docRange
.End = .End - 1
.ConverttoTable
End With
End With

docWord.Close True
Set docWord = Nothing
appWord.Quit
Set appWord = Nothing
End Sub

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

It would help if you would show the code for your WriteWord()

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

Sorry about that! Getting tired and myopic....

Private Sub WriteWord(ByVal strDoc As String, ByVal lngRow As Long,
ByVal lngColumn As Long, ByVal varWriteSomething)
Dim appWord As Word.Application
Dim docWord As Word.Document

Set appWord = New Word.Application
Set docWord = appWord.Documents.Open(strDoc)

docWord.Tables(1).Cell(lngRow, lngColumn).Range.Text =
varWriteSomething

docWord.Close True
Set docWord = Nothing
appWord.Quit
Set appWord = Nothing
End Sub

Do I need the .RecordCount and .Fields.Count properties of the
recordset object for this? Maybe I'm just working in circles now...

Thanks!

Pieter

 

[Piet Linden]

I do not see anything in your code that is attempting to convert the text
into a table.  Try the following modified code

Private Sub WriteWord(ByVal strDoc As String, ByVal lngRow As Long,
ByVal lngColumn As Long, ByVal varWriteSomething)
    Dim appWord As Word.Application
    Dim docWord As Word.Document
    Dim docRange as Word.Range
    Set appWord = New Word.Application
    Set docWord = appWord.Documents.Open(strDoc)

    With docWord.Tables(1).Cell(lngRow, lngColumn)
        .Range.Text = varWriteSomething
        Set docRange = .Range
        With docRange
            .End = .End - 1
            .ConverttoTable
        End With
    End With

    docWord.Close True
    Set docWord = Nothing
    appWord.Quit
    Set appWord = Nothing
End Sub

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP






Sorry about that!  Getting tired and myopic....

Private Sub WriteWord(ByVal strDoc As String, ByVal lngRow As Long,
ByVal lngColumn As Long, ByVal varWriteSomething)
    Dim appWord As Word.Application
    Dim docWord As Word.Document

    Set appWord = New Word.Application
    Set docWord = appWord.Documents.Open(strDoc)

    docWord.Tables(1).Cell(lngRow, lngColumn).Range.Text =
varWriteSomething

    docWord.Close True
    Set docWord = Nothing
    appWord.Quit
    Set appWord = Nothing
End Sub

Do I need the .RecordCount and .Fields.Count properties of the
recordset object for this?  Maybe I'm just working in circles now...

Thanks!

Pieter

Oh, I see where I went wrong! Cool, thanks Doug!