How to open excel workbook from powershell for automation
58,100
Solution 1
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")
Solution 2
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.
Comments
-
ThomasMX almost 2 years
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.
-
Geoff Langenderfer about 4 yearsin $app.Run("Macro1"), what is Macro1 referencing? A file? A variable?
-
ThomasMX about 4 years@GeoffLangenderfer TBH I can't quite recall becaues I posted this answer a while ago. I guess it is the name of a Sub( ) in either of the macro files. See doco at docs.microsoft.com/en-us/office/vba/api/excel.application.run
-
Simon Tewsi over 3 years
$excel.Visible = $true
was really useful to make the spreadsheet visible. -
Tony almost 3 yearsA CSV file isn't an Excel file. Excel can open it, but it's just text with comma-separated values (hence the name). Native Excel XLS files are binary, defined here. Newer XLSX files are ZIP containers with data in the Office Open XML standard (ECMA-376 and ISO/IEC 29500:2008).