[powershell]Pasting table from Excel to Word, autofit table

Question

Currently working on a robot where I want to download an excel file from a system, copy paste the contents of the excel file to a word file as an embedded excel table, but the table is wider than the page (it is already in landscape mode). I am using a powershell script in DAS to execute this step. To solve the problem normally, I would simply right-click on the table and use Autofit -> Fit to Window. How do I do this in powershell?

My script is currently like this:

$word = new-object -comobject Word.application
$word.visible = $true

$doc1 = $word.documents.open($destination)
$bookmark1 = $doc1.Bookmarks.Item("FacilitySheet")

$xl = New-Object -comobject Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Open("C:\Users\Pater\Downloads\spreadsheet.xlsx")
$ws = $wb.ActiveSheet
$Range1 = $ws.UsedRange.Cells
$RowCount = $Range1.rows.count
$CopyRange = $ws.Range("A1:O$RowCount").Copy()

$bookmark1.Range.Paste()

Answer 1

To solve this you must set the AutoFitBehavior to the desired value. Please check https://docs.microsoft.com/en-us/office/vba/api/word.table.autofitbehavior for the allowed values and take into account that the values wdAutoFitContent, etc may not be accessed from Powershell, so you must set the raw values (1 for wdAutoFitContent, for example)

$word = new-object -comobject Word.application
$word.visible = $true

$doc1 = $word.documents.open($destination)
$bookmark1 = $doc1.Bookmarks.Item("FacilitySheet")

$xl = New-Object -comobject Excel.Application
$xl.Visible = $true
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Open("C:\Users\Pater\Downloads\spreadsheet.xlsx")
$ws = $wb.ActiveSheet
$Range1 = $ws.UsedRange.Cells
$RowCount = $Range1.rows.count
$CopyRange = $ws.Range("A1:O$RowCount").Copy()

$bookmark1.Range.Paste()
$bookmark1.Range.AutoFitBehavior(1)