?
Solved

Exporting data to Word Table

Posted on 2007-11-18
8
Medium Priority
?
616 Views
Last Modified: 2013-11-27
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)).HeadingFormat = 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
0
Comment
Question by:Kelvin Sparks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20311734
You cannot specify the row delimiter in the ConvertToTable method as you have discovered.

However vbVerticalTab (Ascii 11) works as just as well.

    varData = rstAny.GetString(, , , vbVerticalTab)

No other changes are needed


0
 
LVL 27

Accepted Solution

by:
jjafferr earned 2000 total points
ID: 20312955
This is how to do it with Bookmark in Word
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22516294.html#18930694

download the zip file, which contains the mdb and the Word template.

jaffer
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 20315925
Thanks jjaffer. That appears to do the trick.
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20316223
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?
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 20316669
Yes, I tried your solution first, but it did resolve the problem. Pity as it would have been the simpler solution.

Regards


kelvin
0
 
LVL 22

Author Comment

by:Kelvin Sparks
ID: 20316672
Oooops Read di not resolve the problem
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20316700
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.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 20333637
Thinking about it, there is a possibility that the substitute delimiter also appeared in the fields. That would also produce unwanted results.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question