Databases
StackOverflow2013
Postsdbo
POHow to read cell data in excel and output to command prompt
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to read cell data in excel and output to command prompt
    text
    copied!<p>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.</p> <p>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:</p> <p><pre><code> function Invoke([object]$m, [string]$method, $parameters) { $m.PSBase.GetType().InvokeMember( $method, [Reflection.BindingFlags]::InvokeMethod, $null, $m, $parameters,$ciUS ) } </p> <p>$ciUS = [System.Globalization.CultureInfo]'en-US'</p> <p>$objExcel = New-Object -comobject Excel.Application $objExcel.Visible = $False $objExcel.DisplayAlerts = $False</p> <p>$objWorkbook = Invoke $objExcel.Workbooks.Open "C:\PS\User Data.xls" Write-Host "Numer of worksheets: " $objWorkbook.Sheets.Count</p> <p>$objWorksheet = $objWorkbook.Worksheets.Item(1) Write-Host "Worksheet: " $objWorksheet.Name</p> <p>$Forename = $objWorksheet.Cells.Item(2,1).Text $Surname = $objWorksheet.Cells.Item(2,2).Text</p> <p>Write-Host "Forename: " $Forename Write-Host "Surname: " $Surname</p> <p>$objExcel.Quit() If (ps excel) { kill -name excel} </pre></code></p> <p>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!</p> <p>Here is the Exeption problem I mentioned: </p> <p><pre><code> 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 &lt;&lt;&lt;&lt; ( + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException</p> <p>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 &lt;&lt;&lt;&lt; (1) + CategoryInfo : InvalidOperation: (Item:String) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull</p> <p>Worksheet: You cannot call a method on a null-valued expression. At C:\PS\excel.ps1:21 char:37 + $Forename = $objWorksheet.Cells.Item &lt;&lt;&lt;&lt; (2,1).Text + CategoryInfo : InvalidOperation: (Item:String) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull</p> <p>You cannot call a method on a null-valued expression. At C:\PS\excel.ps1:22 char:36 + $Surname = $objWorksheet.Cells.Item &lt;&lt;&lt;&lt; (2,2).Text + CategoryInfo : InvalidOperation: (Item:String) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull</p> <p>Forename: Surname: </pre></code></p> <p>This is the first question I have ever asked, try to be nice! :))</p> <p>Many Thanks</p> <p>Max</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload