How to read cell data in excel and output to command prompt

Question

I'm a sys admin and I am trying to learn how to use powershell... I have never done any type of scripting or coding before and I have been teaching myself online by learning from the technet script centre and online forums.

What I am trying to accomplish is to open an excel spreadsheet get information from it (usernames and password) and then output it into the command prompt in powershell. When ever I try to do this I get an Exception calling "InvokeMember" anyway, here is the code I have so far:

 
function Invoke([object]$m, [string]$method, $parameters)
{
$m.PSBase.GetType().InvokeMember(
$method, [Reflection.BindingFlags]::InvokeMethod, $null, $m, $parameters,$ciUS )
} 

$ciUS = [System.Globalization.CultureInfo]'en-US'

$objExcel = New-Object -comobject Excel.Application
$objExcel.Visible = $False
$objExcel.DisplayAlerts = $False

$objWorkbook = Invoke $objExcel.Workbooks.Open "C:\PS\User Data.xls"
Write-Host "Numer of worksheets: " $objWorkbook.Sheets.Count

$objWorksheet = $objWorkbook.Worksheets.Item(1)
Write-Host "Worksheet: " $objWorksheet.Name

$Forename = $objWorksheet.Cells.Item(2,1).Text
$Surname = $objWorksheet.Cells.Item(2,2).Text

Write-Host "Forename: " $Forename
Write-Host "Surname: " $Surname

$objExcel.Quit()
If (ps excel) { kill -name excel}

I have read many different posts on forums and articles on how to try and get around the en-US problem but I cannot seem to get around it and hope that someone here can help!

Here is the Exeption problem I mentioned:

Exception calling "InvokeMember" with "6" argument(s): "Method 'System.Management.Automation.PSMethod.C:\PS\User Data.x
ls' not found."
At C:\PS\excel.ps1:3 char:33
+ $m.PSBase.GetType().InvokeMember <<<< (
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

Numer of worksheets:
You cannot call a method on a null-valued expression.
At C:\PS\excel.ps1:18 char:45
+ $objWorksheet = $objWorkbook.Worksheets.Item <<<< (1)
    + CategoryInfo          : InvalidOperation: (Item:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Worksheet:
You cannot call a method on a null-valued expression.
At C:\PS\excel.ps1:21 char:37
+ $Forename = $objWorksheet.Cells.Item <<<< (2,1).Text
    + CategoryInfo          : InvalidOperation: (Item:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At C:\PS\excel.ps1:22 char:36
+ $Surname = $objWorksheet.Cells.Item <<<< (2,2).Text
    + CategoryInfo          : InvalidOperation: (Item:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

Forename:
Surname:

This is the first question I have ever asked, try to be nice! :))

Many Thanks

Max

Answer 1

Just for fun, try modifying your code to run against an xls file that doesn't have a space in its name. If that works, then you need to escape the space in the file's name in this line:

$objWorkbook = Invoke $objExcel.Workbooks.Open "C:\PS\User Data.xls"

or perhaps quote the parameter in this one:

$m.PSBase.GetType().InvokeMember(
$method, [Reflection.BindingFlags]::InvokeMethod, $null, $m, $parameters,$ciUS )

Answer 2

I tried modifying the code and I have got it kind of working!

Instead of using

$objWorkbook = Invoke $objExcel.Workbooks.Open "C:\PS\User Data.xls"

I took away the Workbooks.Open "C:\PS\User Data.xls" and have created a variable $file = "C:\PS\User Data.xls"

Now I use

$objWorkbook = Invoke $objExcel.Workbooks Open($File)

And it nearly works! It now does not throw any errors at me and here is the outcome in cmd

Numer of worksheets: 3
Worksheet: List Of Names
Forename:
Surname:

But for some reason it still doesnt display the $Forename or $Surname

P.S I've also done as you have said Massimo and created a login on stack overflow and asked the question there!

Thanks for your help guys

Answer 3

Try using .Value2 instead of text. Here is a simple example of how to open an Excel workbook and read a value:

$xl = New-Object -comobject Excel.Application
$wb = $xl.WorkBooks.Open("C:\temp\test.xls")
$ws = $wb.Worksheets.Item(1)
Write-Host $ws.Cells.Item(2,1).Value2

Answer 4

thanks for your help...I have fixed the problem! I was using PowerShell V2 CTP2, now I have upgraded to CTP3 this has fixed the language barrier problem and I can now run scripts without worrying about the invoke function and all is fine!

Many Thanks

Max