Solved

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

Posted on 2004-04-22
11
11,375 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
[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
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Copy cell to another cell in another table 5 78
Need to put a web footnote into a text book 7 76
vba - Word save error 11 52
How to run a VBA Macro on Mac Word 2011? 2 141
If you work with Word a lot, you probably use styles. If you use styles a lot, you've probably balled your fist more often than not when working with the ribbon. In Word 2007/2010, one of the things that I find missing when using styles is a quic…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

734 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