Hello,
I'm using VBA-code from Access to build and fill a word table.
I've got a ADO-Recordset with data like these:
ADE 10-08-2007 11-08-2007
TRW 04-22-2008 04-23-2008
code-snippet:
Public Function fctCreateTableFromRecordsetMON(rngAny As Word.Range _
, rstAny As ADODB.Recordset _
, Optional fIncludeFieldNames
As Boolean = False _
) As Word.Table
Dim objTable As Word.Table
Dim fldAny As ADODB.Field
Dim varData As Variant
Dim strBookmark As String
Dim cField As Long
' Get the data from the recordset
varData = rstAny.GetString()
' Create the word-table
'---+++---+++---+++---+++
With rngAny
.InsertAfter varData
Set objTable = .ConvertToTable()
.
.
.
End With
'---+++---+++---+++---+++
Set fctCreateTableFromRecordsetMON = objTable
End Function
The problem is, the two dates in every Recordset row have a minus-sign
as separator and this seems to trouble the word table since word
appends two columns to the word-table and writes some stuff of the
dates into.
So the ConvertToTable-command doesn't work right for these date
fields. How can I simply insert date fields formatted with "-"?
thanks
candide_sh
Hi
Here is a way to do what you want.
========================================================================
Private Sub GetData()
'****************************************************
' be sure to create a reference to the ado library
'****************************************************
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim strQuery As String
Dim iRow As Long, iCol As Long, iNumRows As Long, iNumColumns As Long
Dim vData As Variant
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
' set the connection string
cn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=c:\db2.mdb;" _
& "Persist Security Info=False"
' open the connection
cn.Open
' query to retrieve the records
strQuery = "SELECT f1,f2,f3,f4 FROM tblData"
' open the recordset
rs.Open strQuery, cn, adOpenDynamic, adLockBatchOptimistic
' fill the variant array with the data
vData = rs.GetRows
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
' get the number or rows and columns needed for the table
'iNumRows = (UBound(vData, 2) - LBound(vData, 2)) + 1
'iNumColumns = (UBound(vData, 1) - LBound(vData, 1)) + 1
' note: this is probably OK because I believe GetRows always returns a 0
based array
' or use the above just to be sure
iNumRows = UBound(vData, 2) + 1
iNumColumns = UBound(vData, 1) + 1
' create a table of the appropiate size to handle the data
ActiveDocument.Tables.Add Range:=Selection.Range, _
NumRows:=iNumRows, _
NumColumns:=iNumColumns, _
DefaultTableBehavior:=wdWord9TableBehavior, _
AutoFitBehavior:=wdAutoFitFixed
' fill the table
With ActiveDocument.Tables(1)
For iRow = 1 To .Rows.Count
For iCol = 1 To .Columns.Count
.Cell(iRow, iCol).Range.Text = vData(iCol - 1, iRow - 1)
Next
Next
End With
End Sub
============================================================================
Good luck
Harold
