Edit Word Table columns width from Excel vba macro

AlexDarsigny

New Member
Joined
Mar 19, 2014
Messages
10
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
Code:
ActiveDocument.Table.Columns(1).Width = 30

Thank you very much for your help!

Code:
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Can you not use SetWidth for the columns?

Something like this perhaps.
Code:
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
 
Upvote 0
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 :
Code:
.Columns[U][B](1)[/B][/U].SetWidth ColumnWidth:=objWord.CentimetersToPoints(0.5), RulerStyle:=wdAdjustSameWidth
, but it's not supported.
 
Upvote 0
What do you want to set the column widths to?

I ran this code in Word but it should work in Excel.
Code:
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)
 
Upvote 0
Ok, I added
Code:
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 :

Code:
Set objWord = CreateObject("Word.Application")
Set objDoc = objWord.Documents.Open(path_template_report)
 
Upvote 0
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.
 
Upvote 0
Do you think that "Run-time error '5992' Application-defined or object-defined error" is related to the fact that my code relates to :
Code:
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:
[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)
 
Upvote 0
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?
 
Upvote 0
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!
 
Upvote 0
The problem is that you have columns where the cells aren't all the same width.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top