Results 1 to 6 of 6
  1. #1
    cathyj
    Guest

    Dynamic Reports / Dynamic tables

    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

  2. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Reports / Dynamic tables

    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.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  3. #3
    cathyj
    Guest

    Re: Dynamic Reports / Dynamic tables

    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

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Dynamic Reports / Dynamic tables

    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.)

  5. #5
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Reports / Dynamic tables

    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.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Reports / Dynamic tables

    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

    <pre>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
    </pre>



    It's not pretty code, but it did the job I needed to do at the time.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •