Using PowerShell to access Excel

Question

I have this script working in my code, but, today, it stopped working. Here is what I have:

$Excel=new-object -comobject excel.application;
$PathXLSX = "C:\Temp\TEMP.xlsx"
$worksheetXLSX=$Excel.Workbooks.open($PathXLSX);

When I run the third line, I get this error:

You cannot call a method on a null-valued expression. At line:1 char:1

+ $worksheetXML=$Excel.workBooks.open($PathXML);
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull

powershellgallery.com/packages/ImportExcel/7.1.0 if you can install modules this will help you greatly — Santiago Squarzon
– Santiago Squarzon, Commented Apr 23, 2021 at 15:18
Agree with @SantiagoSquarzon, however the specific problem is you specified the wrong or mispelled variable to the .Open() method. $PathXML instead of ..XLSX So says the error, though I don't know why the code and the error don't align... Probably a typo somewhere. — Steven
– Steven, Commented Apr 23, 2021 at 15:23
I Did use the powershellgallery.com/packages/ImportExcel/7.1.0, when I started my script. As for the typo, I don't think that is the issue. The code worked fine a few days ago, but today, the same code does not. — Clifton Dunaway
– Clifton Dunaway, Commented Apr 23, 2021 at 16:45

Mike Smith - MCT · Accepted Answer · 2021-04-23 23:39:47Z

1

That error would be generated if the $Excel object was null. Does it exist? Try this to see if returns a type:

$Excel.GetType()

If that is not null then try:

 $excel.GetType().fullname

That should return: Microsoft.Office.Interop.Excel.ApplicationClass

As your codes does not match the error message I'm guessing you have some typos.

$worksheetXLSX=$Excel.Workbooks.open($PathXLSX);
$worksheetXML=$Excel.workBooks.open($PathXML);

answered Apr 23, 2021 at 23:39

Mike Smith - MCT

1,2611 gold badge11 silver badges16 bronze badges

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

9 Comments

Clifton Dunaway Over a year ago

When I ran the $Excel.GetType() I get this: $excel.GetType().fullname I then ran this:$excel.GetType().fullname and got this response: Microsoft.Office.Interop.Excel.ApplicationClass As for the typo, I do not see this in my script. It was successfully for a week, before it stopped.

Clifton Dunaway Over a year ago

Mike, can you send a script that will open the workbook then?

Mike Smith - MCT Over a year ago

Clifton, you code now looks OK. Here's what I used: $Excel = New-Object -ComObject Excel.Application; $PathXLSX = "C:\Test\TEMP.xlsx"; $worksheetXLSX=$Excel.Workbooks.open($PathXLSX);$worksheetXLSX.Sheets.count;

Mike Smith - MCT Over a year ago

Could the Excel file currently be opened and locked by another user or process?

Clifton Dunaway Over a year ago

No, it is on my computer. Would you mind sending me the script that would work?

|

Clifton Dunaway · Accepted Answer · 2021-04-28 16:14:50Z

0

I finally got my script working today!

What I did was stop using the Powershell 5.1, that comes with Windows 10, and installed the new version, 7.1. When I call the script from the new version, it runs fine.

Thanks everyone for your feedback!

answered Apr 28, 2021 at 16:14

Clifton Dunaway

13 bronze badges

Collectives™ on Stack Overflow

Using PowerShell to access Excel

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related