Solved

VBA - Word Macro to Create Multiple Tables of the Same Format

Posted on 2004-04-22
11
11,362 Views
Last Modified: 2007-12-19
We have a system that exports text into a .ini file from an Oracle Database.  This is then uploaded into a Word 97 Template via a macro.  At the moment there is a single table structure in a Word Template that is populated from the .ini file but we want to change the system so that it can read any number of TBL_BUDGET records and create a separate table for each from scratch.  A copy of the proposed new .ini file is below.

[HEADER]
CHAIR_TITLE=Dr*}
CHAIR_FNAME=Charlie*}
CHAIR_LNAME=Brown*}
EMAIL_ADDRESS=*}
FUNDING_DOLLAR_AMOUNT=$181,500.00*}
nongst_funding_$_AMOUNT=$165,000.00*}
gst_funding_$_AMOUNT=$16,500.00*}
FINANCIAL_STATEMENT_FREQ=Quarterly*}
PERFORMANCE_REPORT_FREQ=Yearly*}
ACQUITTAL_DATE=30 September 2004*}
NO_TABLES=2*}
ACQUITTAL_DATE=30 September 2004*}

[TBL_BUDGET1]
ROW1=2003/04*}Payroll Tax*}    16,500.00#)
ROW2=2003/04*}Leave*}    20,000.00#)
ROW3=2003/04*}Salaries*}   145,000.00#)
ROW4= *}Total*}     181,500.00#)
COUNT=4

[TBL_BUDGET2]
ROW1=2003/04*}Health*}    2,000.00#)
ROW2=2003/04*}Education*}   5,000.00#)
ROW3= *}Total*}     8,500.00#)
COUNT=3

The .ini file would be created from within ORACLE and would generate as many of the TBL_BUDGET blocks as required for the letter.  The Word Template currently has fields defined that match with the names identified in the .ini file and are populated automatically by the macro.  However it is only populating a single existing table (ie there is only one TBL_BUDGET block) which has had the table structure already created on the Template.

My question is how would I set this up so that I could create any number of the TBL_BUDGET blocks (ie named TBL_BUDGET1, TBL_BUDGET2 etc) and by the use of a control variable on the file (for example NO_TABLES) create a series of information blocks incorporating the table data outlined in the ROW1 - ROW4 lines of the above .ini file.  The tables would all be the same format, other than number of rows, but would need to be created totally from scratch.  I would also need to be able to define where in the template these are created.
0
Comment
Question by:smithm43
11 Comments
 
LVL 45

Expert Comment

by:sunnycoder
ID: 10896423
Hi smithm43,

You can get better response for this question in the word TA.
http://www.experts-exchange.com/Applications/MS_Office/Word/

Would you like me to move this question to the MS Word Topic Area ?

sunnycoder
Page Editor
0
 

Author Comment

by:smithm43
ID: 10896453
If you think it is more appropriate I have no problems with you moving it there.

michael
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 10903073
Hi smithm43,
To create a table:
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=10, NumColumns _
        :=3, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitFixed

To resize it, change formatting, fonts, font size:
With .Tables(1)
            .Rows.Height = InchesToPoints(0.18)
            .Range.Font.Size = 8
            .Columns(1).Width = InchesToPoints(0.19)
            .Columns(2).Width = InchesToPoints(3.75)
            .Columns(3).Width = InchesToPoints(3.75)
        End With

To move down :
Set Range3 = ActiveDocument.Range(Start:=Selection.Tables(1).Range.End, End:=Selection.Tables(1).Range.End)
    Range3.MoveEnd Unit:=wdCharacter, Count:=1
    Range3.SetRange Start:=Range3.Start + 2, End:=Range3.End
    Range3.Select
    With Selection
        .Collapse Direction:=wdCollapseEnd
        .TypeParagraph
    End With

Everything you ask is doable, the actual location would be the biggest problem that I see. I will look at this over the weekend and get back on Monday.
dragontooth

0
 
LVL 7

Expert Comment

by:rosesolutions1
ID: 10905014
Use bookmarks
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 10928438
Hi smithm43,

Do you still need help?

dragontooth

0
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!

 

Author Comment

by:smithm43
ID: 10934227
I am still alittle bit unsure of how you build the looping structure to control the interactive creation of n tables?  I am also unfamiliar with the bookmark concept.
0
 
