15

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.

Improve this question
1
  • Depending upon exactly what you need to do with the document, take a look at the ImportExcel by Doug Finke, or use it as an ODBC data source. For certain tasks, these will be much faster and easier than automating Excel itself. Commented Jun 6, 2016 at 20:22

4 Answers 4

Reset to default
24

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

21

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")
Improve this answer

4 Comments

in $app.Run("Macro1"), what is Macro1 referencing? A file? A variable?
@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 learn.microsoft.com/en-us/office/vba/api/excel.application.run
$excel.Visible = $true was really useful to make the spreadsheet visible.
Might be just me. But I need to specifiy it like this to make it work. Else i get an error about core types due to my language mode. $excel.Application.Visible = $true
0

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

explorer.exe $filename

Simples :)

Improve this answer

2 Comments

This works and is the simplest answer here.
the answer is most relevant to question
0

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.

Improve this answer

1 Comment

A 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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.