How to open excel workbook from powershell for automation

Question

I want to open an excel workbook and read out data, do other kinds of operations, etc. I know that I have to add an assembly reference:

 [Reflection.Assembly]::LoadFile("C:\Program Files\Microsoft Office\Office16\ADDINS\Microsoft Power Query for Excel Integrated\bin\Microsoft.Office.Interop.Excel.dll")

And then I need to instantiate an Application object.

$workbook = New-Object -TypeName Microsoft.Office.Interop.Excel.Application

This however returns an error "A constructor was not found" Isn't by the way Microsoft.Office.Interop.Excel.Application an interface actually? I am wondering how it can be instantiated in this scenario.

Answer 1

You need to open it as a ComObject.

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($FilePath)

In that example you would have needed to define $FilePath as the full path to the Excel file that you are trying to open.

Answer 2

I've found a nice snippet which also runs a macro here

# start Excel
$excel = New-Object -comobject Excel.Application

#open file
$FilePath = 'C:\temp\Book1.xlsm'
$workbook = $excel.Workbooks.Open($FilePath)

#make it visible (just to check what is happening)
$excel.Visible = $true

#access the Application object and run a macro
$app = $excel.Application
$app.Run("Macro1")