Solved

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

Posted on 2014-04-15
16
629 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
[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
  • 7
  • 5
16 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 40002955
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
ID: 40002988
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 15

Expert Comment

by:DrTribos
ID: 40003129
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:rkulp
ID: 40003770
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 15

Expert Comment

by:DrTribos
ID: 40005565
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
ID: 40005603
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
 
LVL 15

Expert Comment

by:DrTribos
ID: 40005621
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
ID: 40005712
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 15

Expert Comment

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

Author Comment

by:rkulp
ID: 40043089
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 15

Expert Comment

by:DrTribos
ID: 40043104
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
ID: 40054980
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
ID: 40064013
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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

617 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