CODEWITHSUNDEEP
.netexcelpowershellautomation
ThomasMX
ThomasMX

Reputation: 1811

How to open excel workbook from powershell for automation

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.

Upvotes: 15

Views: 99119

Answers (4)

Lans20
Lans20

Reputation: 1

If you just add the path to the variable $FilePath without quotes, it will open automatically:

$FilePath = C:\temp\tempfile.csv

The only problem is that if you did not properly closed the file on Excel, the next time you run it, it will open as READ ONLY and you have to kill the process on TASK manager.

Depending of the needs and coding, sometimes simple works the best.

Upvotes: 0

ChrisF
ChrisF

Reputation: 59

Just let Windows take care of the hard part for you:

explorer.exe $filename

Simples :)

Upvotes: 0

ThomasMX
ThomasMX

Reputation: 1811

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")

Upvotes: 20

TheMadTechnician
TheMadTechnician

Reputation: 36297

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.

Upvotes: 24

Related Questions