Dynamic Reports / Dynamic tables

Topic

New Reply

Sorry, my problem explanation in lengthy, but I need to explain exactly what I am doing to try to field out the obvious (but well meaning) solutions

Reply | Quote

Replies

Cathy,

A couple of thoughts.

You might try putting the data in, and then createing the table after all the data has been inserted.

You might be able to create the tables in Excel, then paste them into Word. This has the advantage that you can select all cells to be formatted in one go- eg:
Range(“A1,B2,C3,D4,D2”).Interior.ColorIndex = 35

Do you have a predominate format- eg, 90% of cells with one type of formatting, then 10% to be changed separately- so that you could format a table in one go?

Is it possible for you to create the entire report purely in Excel?

Just a few random thoughts.

Reply | Quote

Hi Geoff

Thanks for your reply. Excel does not have the features required to publish a good looking report document. I specifically require Word for styles, Word wrapping, table of contents, headers/footers, sections, etc – not that I am anti-Excel – I am already using Excel to create the chart components of my system (working very well).

Could you please expand on your first idea – what do you mean by putting the data in and then creating the table?

Any other suggestions are welcome!!

Cathy

Reply | Quote

Different Jeff here.

If you make Word visible and watch can you see where it is bogging down?

It might help to create a few tables with the desired number of columns and header row formats and save them as AutoText. You then could check the data for the number of columns, insert the table and Selection.TypeText data in without formatting each cell, .MoveRight wdCell to proceed to the next cell/row, and .MoveDown wdLine to exit the table. You’ll probably need to experiment with the border settings to get that to work just right. (If you are using ranges, I’m not sure what the equivalent methods are.)

Reply | Quote

Cathy,

Here’s some code which I built ages ago. It uses the Selection a lot, which I no longer really like- but we’ve used this so seldom, it’s not useful for me to change it!

Just before this code, I’ve built up text with tab separators

Selection.WholeStory
Selection.ConvertToTable Separator:=wdSeparateByTabs, _
  NumColumns:=4, NumRows:=25, Format:=wdTableFormatNone, _
  ApplyBorders:=True, ApplyShading:=True, ApplyFont:=True, _
  ApplyColor:=True, ApplyHeadingRows:=True, _
  ApplyLastRow:=False, ApplyFirstColumn:=True, _
  ApplyLastColumn:=False, AutoFit:=False
Selection.Tables(1).Rows.SetLeftIndent LeftIndent:=12, _
  RulerStyle:=wdAdjustNone
Selection.Tables(1).Columns(1).SetWidth ColumnWidth:=75, _
  RulerStyle:=wdAdjustNone
Selection.Tables(1).Columns(2).SetWidth ColumnWidth:=320, _
  RulerStyle:=wdAdjustNone
Selection.Tables(1).Columns(3).SetWidth ColumnWidth:=55, _
  RulerStyle:=wdAdjustNone
Selection.Tables(1).Columns(4).SetWidth ColumnWidth:=65, _
  RulerStyle:=wdAdjustNone
Selection.Font.Name = "Arial"
Selection.Font.Size = 8
With Selection.ParagraphFormat
  .SpaceBefore = 0
  .SpaceAfter = 0
End With
With Selection.Tables(1)
  With .Borders(wdBorderLeft)
    .LineStyle = wdLineStyleSingle
    .LineWidth = wdLineWidth150pt
    .ColorIndex = wdAuto
  End With
  With .Borders(wdBorderRight)
    .LineStyle = wdLineStyleSingle
    .LineWidth = wdLineWidth150pt
    .ColorIndex = wdAuto
  End With
  With .Borders(wdBorderTop)
    .LineStyle = wdLineStyleSingle
    .LineWidth = wdLineWidth150pt
    .ColorIndex = wdAuto
  End With
  With .Borders(wdBorderBottom)
    .LineStyle = wdLineStyleSingle
    .LineWidth = wdLineWidth150pt
    .ColorIndex = wdAuto
  End With
  With .Borders(wdBorderHorizontal)
    .LineStyle = wdLineStyleSingle
    .LineWidth = wdLineWidth075pt
    .ColorIndex = wdAuto
  End With
  With .Borders(wdBorderVertical)
    .LineStyle = wdLineStyleSingle
    .LineWidth = wdLineWidth075pt
    .ColorIndex = wdAuto
  End With
  .Borders.Shadow = False
End With
Selection.Sort ExcludeHeader:=False, FieldNumber:="Column 1", _
  SortFieldType:=wdSortFieldAlphanumeric, _
  SortOrder:=wdSortOrderAscending, FieldNumber2:="Column 2", _
  SortFieldType2:=wdSortFieldAlphanumeric, _
  SortOrder2:=wdSortOrderAscending, FieldNumber3:="", _
  SortFieldType3:=wdSortFieldAlphanumeric, _
  SortOrder3:=wdSortOrderAscending
Selection.ParagraphFormat.Alignment = wdAlignParagraphLeft

It’s not pretty code, but it did the job I needed to do at the time.

Reply | Quote

Don’t know if you ever solved this or not, but one thought I had was to recommend creating the tables in Access and then using the Transfer action to place them in Word. I believe if you run an Append query that writes all the table values to an Access table, you can pre-set the Field properties and they should be retained in Word. That way all you would have to do is the visual formatting in Word.

If you found another faster way, I would love to see it.

Thanks,

Rich P.

Reply | Quote