11

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.

Community
  • 1
  • 1
ThomasMX
  • 1,643
  • 2
  • 19
  • 35
  • Depending upon exactly what you need to do with the document, take a look at the [ImportExcel](https://blogs.technet.microsoft.com/heyscriptingguy/2015/11/25/introducing-the-powershell-excel-module-2/) 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. – alroc Jun 06 '16 at 20:22

4 Answers4

23

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.

TheMadTechnician
  • 34,906
  • 3
  • 42
  • 56
16

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")
ThomasMX
  • 1,643
  • 2
  • 19
  • 35
  • 1
    in $app.Run("Macro1"), what is Macro1 referencing? A file? A variable? – Geoff Langenderfer Feb 17 '20 at 14:47
  • 1
    @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 https://learn.microsoft.com/en-us/office/vba/api/excel.application.run – ThomasMX Mar 09 '20 at 13:43
  • `$excel.Visible = $true` was really useful to make the spreadsheet visible. – Simon Elms Feb 09 '21 at 22:33
  • 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` – zinen Oct 07 '22 at 12:19
0

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

explorer.exe $filename

Simples :)

ChrisF
  • 51
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.

stateMachine
  • 5,227
  • 4
  • 13
  • 29
Lans20
  • 1
  • 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](https://learn.microsoft.com/en-us/openspecs/office_file_formats/ms-xls/cd03cb5f-ca02-4934-a391-bb674cb8aa06). Newer XLSX files are ZIP containers with data in the Office Open XML standard ([ECMA-376](https://www.ecma-international.org/publications-and-standards/standards/ecma-376/) and [ISO/IEC 29500:2008](https://learn.microsoft.com/en-us/openspecs/office_standards/ms-oi29500/1fd4a662-8623-49c0-82f0-18fa91b413b8)). – Tony Jul 04 '21 at 21:04