Notes

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
======Microsoft - Office - Scripting - PowerShell======

=====Notes=====

====Update table of contents====

<code powershell>
$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()
</code>
Sources:

  * [[https://msdn.microsoft.com/en-us/library/office/microsoft.office.interop.word.tableofcontents(v=office.14).aspx|Office development >  Office clients >  Office 2010 >  Word 2010 >  Word 2010 Primary Interop Assembly Reference >  Class Library >  Microsoft.Office.Interop.Word >  TableOfContents Interface]]
  * [[https://learn-powershell.net/2015/01/04/creating-a-table-of-contents-in-word-using-powershell/|Learn Powershell | Achieve More - Creating a Table of Contents in Word Using PowerShell]]

====Add table of contents====

<code powershell>
$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()
</code>
Source: [[https://msdn.microsoft.com/en-us/library/office/microsoft.office.interop.word.tablesofcontents.add(v=office.14).aspx|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.

<code powershell>
$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)
</code>
Sources:
  - [[http://stackoverflow.com/questions/19265636/save-as-csv-with-semicolon-separator|stack overflow - Save as CSV with semicolon separator]]
  - [[http://stackoverflow.com/questions/25288372/excel-workbooks-saveas-error-in-powershell|stack overflow - Excel workbooks.saveas() error in powershell]]
  - [[https://msdn.microsoft.com/EN-US/library/ff195820.aspx|Microsoft - MSDN - Worksheet.SaveAs Method (Excel)]]
  - [[https://msdn.microsoft.com/en-us/library/ff198017.aspx|Microsoft - MSDN - XlFileFormat Enumeration (Excel)]]