Write DataSet to a Microsoft Word Document with an Array using vb.net

Good Morning everyone! My weekend was not all that great on a personal level. I was pretty much down for the count with the flu all weekend. It seems when the seasons change it just hits me. But on a work level, I did well. I came up with the GUI design for the implementation of a Crystal Reports type interface allowing people to build templates for emails they send out. It may not sound like much but believe me, getting this part down was a huge step. The biggest challenge was finding a way to do everything we needed to do and yet conserve work space area in the document even for those with 800 x 600 monitors.

Anyway, this is a subject I think many developers would like to hear about. MS Word is extremely slow to automate. Their can be no dispute about this. Writing a huge amount of data to the document is a trying and perilous task. Unlike Excel so many things can go wrong.

What we want to do is write the dataset table to an array first, then write the array to a text string, place the text on the document at the required insertion point and then convert it to a table. It is not as fast as my sample doing this with Excel, but it is must faster than doing it cell by cell and is at least doable with vb.net and MS Word. This sample will work with any MS Word version except Word 97 and below and on any version of the .NET Framework.

Thanks and make it a great day!

‘Note vba is not zero based….

Dim rows As Integer = dsreport.Tables.Item(0).Rows.Count
Dim columns As Integer = dsreport.Tables.Item(0).Columns.Count

‘creating the array that will hold our data

Dim DataArray(rows, columns) As Object

‘writing the data to the array

For c = 0 To columns – 1
            DataArray(r, c) = dsreport.Tables(0).Columns.Item(c).ColumnName
            For r = 0 To rows – 1
                DataArray(r, c) = dsreport.Tables(0).Rows(r).Item(c)
            Next
        Next

‘creating a range at the Word Document insertion point

Dim oRange = oDoc.Content.Application.Selection.Range

‘creating a string to hold what is in our array
        Dim oTemp As String
        For r = 0 To rows – 1
            For c = 0 To columns – 1
                oTemp = oTemp & DataArray(r, c) & vbTab
            Next
        Next

‘writing the text to our document. It is not a table yet.
        oRange.Text = oTemp

‘convert it to a table using vba and you are done!

oRange.ConvertToTable(Separator:=wdSeparateByTabs, NumColumns:=dsreport.Tables(0).Columns.Count, _
NumRows:=dsreport.Tables(0).Rows.Count – 1, AutoFitBehavior:=wdAutoFitContent)

 

 

 

Advertisements
  1. #1 by PAULETTE on October 27, 2008 - 10:32 am

    I\’m about as far from being a techno-geek as possible–kind of like the difference  between an a main frame computer and a stone tablet–now, where did I put my chsle …?  seriouslly though Kelly, thanks for stopping by my space and for leaving such a supportive comment.  Sometimes I wonder why I write–other times I doubt I\’d be able to stop writing if I tried–it\’s the only way I can get my thoughts to slow down enough for me to grasp what it is that I\’m thinking and feeling … and why.  Take care Kelly.  May your path be one of beauty and balance.  Paulette

  1. Write DataTable to an MS Word Table Efficiently with C# using a Dynamic Type « Kelly's Chronicles

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: