Edit Word Table columns width from Excel vba macro

[AlexDarsigny]

Hi everyone,

I spent hours trying to find a way to edit each column width of a word table from an excel macro and the best I could find is a way to edit every column to the same width, which is not enough. The following code does :
- Open an existing Word file
- Open an existing Excel file
- Copy a range in the Excel file
- Paste the selection into Word file
- Edit the new table created in the Word file

It's the editing part that's causing problem. Here's a video of what I want from this macro in the table editing part : http://s000.tinyupload.com/?file_id=37631956073752272221 (open it wih any web browser)

I have no problem to set all my rows height to 0.5 cm because I want the same height for every row. I used Rows.SetHeight function (as you can see in my code). But when it comes to setting every column individually, I really don't know what to do. I found some pieces of code that seems to only work in a Word VBA macro like

ActiveDocument.Table.Columns(1).Width = 30

Thank you very much for your help!

Sub test()
    folder_Modules_xls = "C:\Modules"
    path_template_report = "C:\template_report.docx"
    'Get all the selected modules and deduct Bookmarks by removing extension (*.xls or *.xlsx)
    SelectedModules = "test.xlsx"
    
    'OPEN main Word File
    Set objWord = CreateObject("Word.Application")
    Set objDoc = objWord.Documents.Open(path_template_report)
    objWord.Visible = True
            
    'Extract Bookmark that will be used in word file.
    Bookmarks = Left(SelectedModules, WorksheetFunction.Search(".", SelectedModules) - 1)
            
    'OPEN Excel file to print into the main Word file
    Set wb = Workbooks.Open(folder_Modules_xls & "\" & SelectedModules)
    'READ the number of range name in the xls file corresponding to the number of print area
    printAreaQty = ActiveWorkbook.Names.Count
    'rRange = RecoverNameRange("Print_Area1")


    'In the Word file, go to the corresponding bookmark
    objDoc.Bookmarks(Bookmarks).Select


    'Skip a line
    objWord.Selection.TypeText (vbCrLf) 'return carriage
    
    'Select the print area #1 in the xls file
    Application.Range("Print_Area1").Copy
    'Paste the table at the selected bookmark in the main word doc.
    objWord.Selection.Paste
    Application.CutCopyMode = False
    
    'CUSTOMIZE THE TABLE (the newest)
    With objDoc.Tables(objDoc.Tables.Count)
        .AutoFitBehavior wdAutoFitWindow 'fit to the page
        .Rows.SetHeight RowHeight:=objWord.CentimetersToPoints(0.5), HeightRule:=wdRowHeightExactly 'adjust row height
    End With


    'Close Excel file
    Workbooks(SelectedModules).Close SaveChanges:=False
    Set wb = Nothing
    
    'CLOSING SETTING
    Set objWord = Nothing
    Set objDoc = Nothing
End Sub

 

[Norie]

Can you not use SetWidth for the columns?

Something like this perhaps.

With objDoc.Tables(objDoc.Tables.Count)
    .AutoFitBehavior wdAutoFitWindow 'fit to the page
    .Rows.SetHeight RowHeight:=objWord.CentimetersToPoints(0.5), HeightRule:=wdRowHeightExactly 'adjust row height
    .Columns.SetWidth ColumnWidth:=objWord.CentimetersToPoints(0.5), RulerStyle:=wdAdjustSameWidth
End With

 

[AlexDarsigny]

Thank you for your quick response. SetWidth is the first thing I've tried. There's no error but it sets the same width for every column and I want to set different width for each column so I can't use this function. I also tried :

.Columns[U][B](1)[/B][/U].SetWidth ColumnWidth:=objWord.CentimetersToPoints(0.5), RulerStyle:=wdAdjustSameWidth

, but it's not supported.

 

[Norie]

What do you want to set the column widths to?

I ran this code in Word but it should work in Excel.

Dim tbl As Table

Set tbl = Me.Tables(1) ' Me is the document the code is in

tbl.Columns(1).Width = Application.CentimetersToPoints(3)
tbl.Columns(2).Width = Application.CentimetersToPoints(6)

 

[AlexDarsigny]

Ok, I added

Dim tbl As TableSet tbl = objDoc.Tables(1) ' Me is the document the code is in


tbl.Columns(1).Width = Application.CentimetersToPoints(3)
tbl.Columns(2).Width = Application.CentimetersToPoints(6)

And i have "Run-time error '5992' Application-defined or object-defined error". Maybe I don't get exacly what "Me" is. I used "objDoc" as your "Me". It's been set as :

Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Open(path_template_report)

 

[Norie]

Me is the document the code is in, so objDoc is what you should have used in it's place.

In place of Application you would use objWord, the Word application.

 

[AlexDarsigny]

Do you think that "Run-time error '5992' Application-defined or object-defined error" is related to the fact that my code relates to :

Dim tbl As TableSet tbl = objDoc.Tables(1) ' Me is the document the code is in
tbl.Columns(1).Width = Application.CentimetersToPoints(3)
tbl.Columns(2).Width = Application.CentimetersToPoints(6)

This code should produce the error then?

[CODE]Dim tbl As TableSet tbl = objDoc.Tables(1) ' Me is the document the code is in
tbl.Columns(1).Width = [COLOR=#333333]objWord[/COLOR].CentimetersToPoints(3)
tbl.Columns(2).Width = [COLOR=#333333]objWord[/COLOR].CentimetersToPoints(6)

 

[Norie]

Both sets of code should work as both Word and Excel have CentimetersToPoints.

I only tested on a table I'd created, how exactly did you create/get the table you want to set the column widths for?

 

[AlexDarsigny]

The step of my sub are :
- Open an existing Word file
- Open an existing Excel file
- Copy a range in the Excel file
- Paste the selection into Word file
- Edit the new table created in the Word file with the same document object I used to paste the excel sheet content.

Can you please download this zip (http://s000.tinyupload.com/?file_id=06192689401906817644) and simply paste its content on C:\ of your computer. Inside Report Builder.xlsm, simply hit "Build Report!" button to start the macro. I put in comment the line that's causing the error so it's suppose to work. Can you please make this sub work on your side first, that would be very helpful to investigate afterwards. Make sure there's no other file than "Report_Builder.xlsm" that's opened when you start the sub.

Thank you!

 

[Norie]

The problem is that you have columns where the cells aren't all the same width.