Microsoft - Office - Scripting - PowerShell

Notes

Update table of contents

$strDocument = "C:\Temp\Document.docx"
$objMSWord = New-Object -ComObject Word.Application
$objMSWord.Visible = $false
$objDocument = $objMSWord.Documents.Open($strDocument)
 
# Update the table of contents.
# A foreach seems to be needed.
# $objDocument.TablesOfContents[X].Update does nothing, and .Update() gets you:
# "Method invocation failed because [System.__ComObject] does not contain a method named 'Update'."
# https://msdn.microsoft.com/en-us/library/office/microsoft.office.interop.word.tableofcontents(v=office.14).aspx
foreach ($objTOC in $objDocument.TablesOfContents) {
 
	$objTOC.Update()
 
}
 
# Close the document.
$objDocument.Close()
 
# Quit MS Word.
$objMSWord.Quit()

Sources:

Add table of contents

$strDocument = "C:\Temp\Document.docx"
$objMSWord = New-Object -ComObject Word.Application
$objMSWord.Visible = $false
$objDocument = $objMSWord.Documents.Open($strDocument)
 
# Reset the selection to the previous point, to make the following
# Find.Execute search again from the beginning.
# http://www.word.tips.net/T000826_Moving_to_the_Start_or_End_of_the_Real_Document.html
$objMSWord.Selection.GoTo(1, 1) | Out-Null
 
# Search for "<TOC>" and add a table of contents at that point.
if ($objMSWord.Selection.Find.Execute("<TOC>")) {
 
	$objDocument.TablesOfContents.Add($objMSWord.Selection.Range)
 
}
 
# Close the document.
$objDocument.Close()
 
# Quit MS Word.
$objMSWord.Quit()

Source: Office development > Office clients > Office 2010 > Word 2010 > Word 2010 Primary Interop Assembly Reference > Class Library > Microsoft.Office.Interop.Word > TablesOfContents Interface > TablesOfContents Methods > Add Method

SaveAs Excel sheet as CSV with regional CSV separator

// Tested with Excel 2016 Dutch version. Excel 2013 Dutch versions did not seem to need this fix. //

By default Excel VBA code uses the en-US locale and will thus save a CSV with the comma as the separator.

To use the separator supplied by the current locale (in this case a “;”) you need to force locale to True when using the .SaveAs function.

$objMSExcel = New-Object -ComObject Excel.Application
 
# Should Excel be visible?
$objMSExcel.Visible = $false
$objMSExcel.DisplayAlerts = $false
 
$objWorkBook = $objMSExcel.Workbooks.Open("c:\path\to\excel\sheet.xls")
 
foreach ($objWorkSheet in $objWorkBook.Sheets) {
 
	if ($objMSExcel.Application.WorksheetFunction.CountA($objWorkSheet.Cells) -gt 0) {
 
		# Save the workstheet as (filename, fileformat) where fileformat 6 is CSV.
		# Set the Local option at the end to true to prevent save with the US-English CSV separator.
		# http://stackoverflow.com/questions/19265636/save-as-csv-with-semicolon-separator
		# http://stackoverflow.com/questions/25288372/excel-workbooks-saveas-error-in-powershell#25289878
		$objWorkSheet.SaveAs("c:\path\to\excel\sheet.csv",6,[Type]::Missing,[Type]::Missing,[Type]::Missing,[Type]::Missing,[Type]::Missing,[Type]::Missing,[Type]::Missing,$true)
 
	}
 
}
 
# Close the workbook, do not save changes.
$objWorkBook.Close($false)