Solved

In Word automation With Late Binding, How Do I Add an Unknown Number of Tables?

Posted on 2014-04-15
16
571 Views
Last Modified: 2014-05-14
I am trying to automate the building of a directory of clubs where the officers are listed in a single CSV file along with club name, etc. As the club name changes, I want to print a table with club information including the officers. I don't know how many clubs will be in the CSV file and the number of officers listed varies from 0 to 4. I can add a new document and fill the table for each club. What I want to do is create the document and then add clubs as I go through the CSV file so the result for 30 clubs would be one Word document with 30 tables. All the tables will be the same size but some cells might be empty.
Here is my code thus far:
    Public oWord As Object
    Public oDoc As Object
    Public oRng As Object

  Public Sub PrintDirectoryRecord(DirectoryRecord As ClubRecord)

        If Not WordDocCreated Then CreateWordDocument(oWord, oDoc, oRng)
        WordPrintClub(DirectoryRecord, oWord, oDoc, oRng)
        HasPresident = False
        HasSecretary = False
        HasTreasurer = False
        HasMembership = False
    End Sub

    Private Sub FillOfficerMessage(ByVal Title As String, ByRef Officer As OfficerRecord)
'This just creates a string that is entered into a cell. The code works and is deleted here.
    End Sub

    Public Sub CreateWordDocument(ByRef oWord As Object, oDoc As Object, oRng As Object)
'Note: I have tried to use oWord, oDoc and oRng as public objects in hopes of being able
'to create the document and then reuse them as tables are added -- doesn't work as the
'oRng is considered a Null variable in WordPrintClub
        oWord = CreateObject("Word.Application")
        oWord.Visible = True
        oDoc = oWord.Documents.Add
        With oDoc.PageSetup
            .LineNumbering.Active = False
            .Orientation = WordConstants.wdOrientPortrait
            .TopMargin = oWord.InchesToPoints(0.5)
            .BottomMargin = oWord.InchesToPoints(0.5)
            .LeftMargin = oWord.InchesToPoints(0.38)
            .RightMargin = oWord.InchesToPoints(0.38)
            .Gutter = oWord.InchesToPoints(0)
            .HeaderDistance = oWord.InchesToPoints(0.5)
            .FooterDistance = oWord.InchesToPoints(0.5)
            .PageWidth = oWord.InchesToPoints(4)
            .PageHeight = oWord.InchesToPoints(7)
            .FirstPageTray = WordConstants.wdPrinterDefaultBin
            .OtherPagesTray = WordConstants.wdPrinterDefaultBin
            .SectionStart = WordConstants.wdSectionNewPage
            .OddAndEvenPagesHeaderFooter = True
            .DifferentFirstPageHeaderFooter = False
            .VerticalAlignment = WordConstants.wdAlignVerticalTop
            .SuppressEndnotes = False
            .MirrorMargins = False
            .TwoPagesOnOne = False
            .BookFoldPrinting = False
            .BookFoldRevPrinting = False
            .BookFoldPrintingSheets = 1
            .GutterPos = WordConstants.wdGutterPosLeft
        End With
        oRng = oDoc.bookmarks.item("\endofdoc").range
        WordDocCreated = True
    End Sub

    Public Sub WordPrintClub(ByRef DirectoryRecord As ClubRecord, ByRef oWord As Object, ByRef oDoc As Object, ByRef oRng As Object)
        '
        Dim oTable As New Object
        Dim oCell As Object
