Re: ConvertToTable troubles with date fields formatted with "-" by Hl
Hl
Mon May 19 07:56:19 PDT 2008
<candide_sh@yahoo.de> wrote in message
news:372ea4e4-d8ca-404c-ac9e-5389518b9e57@27g2000hsf.googlegroups.com...
> 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