How to read cell data in excel and output to command prompt
- by Max Ollerenshaw
Hi All,
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