'This is where the "Object not set" error occurs.
        oRng = oDoc.bookmarks.item("\endofdoc").range
        oTable = oDoc.Tables.Add(oRng, NumRows:=7, NumColumns:= _
            2, DefaultTableBehavior:=1, AutoFitBehavior:=WordConstants.WdAutoFitWindow)
        oTable.Range.Font.Size = 9
        'Merge top row cells
        oCell = oTable.Cell(1, 1)
        oTable.cell(1, 2).merge(oCell)
        oTable.cell(1, 1).Range.ParagraphFormat.Alignment = WordConstants.wdAlignParagraphCenter
        oTable.cell(1, 1).range.font.bold = True
        oTable.cell(1, 1).range.text = DirectoryRecord.ClubName + " (" + DirectoryRecord.ClubNbr + ") Chartered " + DirectoryRecord.CharterDate.ToShortDateString
        oTable.cell(2, 1).Range.ParagraphFormat.Alignment = WordConstants.wdAlignParagraphLeft
        oTable.cell(2, 1).range.text = "PRESIDENT"
        oTable.cell(2, 2).Range.ParagraphFormat.Alignment = WordConstants.wdAlignParagraphLeft
        oTable.cell(2, 2).range.text = "TREASURER"
        oTable.cell(4, 1).Range.ParagraphFormat.Alignment = WordConstants.wdAlignParagraphLeft
        oTable.cell(4, 1).Range.Text = "SECRETARY"
        oTable.cell(4, 2).Range.ParagraphFormat.Alignment = WordConstants.wdAlignParagraphLeft
        oTable.cell(4, 2).range.text = "MEMBERSHIP CHAIR"
        oTable.cell(3, 1).Range.ParagraphFormat.Alignment = WordConstants.wdAlignParagraphLeft
        oTable.cell(3, 2).Range.ParagraphFormat.Alignment = WordConstants.wdAlignParagraphLeft
        oTable.cell(5, 1).Range.ParagraphFormat.Alignment = WordConstants.wdAlignParagraphLeft
        oTable.cell(5, 2).Range.ParagraphFormat.Alignment = WordConstants.wdAlignParagraphLeft
        If HasPresident Then
            FillOfficerMessage("President", DirectoryRecord.President)
            oTable.cell(3, 1).range.text = gMsg
        End If
        If HasTreasurer Then
            FillOfficerMessage("Treasurer", DirectoryRecord.Treasurer)
            oTable.cell(3, 2).range.text = gMsg
        End If
        If HasSecretary Then
            FillOfficerMessage("Secretary", DirectoryRecord.Secretary)
            oTable.cell(5, 1).range.text = gMsg
        End If

        If HasMembership Then
            FillOfficerMessage("Membership", DirectoryRecord.Membership)
            oTable.cell(5, 2).range.text = gMsg
        End If
        oCell = oTable.cell(6, 1)
        oTable.cell(6, 2).merge(oCell)
        oTable.cell(6, 1).range.font.bold = True
        oTable.cell(6, 1).range.text = "MEETING LOCATION/TIME"
        oCell = oTable.cell(7, 1)
        oTable.cell(7, 2).merge(oCell)
        oTable.cell(7, 1).range.font.Italic = True
        oTable.cell(7, 1).range.text = "Meeting location and time goes here"
        oTable.cell(7, 1).range.paragraphformat.alignment = WordConstants.wdAlignParagraphCenter

    End Sub

Open in new window


I would greatly appreciate suggestions on another way to do this and/or ways to fix this approach. I am not wedded to the code.
0
Comment
Question by:rkulp
  • 7
  • 5
16 Comments
 
LVL 22

Expert Comment

by:Flyster
Comment Utility
Would you be able to provide a sample of the CSV file so we can see the format we're dealing with. Be sure to remove any sensitive information.

Flyster
0
 
LVL 1

Author Comment

by:rkulp
Comment Utility
The data is read into Excel and then imported into the following structures which are used to populate the tables:
    Public Structure OfficerRecord
        Public Name As String
        Public Spouse As String
        Public Address1 As String
        Public Address2 As String
        Public Address3 As String
        Public Address4 As String
        Public City As String
        Public State As String
        Public Zip As String
        Public HomePhone As String
        Public CellPhone As String
        Public WorkPhone As String
        Public Email As String
    End Structure
    Public Structure MeetingRecord
        Public Days As String
        Public Time As String
        Public Location As String
        Public Address1 As String
        Public Address2 As String
        Public City As String
    End Structure
    Public Structure ClubRecord
        Public ClubName As String
        Public CharterDate As Date
        Public ClubNbr As String
        Public President As OfficerRecord
        Public Secretary As OfficerRecord
        Public Treasurer As OfficerRecord
        Public Membership As OfficerRecord
        Public MeetingTimes As MeetingRecord
    End Structure
    Public DirectoryRecord As ClubRecord

