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
Thank you very much for your help!
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