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)