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
 
One of my colleague searched for 5 minutes on the web and found PasteExcelTable. I modify one line in my code and here it is, my table looks exactly like my spreadsheet, no resizing to do!
Code:
objWord.Selection.Paste
is replaced by
Code:
objWord.Selection.PasteExcelTable False, False, True
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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