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

 

[AlexDarsigny]

I see. But when I set each column width manually via Table Properties (video : http://s000.tinyupload.com/download.php?file_id=37631956073752272221&t=3763195607375227222112110) and i check "Preferred width", there's some kind of intelligence with the cell resizing and that's exacly what I'm looking for. Is there a possibiliy to directly control the Table Properties windows via an object? An object that can access every tab, field, checkboxes of this windows? I did a macro recording of all the moves I did on the video but those are code for Word only and not excel so it's not adaptable.

 

[Norie]

If you've recorded code in Word it you should be able to run it from Excel, you are automating Word after all.

 

[AlexDarsigny]

The recording looks like that. It's not oriented on column number like it is when you look at the Table Properties windows though :

Selection.Move Unit:=wdColumn, Count:=1    Selection.SelectColumn
    Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
    Selection.Columns.PreferredWidth = CentimetersToPoints(0.5)
    Selection.Move Unit:=wdColumn, Count:=1
    Selection.SelectColumn
    Selection.Move Unit:=wdColumn, Count:=1
    Selection.SelectColumn

I guess if I can activate my Word document and select the whole table from my excel sub, I could use those command. I'll check that tomorrow, but if you find a more convenient way, let me know! Thanks!

 

[Norie]

What exactly are you trying to set the column widths to?

All the tables in the final document look fine, to me anyway.

 

[NdNoviceHlp]

Maybe autofit will work? Dave

 'autofit table cell contents
    With ObjWord.ActiveDocument.Tables(1) 
        .Columns.AutoFit 
    End With

 

[AlexDarsigny]

I want the Word table to look exactly like the Excel sheet. If you compare, you can see that columns are different after the copy/paste action. That's why I want to set every column individually with the Excel sheet column width.
I tried to use the recording I've made but again, it seems to crash because columns where the cells aren't all the same width. I don't understand why I can do the exact manipulation manually but it cannot be managed with the exact same command in run-time.

 '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
        
        objDoc.Tables(objDoc.Tables.Count).Select
        objWord.Selection.Move Unit:=wdColumn, Count:=1
        [U][B]objWord.Selection.SelectColumn[/B][/U][B] '---> Run-time error '4605'[/B]
        objWord.Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
        objWord.Selection.Columns.PreferredWidth = CentimetersToPoints(0.5)
        objWord.Selection.Move Unit:=wdColumn, Count:=1
        objWord.Selection.SelectColumn
        objWord.Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
        objWord.Selection.Columns.PreferredWidth = CentimetersToPoints(0.52)

However, this works since I resize every column (Column 1-7 in Table Properties window) and not a particular column.

objDoc.Tables(objDoc.Tables.Count).Select    
objWord.Selection.Columns.PreferredWidthType = wdPreferredWidthPoints
    objWord.Selection.Columns.PreferredWidth = 40

----------------------
For the Autofit, it screws up my table all the way. But thanks for the hint. I absolutely have to set my width by myself like I'd do manually.

 

[NdNoviceHlp]

It's actually errorring on the line before...U are using late binding and can't use wdcolumn or any "wd" constant. Google "Word numeric constants" and replace the "wdcolumn" with it's numeric constant (or change your code to early binding and set a reference to the Word object Library). Not sure how your second code "works" using wdPreferredWidthPoints? Good luck. Dave

 

[Norie]

NdNoviceHlp

In the file the OP posted a link to there is a reference to the Microsoft Word 14.0 Object Library.

 

[AlexDarsigny]

Indeed, I added Microsoft Word 14.0 Object Library in Excel VBA Editor > Tools > Reference.
I'm running out of solution right now and the only one I'm thinking of would be to send keyboard keys via vba command in order to physically open the Table Properties windows and do the exact manipulation I did manually. But I really don't like that solution since the time taken for each action is unknown and inconsistant.

 

[Norie]

Have you tried setting the width of individual cells in the table?

That might sound tedious but if you can get it to work you should be able to set up some looping to do it.