How to stop an Excel table being oversized if transferring to a Word document table

[Aussiebear]

if I wanted to copy an excel table to a Word document table, how do I ensure that the table size does not overwhelm the document table (too many columns for the page size).


Sub MakeTablefromExcelFile()
Dim oExcelApp, oExcelWorkbook, oExcelWorksheet, oExcelRange
Dim nNumOfRows As Long
Dim nNumOfCols As Long
Dim strFile As String
Dim oTable As Table
Dim oRow As Row
Dim oCell As Cell
Dim x As Long, y As Long 'counter for loops
strFile = "c:\Users\Aussiebear\Desktop\BookSample.xlsx" <---- change to your path
Set oExcelApp = CreateObject("Excel.Application")
oExcelApp.Visible = True
Set oExcelWorkbook = oExcelApp.Workbooks.Open(strFile)
Set oExcelWorksheet = oExcelWorkbook.Worksheets(1)
Set oExcelRange = oExcelWorksheet.Range("A1:C8")
nNumOfRows = oExcelRange.Rows.Count
nNumOfCols = oExcelRange.Columns.Count
ActiveDocument.Range.InsertParagraphAfter
'//This next line sets the word table to match the number of rows and columns from the excel table, but what if the initial range had been ("A1: K8")?
'// Does it just shrink the print size down to fit?
Set oTable = ActiveDocument.Tables.Add(Range:=ActiveDocument.Paragraphs.Last.Range, NumRows:=nNumOfRows, NumColumns:=nNumOfCols)
For x = 1 To nNumOfRows
For y = 1 To nNumOfCols
oTable.Cell(x, y).Range.Text = oExcelRange.Cells(x, y).Value
Next y
Next x
oExcelWorkbook.Close False
oExcelApp.Quit
With oTable.Rows(1).Range
.Shading.Texture = wdTextureNone
.Shading.ForegroundPatternColor = wdColorAutomatic
.Shading.BackgroundPatternColor = wdColorLightGreen
End With
End Sub

[jdelano]

This is just me hacking around with the code, maybe you could check the selected Rage width against the page width and if it exceeds it, change the orientation of the page to landscape.



Set oTable = ActiveDocument.Tables.Add(Range:=ActiveDocument.Paragraphs.Last.Range, NumRows:=nNumOfRows, NumColumns:=nNumOfCols)

If oExcelRange.Width > ActiveDocument.PageSetup.PageWidth Then
' the range being copied over is wider than the page, change the orientation
ActiveDocument.PageSetup.Orientation = wdOrientLandscape
oTable.PreferredWidth = ActiveDocument.PageSetup.PageWidth - ActiveDocument.PageSetup.RightMargin
End If

[Aussiebear]

Can it warn you before altering the page layout?

[jdelano]

Sure, you could put a message box asking if they want this to happen

It would look something like this



Dim insertTable as Boolean
insertTable = True

Set oTable = ActiveDocument.Tables.Add(Range:=ActiveDocument.Paragraphs.Last.Range, NumRows:=nNumOfRows, NumColumns:=nNumOfCols)

If oExcelRange.Width > ActiveDocument.PageSetup.PageWidth Then
' the range being copied over is wider than the page, change the orientation
If Msgbox("The selected Excel range is larger than the documents page width. Insert anyway as a landscape page?", vbYesNo, "Table overflow") = vbYes then
ActiveDocument.PageSetup.Orientation = wdOrientLandscape
oTable.PreferredWidth = ActiveDocument.PageSetup.PageWidth - ActiveDocument.PageSetup.RightMargin
Else
insertTable = False
End If
End If

oExcelWorkbook.Close False
oExcelApp.Quit


If insertTable then
For x = 1 To nNumOfRows
For y = 1 To nNumOfCols
oTable.Cell(x, y).Range.Text = oExcelRange.Cells(x, y).Value
Next y
Next x

With oTable.Rows(1).Range
.Shading.Texture = wdTextureNone
.Shading.ForegroundPatternColor = wdColorAutomatic
.Shading.BackgroundPatternColor = wdColorLightGreen
End With
End If

[Aussiebear]

Thank you.

[jdelano]

You're welcome.

[Dave]

Hi Aussiebear. It may be a bit late and not exactly what you want, but if you want to adjust the column sizes so that they all fit in the document, you can do something like this...

With ActiveDocument.PageSetup
TblWdth = .PageWidth - .LeftMargin - .RightMargin - .Gutter
End With
ColWdth = TblWdth / nNumOfCols 'number of columns
With oTable
.Columns.Width = ColWdth
End With


HTH. Dave

[Aussiebear]

Thank you Dave. That would work if..... you had a rough idea of the fit otherwise you could end up with columns too narrow.

[Dave]

Yes of course the known unknowns always present a challenge. I'm just not sure that there is any relationship between an XL range width and a Word document page width .... are they measured in the same units ie. twips/pixels/points? To determine an XL table size in a Word document, I think you need to first insert/paste/generate the XL table in the Word document. However, Word is a funny thing and I have definitely been wrong before.
To stick the whole table in at once and let Word condense the image to fit, you could just set the XL table as a range and then use paste as table in the active document. I had some luck placing large XL ranges as a table in Word documents with this post...
[SOLVED:] Fit large XL range to Word doc (vbaexpress.com) (http://www.vbaexpress.com/forum/showthread.php?71245-Fit-large-XL-range-to-Word-doc&highlight=) (Change apply borders to true)
You could also copypicture the XL table range, add a 1 cell table to the document and then paste special the XL range pic to the 1 cell Word table. Then format the table to the pagewidth. However, both of these routes will not resolve squishy formatting if the number of columns in the XL table is too large. Seems like GIGO would apply. Dave

[Dave]

Had a bit of spare time to google and found out that I'm more than likely wrong (again) as both an XL range and Word pagewidth are measured in points. Seems odd to me that MS would do anything that makes sense.
https://learn.microsoft.com/en-us/office/vba/api/word.page.width
https://learn.microsoft.com/en-us/office/vba/api/excel.range.width
Note: Word returns a Long while XL returns a Double
So, jdelano' s suggested code to compare the XL range width with Word pagewidth should be correct. My apologies @jdelano for being a doubter. Not sure that I will ever understand the interactions between XL and Word. Dave