LVL 14

Accepted Solution

by:
Tommy Kinard earned 500 total points
ID: 10939627
smithm43 these macros will read the ini file (be sure to put the actual name and path) read the file for the table formation creates the table with 3 columns the rows will be determined by the information in the file, fills the cells  with information moves down 1 line and creates the next table if it should otherwise it exits. I have not addressed the information before the tables. This works on a blank document. Let me know if you require more help.

 SO Here ya go :)


Sub OpenIni(FilId As Integer)
    'Open .ini file and read
    Dim MyInfo As String
    FilId = FreeFile
    Open "C:\WINDOWS\Desktop\New.ini" For Input As #FilId  '< you will need to change this path to what you require
    While Not EOF(FilId)
        While InStr(1, MyInfo, "[TBL_BUDGET") = 0
            Line Input #FilId, MyInfo '< I have not addressed what is first in the ini file
        Wend
        Call CreateTable(FilId)
        Call MoveDown  '<-- move down for the next table
        If Not EOF(FilId) Then Line Input #FilId, MyInfo
    Wend
    Close (FilId)
End Sub
Sub CreateTable(FilId As Integer)
    Dim RowInfo(1 To 8) As String
    Dim RowData As Variant
    Dim I As Integer
    Dim J As Integer
    Dim WhichTable As Integer
    I = 1
    While InStr(1, RowInfo(I), "COUNT") <> 1
        Line Input #FilId, RowInfo(I)
        If InStr(1, RowInfo(I), "COUNT") <> 1 Then
            RowInfo(I) = BreakEq(RowInfo(I))
            I = I + 1
        End If
    Wend
    I = I - 1
    ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=I, NumColumns _
        :=3, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
        wdAutoFitFixed
    WhichTable = ActiveDocument.Tables.Count
    For J = 1 To I         '<-- fills the table
        RowData = Split(RowInfo(J), "}")
        With ActiveDocument.Tables(WhichTable)
            .Cell(Row:=J, Column:=1).Range.InsertAfter Text:=RowData(0)
            .Cell(Row:=J, Column:=2).Range.InsertAfter Text:=RowData(1)
            .Cell(Row:=J, Column:=3).Range.InsertAfter Text:=RowData(2)
        End With
    Next
End Sub


Function BreakEq(Info As String) As String
    Dim Anum As Long
    Dim DisLen As Long
    Anum = InStr(1, Info, "=")
    DisLen = Len(Info)
    If Anum = DisLen Then
        BreakEq = vbNullString
    Else
        BreakEq = Right$(Info, DisLen - Anum)
    End If
End Function
Sub Main()
    Dim FilId As Integer
    Call OpenIni(FilId)
End Sub
Sub MoveDown()
    Dim Range3 As Range
    Dim I As Integer
    I = ActiveDocument.Tables.Count
    Set Range3 = ActiveDocument.Range(Start:=ActiveDocument.Tables(I).Range.End, End:=ActiveDocument.Tables(I).Range.End)
    Range3.MoveEnd Unit:=wdCharacter, Count:=1
    Range3.SetRange Start:=Range3.Start + 2, End:=Range3.End
    Range3.Select
    With Selection
        .Collapse Direction:=wdCollapseEnd
        .TypeParagraph
    End With
End Sub


dragontooth


0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 10949119
smithm43 Thanks for the points and the grade! :)

Is it working as expected? I actually thought that there would be some tweeking to do.

dragontooth

0
 

Author Comment

by:smithm43
ID: 10954632
You're welcome. I haven't had a real go at it yet but am happy with the way you have given me a lead on how to approach this.  Starting is always the scariest bit and I haven't played with VB (or VBA) for a while.  It is part of a bigger project but I should have had a chance to get into it by mid May.  Should have it finished by early June so if you are interested I can send you the final code.
0
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 10959186
Always interested :)
I enjoy seeing different approaches to coding things, keeps me from getting short sighted and narrow minded :)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It is often necessary in this forum and others to illustrate Word fields as text with the field delimiters replaced with the curly brackets that the delimiters resemble when field codes are being displayed on the document. This means that the text c…
I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

705 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

20 Experts available now in Live!

Get 1:1 Help Now