Open in new window


The data in each cell in rows 3 and 5 is a multi-line string which is built from the variables above.
0
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
Since it is in excel, you can load the used range into an array:
arrData = xlsheet.UsedRange.Value

Open in new window


and then test the size of the array using:
ubound(arrayname)

Open in new window


I'd then use a For Loop to add the tables according to the size of the array (I'm assuming each row is a new table).
    For j = 2 To UBound(arrData, 2)  ' 2 for headers
              For i = 2 To UBound(arrData, 1)

Open in new window

0
 
LVL 1

Author Comment

by:rkulp
Comment Utility
DrTribus,
Thanks for hanging with me.
Each row is an officer of a club. The number of rows is approximately, but not exactly, four times the number of clubs. Since I load the entire worksheet into a datatable, I know the number of rows. If necessary I could do two reads and calculate the number of clubs. The question remains though: Why does the program break with a "Variable not set" error when I try to add the table on line 61 of the original block of code?

When I do this for a fixed number of tables, I just create the Word application and document then add tables like Table1, Table2, etc. In this situation, I tried to create the Word application and document outside the subroutine which added the tables because I didn't want a new document for each table. Does this problem have to do with not creating the application and document in the same routine? I would hate to have to save and close the document at the end of a club and then open it to add another table.
0
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
Without actually running the code, I think you need to change by adding SET:

SET oRng = oDoc.bookmarks.item("\endofdoc").range
0
 
LVL 1

Author Comment

by:rkulp
Comment Utility
DrTribos,

I have seen the use of SET in VBA but the Visual Studio 2013 IDE will not let me use it. I can type it in but it disappears when I leave the line.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
Sorry I did not see the tags - thought we were in VBA.  In that case can you try moving the end of the range and then collapsing the range..

In VBA it would be:

oRng.end = oDoc.bookmarks.item("\endofdoc").range.end
oRng.collapse wdcolloapseend

I'm learning VB.Net so please let me know if you are able to tweak it to get a satisfactory result there :)

[EDIT: added ".end" to line 1 of example code]
0
 
LVL 1

Author Comment

by:rkulp
Comment Utility
That doesn't work because the variable is not instantiated. I went to early binding just to see if the logic worked and the document was produced essentially correct aside from some formatting issues. So, I know it works. Unfortunately, the users will most likely have different versions of Word.
If I can't find a late binding solution, I think my next step is to turn to the OpenXML SDK and try to build the document that way. That can be done even if they don't have Word installed but it cannot be read without a word processor that reads the OpenXML format (Word 2007 and above and, I think Apache Open Office). In the meantime, I'll ask the administrator to bump this up.
Thanks for trying this difficult problem.
0
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
thanks for the update - I'll watch on.
0
 
LVL 1

Author Comment

by:rkulp
Comment Utility
There is apparently no interest in this question. I ended up doing the project using the great dll written by Cathel Coffey (https://docx.codeplex.com/). It works great and does not require MS Word to create the document. It does, however, require Word 2007 or later to read it. I tried to get Apache OpenOffice to read it but failed. However, if I open it in Word 2010 and make a minor edit (inserted and then removed a space) and, finally, save the document Apache OpenOffice opens it correctly.
0
 
LVL 14

Expert Comment

by:DrTribos
Comment Utility
Def interested - away at moment. Tnx for updates.  Would love a link - sorry for the brevity - using phone
0
 
LVL 1

Accepted Solution

by:
rkulp earned 0 total points
Comment Utility
Based on all threads I can find on this subject, I don't think this is possible using early binding. As mentioned above, I have abandoned this approach in favor of using DocX. One thing to note is the website says there is no documentation. However, in the downloads there is a compiled help file that lists all the classes, methods, properties, etc. of the Novacode class. That and the discussions group should be sufficient for many tasks.
0
 
LVL 1

Author Closing Comment

by:rkulp
Comment Utility
No solution was provided by anyone else. Rather than delete the question, I felt the information about DocX was important enough to save for others.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
My experience with Windows 10 over a one year period and suggestions for smooth operation
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now