none
Repeating Macro to Format Tables (help Needed) RRS feed

  • Question

  • Hello All

    I have been trying to get a repeating macro to format the Header in my roster table for each week and also put monthly book marks at the beginning of each month.  I have managed to get some of it done manually but using macros I failed miserably.  So I am requesting some help

    The roster is spread over 4 years it was generated from an excel spreadsheet and inserted into a word table in landscape mode

    By manipulating the top and Bottom margins I can get each week on one page. Then I need a way to be able to format the colors in the heading as the same as the first page  (as below) the left column and the bands I can do with table formats in word.

    My second problem is getting a monthly index to provide bookmarks, I have been Manually putting in bookmarks by copying the Jun_2015 in the first column  as of  the first of the month and inserting it as bookmark

    the End product is saved as  a PDF roster file, formatted in Arial 26 Bold with a monthly index.  I then put the file into an iphone so me and my co workers can have access to the roster at our finger tips so we can tell who is on off etc.

    Many Thanks in appreciation for any help

    June_2015

    Tue

    26 May 2015

    Wed

    27 May 2015

    Thu

    28 May 2015

    Fri

    29 May 2015

    Sat

    30 May 2015

    Sun

    31 May 2015

    Mon

    1 June 2015

     

     

     

     

     

     

     

     

    Bill  Mills

     

     

     

     

     

     

     

    Mike Biker

     

     

     

     

     

     

     

    Fred Furlong

     

     

     

     

     

     

     

    John Jack

     

     

     

     

     

     

     

     

     


    Sunday, May 24, 2015 12:01 AM

Answers

  • Hi Dackadee,

    Not 100% sure exactly that you are achieving this in the most efficient way, but I will try to help you from the point you have got to.

    I have a simple word macro I run sometimes to ensure my users have not ruined the tables in our document by dragging and dropped them outside the margins accidentally, I have added a bit to that code after it has fitted the table to add a bookmark based on the contents of cell (1,1) and set the background colour of row 1 and column 1.

    Try this and let me know how it goes for you:

    Sub fixTables()
    
    
    Dim t As Table, c As Column, tWidth, pWidth, pChange
    Dim tCell As cell
    
    'Make the tables fit
    For Each t In ActiveDocument.Tables
    
        tWidth = 0
        
        On Error Resume Next
        
        'get the actual width of the table
        For Each c In t.Columns
            tWidth = tWidth + c.Width
        Next c
        
        'if no errors happened (table has columns)
        If Err.Number = 0 Then
        
            'work out the actual width of the page the table is sitting on
            pWidth = (t.Range.PageSetup.PageWidth - (t.Range.PageSetup.LeftMargin + t.Range.PageSetup.RightMargin))
            
            'if the table is wider than the page
            If tWidth > pWidth Then
                'work out the percentage change required
                pChange = (pWidth / tWidth)
                For Each c In t.Columns
                    c.Width = (c.Width * pChange)
                Next c
            End If
            
            'set the table to sit exactly on the left margin
            t.Rows.LeftIndent = 0
            t.LeftPadding = 0
            
        End If
            
        On Error GoTo 0
        
        'add the bookmark based on the value in the first cell of the first row of the table (e.g. Jun_2015)
        Dim bookmarkName As String: bookmarkName = t.Rows(1).Cells(1).Range.Text
        ActiveDocument.Bookmarks.Add bookmarkName, t.Range
        
        'set the colour of the first row
    
        With t.Rows(1).Shading
            .Texture = wdTextureNone
            .BackgroundPatternColor = wdColorBlue
            .ForegroundPatternColorIndex = wdAuto
        End With
    
        'and the first column
        With t.Columns(1).Shading
            .Texture = wdTextureNone
            .BackgroundPatternColor = wdColorBlue
            .ForegroundPatternColorIndex = wdAuto
        End With
        
    Next t
    
    
    End Sub

    Monday, May 25, 2015 3:11 PM

