smithm43
asked on
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=$1 65,000.00* }
gst_funding_$_AMOUNT=$16,5 00.00*}
FINANCIAL_STATEMENT_FREQ=Q uarterly*}
PERFORMANCE_REPORT_FREQ=Ye arly*}
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.
[HEADER]
CHAIR_TITLE=Dr*}
CHAIR_FNAME=Charlie*}
CHAIR_LNAME=Brown*}
EMAIL_ADDRESS=*}
FUNDING_DOLLAR_AMOUNT=$181
nongst_funding_$_AMOUNT=$1
gst_funding_$_AMOUNT=$16,5
FINANCIAL_STATEMENT_FREQ=Q
PERFORMANCE_REPORT_FREQ=Ye
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.
ASKER
If you think it is more appropriate I have no problems with you moving it there.
michael
michael
Hi smithm43,
To create a table:
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=10, NumColumns _
:=3, DefaultTableBehavior:=wdWo rd9TableBe havior, 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 :=Selectio n.Tables(1 ).Range.En d, End:=Selection.Tables(1).R ange.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
To create a table:
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=10, NumColumns _
:=3, DefaultTableBehavior:=wdWo
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
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
Use bookmarks
Hi smithm43,
Do you still need help?
dragontooth
Do you still need help?
dragontooth
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Is it working as expected? I actually thought that there would be some tweeking to do.
dragontooth
ASKER
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.
Always interested :)
I enjoy seeing different approaches to coding things, keeps me from getting short sighted and narrow minded :)
I enjoy seeing different approaches to coding things, keeps me from getting short sighted and narrow minded :)
You can get better response for this question in the word TA.
https://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