Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

share|improve this question
    
I have a feeling this would be a lot more in place at StackOverflow... –  Massimo Sep 3 '09 at 21:27
    
You reckon? I'm not sure if SO users are going to know Power Shell scripting... –  Mark Henderson Sep 3 '09 at 22:10
    
Actually, on second thoughts, I just went through all the code and it looks very similar to interfacing with the Excel ActiveX object (probably because it is), so on this basis, perhaps SO is the better place for it. –  Mark Henderson Sep 3 '09 at 22:13
    
It is exactly the way you'd interface with an Excel worksheet in a .NET application; and that's quite to be expected, since PowerShell actually is a .NET language... –  Massimo Sep 3 '09 at 22:59

4 Answers 4

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 )
share|improve this answer

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

share|improve this answer
    
What does your excel spreadsheet look like? I've tested it and it works and the spreadsheet should look like this: Forename,Surname Max,Ollerenshaw Also if you can get the user file in csv file, you could use import-csv cmdlet and that might make things easier –  fenster Sep 4 '09 at 18:41

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
share|improve this answer
    
Hey, I tried that and it didnt work, there are no errors but the cmd prompt doesn't output the name Max –  Max Ollerenshaw Sep 4 '09 at 23:12
    
Ok, then I would suggest some exploration. At the command line do all the steps up to where you would do the Write-Host. At that point do this: **$ws.Cells.Item(2,1) | fl *** Then look for your value in the output. –  EBGreen Sep 8 '09 at 14:27

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.