Kelvin Sparks
asked on
Exporting data to Word Table
Hi Experts
I looking for some quick help here. The following code is from a reference by Liwin, Getz & Gunderloy.
It takes data from an ADO recordset and populates a table. In my case I have a recordset made up from data in two Memo fields. The Enter key has been used to create linebreaks and blank lines within the data. This is creating a problem with the .ConvertToTable Method. The getstring method that has optional parameters as follows
StringFormat
A StringFormatEnum value that specifies how the Recordset should be converted to a string. The RowDelimiter, ColumnDelimiter, and NullExpr parameters are used only with a StringFormat of adClipString.
NumRows
Optional. The number of rows to be converted in the Recordset. If NumRows is not specified, or if it is greater than the total number of rows in the Recordset, then all the rows in the Recordset are converted.
ColumnDelimiter
Optional. A delimiter used between columns, if specified, otherwise the TAB character.
RowDelimiter
Optional. A delimiter used between rows, if specified, otherwise the CARRIAGE RETURN character.
NullExpr
Optional. An expression used in place of a null value, if specified, otherwise the empty string.
I suspect I need a parameter other than carriage return for the row delimiter. Can any one suggest some settings here?
Function CreateTableFromRecordset( _
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 table
With rngAny
' Creating the basic table is easy,
' just insert the tab-delimted text
' add convert it to a table
.InsertAfter varData
Set objTable = .ConvertToTable()
' Field names are more work since
' you must do them one at a time
If fIncludeFieldNames Then
With objTable
' Add a new row on top and make it a heading
.Rows.Add(.Rows(1)).Headin gFormat = True
' Iterate through the fields and add their
' names to the heading row
For Each fldAny In rstAny.Fields
cField = cField + 1
.Cell(1, cField).Range.Text = fldAny.Name
Next
End With
End If
End With
Set CreateTableFromRecordset = objTable
End Function
Kelvin
I looking for some quick help here. The following code is from a reference by Liwin, Getz & Gunderloy.
It takes data from an ADO recordset and populates a table. In my case I have a recordset made up from data in two Memo fields. The Enter key has been used to create linebreaks and blank lines within the data. This is creating a problem with the .ConvertToTable Method. The getstring method that has optional parameters as follows
StringFormat
A StringFormatEnum value that specifies how the Recordset should be converted to a string. The RowDelimiter, ColumnDelimiter, and NullExpr parameters are used only with a StringFormat of adClipString.
NumRows
Optional. The number of rows to be converted in the Recordset. If NumRows is not specified, or if it is greater than the total number of rows in the Recordset, then all the rows in the Recordset are converted.
ColumnDelimiter
Optional. A delimiter used between columns, if specified, otherwise the TAB character.
RowDelimiter
Optional. A delimiter used between rows, if specified, otherwise the CARRIAGE RETURN character.
NullExpr
Optional. An expression used in place of a null value, if specified, otherwise the empty string.
I suspect I need a parameter other than carriage return for the row delimiter. Can any one suggest some settings here?
Function CreateTableFromRecordset( _
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 table
With rngAny
' Creating the basic table is easy,
' just insert the tab-delimted text
' add convert it to a table
.InsertAfter varData
Set objTable = .ConvertToTable()
' Field names are more work since
' you must do them one at a time
If fIncludeFieldNames Then
With objTable
' Add a new row on top and make it a heading
.Rows.Add(.Rows(1)).Headin
' Iterate through the fields and add their
' names to the heading row
For Each fldAny In rstAny.Fields
cField = cField + 1
.Cell(1, cField).Range.Text = fldAny.Name
Next
End With
End If
End With
Set CreateTableFromRecordset = objTable
End Function
Kelvin
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks jjaffer. That appears to do the trick.
Kelvin,
In case searchers conclude that my answer to the question that you actually asked about an alternative delimiter was incorrect, can you say whether you tried it, and if so, did it work?
In case searchers conclude that my answer to the question that you actually asked about an alternative delimiter was incorrect, can you say whether you tried it, and if so, did it work?
ASKER
Yes, I tried your solution first, but it did resolve the problem. Pity as it would have been the simpler solution.
Regards
kelvin
Regards
kelvin
ASKER
Oooops Read di not resolve the problem
OK. I did test it, and it worked in the tests, but we do now have on record that there are some unspecified circumstances where it doesn't.
Thank you.
Thank you.
Thinking about it, there is a possibility that the substitute delimiter also appeared in the fields. That would also produce unwanted results.
However vbVerticalTab (Ascii 11) works as just as well.
varData = rstAny.GetString(, , , vbVerticalTab)
No other changes are needed