0

I've got a lot of similar spreadsheets (same dimensions, different numbers) in Microsoft Excel, like this:

One of twelve spreadsheets

To present them in my report in Microsoft Word, I have to format them in this way:

Formatted table in Word

To avoid meaningless repetitions, I've recorded a macro. However, when I try to execute the macro an error occured: Error "5991". It says that it's impossible to access some individual lines because of some vertical fusion.

Since I know almost nothing about debugging in VBA, I cannot debug it myself in a short period. The recorded macro is:

Sub Macro1()
'
' Macro1 Macro
'
'
    Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderVertical).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
    With Selection.Borders(wdBorderBottom)
        .LineStyle = Options.DefaultBorderLineStyle
        .LineWidth = Options.DefaultBorderLineWidth
        .Color = Options.DefaultBorderColor
    End With
    With Selection.Borders(wdBorderTop)
        .LineStyle = Options.DefaultBorderLineStyle
        .LineWidth = Options.DefaultBorderLineWidth
        .Color = Options.DefaultBorderColor
    End With
    With Selection.Borders(wdBorderBottom)
        .LineStyle = Options.DefaultBorderLineStyle
        .LineWidth = Options.DefaultBorderLineWidth
        .Color = Options.DefaultBorderColor
    End With
    Selection.Borders(wdBorderTop).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderRight).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderVertical).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
    Selection.Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
    With Selection.Borders(wdBorderBottom)
        .LineStyle = Options.DefaultBorderLineStyle
        .LineWidth = Options.DefaultBorderLineWidth
        .Color = Options.DefaultBorderColor
    End With
    With Selection.Borders(wdBorderBottom)
        .LineStyle = Options.DefaultBorderLineStyle
        .LineWidth = Options.DefaultBorderLineWidth
        .Color = Options.DefaultBorderColor
    End With
    With Selection.Borders(wdBorderBottom)
        .LineStyle = Options.DefaultBorderLineStyle
        .LineWidth = Options.DefaultBorderLineWidth
        .Color = Options.DefaultBorderColor
    End With
    Windows("Document1").Activate
    Windows("Document2").Activate
    With Selection.Borders(wdBorderBottom)
        .LineStyle = Options.DefaultBorderLineStyle
        .LineWidth = Options.DefaultBorderLineWidth
        .Color = Options.DefaultBorderColor
    End With
    Selection.Font.Name = "Times New Roman"
    Selection.Font.Size = 10
    Selection.EscapeKey
End Sub
Sub MiseEnPage()
'
' MiseEnPage Macro
' FormatATableInWord
' 

'
    Selection.Tables(1).Style = "Tableau simple 4"
    With Selection.Tables(1)
        .TopPadding = CentimetersToPoints(0)
        .BottomPadding = CentimetersToPoints(0)
        .LeftPadding = CentimetersToPoints(0.05)
        .RightPadding = CentimetersToPoints(0.05)
        .Spacing = 0
        .AllowPageBreaks = True
        .AllowAutoFit = True
    End With
    Selection.Tables(1).AutoFitBehavior (wdAutoFitContent)
    Selection.Tables(1).AutoFitBehavior (wdAutoFitContent)
    Selection.EscapeKey
    Selection.Delete Unit:=wdCharacter, Count:=1
    Selection.Rows.Delete
    Selection.Tables(1).Select
    Selection.Tables(1).Delete
End Sub
Sub TableFormatting()
'
' TableFormatting Macro
'
'
    Selection.Tables(1).AutoFitBehavior (wdAutoFitContent)
    Selection.Tables(1).AutoFitBehavior (wdAutoFitContent)
    Selection.Tables(1).Style = "Tableau simple 4"
    Selection.Font.Size = 10
    Selection.Font.Name = "Times New Roman"
    Selection.Font.Size = 12
    Selection.Font.Size = 12
    With Selection.Borders(wdBorderBottom)
        .LineStyle = Options.DefaultBorderLineStyle
        .LineWidth = Options.DefaultBorderLineWidth
        .Color = Options.DefaultBorderColor
    End With
    With Selection.Borders(wdBorderTop)
        .LineStyle = Options.DefaultBorderLineStyle
        .LineWidth = Options.DefaultBorderLineWidth
        .Color = Options.DefaultBorderColor
    End With
    With Selection.Borders(wdBorderBottom)
        .LineStyle = Options.DefaultBorderLineStyle
        .LineWidth = Options.DefaultBorderLineWidth
        .Color = Options.DefaultBorderColor
    End With
    With Selection.Borders(wdBorderBottom)
        .LineStyle = Options.DefaultBorderLineStyle
        .LineWidth = Options.DefaultBorderLineWidth
        .Color = Options.DefaultBorderColor
    End With
    With Selection.Borders(wdBorderBottom)
        .LineStyle = Options.DefaultBorderLineStyle
        .LineWidth = Options.DefaultBorderLineWidth
        .Color = Options.DefaultBorderColor
    End With
    Selection.EscapeKey
    Selection.Tables(1).Rows(2).SetHeight RowHeight:=20.55, HeightRule:= _
        wdRowHeightAtLeast
    With Selection.Borders(wdBorderBottom)
        .LineStyle = Options.DefaultBorderLineStyle
        .LineWidth = Options.DefaultBorderLineWidth
        .Color = Options.DefaultBorderColor
    End With
End Sub

Is there any way to format the tables in batch?

4
  • No there is no way to edit Word files in batch. Please share your macro and also describe exactly the error so we may help. " I can not debug it myself in a short period" - if time is really matters for you then you'd better hire a professional as here volunteers answer to questions which they like when they have time. Commented Jul 3, 2016 at 12:39
  • 1
    Thank you for helping. I've reedited the question. Since I am a new come in SuperUser, I can only paste 2 links here. I am obliged to paste directely the code and error in plein text.
    – Tough Kid
    Commented Jul 3, 2016 at 13:14
  • 1
    Unfortunately word VBA can't always manage tables correctly. Try to format your table already in excel and copy it formatted. Commented Jul 3, 2016 at 15:09
  • 1
    Thank you for this answer whiche make me realize that I can also format a table in Excel :-).
    – Tough Kid
    Commented Jul 3, 2016 at 17:30

0

You must log in to answer this question.