All replies

  • Hi Dackadee

    Mmmm. This is a bit difficult without the actual document as it's not 100% clear exactly what you have.

    Things would certainly be easier if you'd SPLIT each page into separate tables, rather than adjusting the margins. Then you could 1) set the formatting for the Header row in the table format and 2) loop the tables individually for other tasks...

    I'm not clear on the monthly index part. Can you go into more detail how you're copying and "inserting as a bookmark"? And how should a macro identify when a new month starts - what's the criterium?


    Cindy Meister, VSTO/Word MVP, my blog

    Sunday, May 24, 2015 6:16 PM
    Moderator
  • Good morning Cindy

    Thank you for taking an interest in my problem

    I will give you an example of my workflow so that you can replicate what steps I used to create this table

    What I would like to do would be to be able to do all the processing of bookmarks and format headers in the Table with a macro if that is at all possible?

    I will await with interest to see what you think, or perhaps suggest  another way to process this type of file

    Thanking you again in appreciation

    Dackadee  (at 69 so much to learn so little time J)

    FILE PRE-PROCESSING

    The selected data was extracted from an EXCEL sheet that was saved as a csv file.  I then used a Qbasic program ( no Laughing!) to run through all the rows of the csv file. 

    1  The purpose of this was to insert chr$(32) after each line of text to make the output on the table a bit more easy to read when the  table is finally saves to a pdf file to be read on an iPhone

    2  the program also searched for the first day of the month and then put the month & year JUN_2015 in the 1 column of the heading.  Also after the Day Text a * was added to the text so that I could in the Table use find & replace to replace the * with a manual Line break after  <Tue> with <19-May-15> underneath

    3 The resulting CSV file sample as below

    IN WORD PROCESSING

    4  In word  I then use the Insert >Object> Text From File,   to insert text into word, 

    Then convert text to Table. /  In the Table use Search * & Replace with  ^l Manual line break /  Adjust top and bottom margins so that only one week at a time on each page in landscape.  The apply table styles

    Inserting bookmarks go through  and find the beginning of the Month text ie Jun_2015 Select Text>Copy>Ctrl & Shift>opens insert bookmark box >Paste text into bookmark ( Page after Page   ZZzzzzz)

    The table is then saved as a PDF file using word bookmarks to create the monthly index.  The file is then sent to the various crews to put on their iPhones.  In the iPhone the file is read using iBooks this enables us to scan ahead to future dates using the monthly Index to find dates of time on & off etc.

    ,Tue*19-May-15,Wed*20-May-15,Thu*21-May-15,Fri*22-May-15,Sat*23-May-15,Sun*24-May-15,Mon*25-May-15

     

    CREW 1,,,D,D,D,N,N

     

    CREW 2,L,L,L,L,L,L,L

     

    CREW 3,,,,,,D,D

     

    CREW 4,N,N,,,,,

     

    CREW 5,D,D,N,N,N,,

     

    Jun_2015,Tue*26-May-15,Wed*27-May-15,Thu*28-May-15,Fri*29-May-15,Sat*30-May-15,Sun*31-May-15,Mon*1-Jun-15

     

    CREW 1,N,,,,,,

     

    CREW 2,L,L,L,L,L,L,L

     

    CREW 3,D,N,N,N,,,

     

    CREW 4,,D,D,D,N,N,N

     

    CREW 5,,,,,D,D,D

     

    ,Tue*2-Jun-15,Wed*3-Jun-15,Thu*4-Jun-15,Fri*5-Jun-15,Sat*6-Jun-15,Sun*7-Jun-15,Mon*8-Jun-15

     

    CREW 1,L,L,L,L,L,L,L

     

    CREW 2,D,D,D,N,N,N,

     

    CREW 3,,,,D,D,D,N

     

    CREW 4,,,,,,,D

     

    CREW 5,N,N,N,,,,

     


    Monday, May 25, 2015 1:51 AM
  • Hi Dackadee,

    Not 100% sure exactly that you are achieving this in the most efficient way, but I will try to help you from the point you have got to.

    I have a simple word macro I run sometimes to ensure my users have not ruined the tables in our document by dragging and dropped them outside the margins accidentally, I have added a bit to that code after it has fitted the table to add a bookmark based on the contents of cell (1,1) and set the background colour of row 1 and column 1.

    Try this and let me know how it goes for you:

    Sub fixTables()
    
    
    Dim t As Table, c As Column, tWidth, pWidth, pChange
    Dim tCell As cell
    
    'Make the tables fit
    For Each t In ActiveDocument.Tables
    
        tWidth = 0
        
        On Error Resume Next
        
        'get the actual width of the table
        For Each c In t.Columns
            tWidth = tWidth + c.Width
        Next c
        
        'if no errors happened (table has columns)
        If Err.Number = 0 Then
        
            'work out the actual width of the page the table is sitting on
            pWidth = (t.Range.PageSetup.PageWidth - (t.Range.PageSetup.LeftMargin + t.Range.PageSetup.RightMargin))
            
            'if the table is wider than the page
            If tWidth > pWidth Then
                'work out the percentage change required
                pChange = (pWidth / tWidth)
                For Each c In t.Columns
                    c.Width = (c.Width * pChange)
                Next c
            End If
            
            'set the table to sit exactly on the left margin
            t.Rows.LeftIndent = 0
            t.LeftPadding = 0
            
        End If
            
        On Error GoTo 0
        
        'add the bookmark based on the value in the first cell of the first row of the table (e.g. Jun_2015)
        Dim bookmarkName As String: bookmarkName = t.Rows(1).Cells(1).Range.Text
        ActiveDocument.Bookmarks.Add bookmarkName, t.Range
        
        'set the colour of the first row
    
        With t.Rows(1).Shading
            .Texture = wdTextureNone
            .BackgroundPatternColor = wdColorBlue
            .ForegroundPatternColorIndex = wdAuto
        End With
    
        'and the first column
        With t.Columns(1).Shading
            .Texture = wdTextureNone
            .BackgroundPatternColor = wdColorBlue
            .ForegroundPatternColorIndex = wdAuto
        End With
        
    Next t
    
    
    End Sub

    Monday, May 25, 2015 3:11 PM