• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 11387
  • Last Modified:

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

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
smithm43
Asked:
smithm43
1 Solution
 
sunnycoderCommented:
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
 
smithm43Author Commented:
If you think it is more appropriate I have no problems with you moving it there.

michael
0
 
Tommy KinardCommented:
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
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
rosesolutions1Commented:
Use bookmarks
0
 
Tommy KinardCommented:
Hi smithm43,

Do you still need help?

dragontooth

0
 
smithm43Author Commented:
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
 
Tommy KinardCommented:
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
 
Tommy KinardCommented:
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
 
smithm43Author Commented:
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
 
Tommy KinardCommented:
Always interested :)
I enjoy seeing different approaches to coding things, keeps me from getting short sighted and narrow minded :)
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now