Reading from Excel

[Kilgore]

I can find numerous examples of writing information out to a spreadsheet, but
one common task that I do in VBScript is open a spreadsheet and read the
servername out of column A and place the result of whatever I am looking for
in column B. I think that if I had code that would even allow me to read the
information out of cell 1,1 I could figure the rest of it out; here is the
script that I am trying to get to work:

$a = New-Object -comobject Excel.Application

$a.Visible = $True

$b = $a.Workbooks.Open("c:\scripts\test.xls")
$c = $b.Worksheets.Item(1)

Write-Host $c.Cells.Item(1,1)

It does not write the contents of that cell out though...

[hrh1818]

On Jun 30, 11:52 pm, Kilgore <Kilg...@discussions.microsoft.com>
wrote:

Niel Chambers posted the following code on June 20 in this newsgroup.

There's likely a much nicer way to do this. I've not spent much time
playing with the filesystem but I've done a little Excel - so this is
how I would start:

$xl = new-object -com Excel.Application
foreach ( $wbk in ( ls *.xls ) )
{
$xl.Workbooks.open($wbk) | out-null #drop the object info
output
$xl.Cells.Item(1,1).Value()
$xl.Cells.Item(3,3).Value()
$xl.Cells.Item(6,8).Value()
$xl.Workbooks.Close()

}

You'll probably need some error checking for cells with no data but
hopefully this will get you started.

Cheers,
n

[Kilgore]

Thanks a bunch, that's got me on the right track and it is working for me.

[Jason]

Hi Kilgore:

The following code works to read whatever range of cells you specify (with
Excel 2007 at least), but the EXCEL.EXE process doesn't terminate afterwards
despite the efforts made in the script to close it gracefully (workin' on
it...).

################################

$path = "$pwd\book.xls"

# $excel = New-Object -com "Excel.Application" -strict
$excel = New-Object "Microsoft.Office.Interop.Excel.ApplicationClass"
$excel.Visible = $false
$excel.ScreenUpdating = $false
$excel.DisplayAlerts = $false

# This part can be abbreviated.
$workbooks = $excel.workbooks
$workbook = $workbooks.open($path)
$sheets = $workbook.worksheets
$activesheet = $sheets.item("Sheet1")
$range = $activesheet.range("A1","A1")
$range.Value2

# Try to coax the excel.exe process to go away...(fails)
ForEach ($item In $workbooks) { $item.Saved = $true ; $item.Close() }
$excel.Quit()
$range = $sheets = $activesheet = $workbook = $excel = $null

##########################

Also, it might not be good enough for your needs, but Excel can open/use CSV
files directly, and PowerShell handles CSV data beautifully. Maybe your
script could just use and update a simple CSV file instead?

Cheers,
Jason

------------------------------------------------------
PowerShell Training at SANS Conferences
http://www.WindowsPowerShellTraining.com
------------------------------------------------------

[Neil Chambers]

On 2007-07-01 19:11:47 +0100, "Jason" <nos...@nospam.com> said:

> Hi Kilgore:
>
> The following code works to read whatever range of cells you specify
> (with Excel 2007 at least), but the EXCEL.EXE process doesn't terminate
> afterwards despite the efforts made in the script to close it
> gracefully (workin' on it...).

<snip>

>
> # Try to coax the excel.exe process to go away...(fails)
> ForEach ($item In $workbooks) { $item.Saved = $true ; $item.Close() }
> $excel.Quit()
> $range = $sheets = $activesheet = $workbook = $excel = $null

<snip>

The Excel process is orphaned at that point and needs no further
control. The Garbage Collector kicks in periodically and cleans up for
you. This is by design :-)

You can see for yourself by asking the GC to collect:

[System.GC]::Collect()

You could, of course, add this to your script if you _really_ wanted to
but I don't see the benefit as this is already taken care of.

Cheers,
Neil

[Kilgore]

Well I noticed that the excel.exe stays active believe it or not! And
wondered the very same thing; in vbscrript it's a quick "objExcel.Quit" and
Microsoft Bob's your uncle.

Thanks Neil for the explanation on garbage collection and the orphaned
process; I guess this is different in POSH than VBScript. Knowing that it is
by design is....well good to know. I've toyed with the idea of out-csv and
how it's better to get into the cmdlet world of doing this as there may be an
out-Excel one day. I like the *.xls way of doing it though because I'm often
working with tabs; I think I'll try it anyhow though just to see the
differences.

Thanks to all who posted.

[Jason]

Hi Kilgore:

If you want it, I've put that code above into a more reusable function.
I've given up on trying to force the immediate *graceful* termination of the
Excel process, but there's Neil's tip if the default clean-up interval is
too long (thanks Neil).

######################################

function Read-Excel ($path = $(throw "Enter full path to Excel spreadsheet
file."),
$sheetname = "Sheet1", $firstcelll = "A1",
$lastcell = "A1")
{
# Assume present working directory if full path to file not given.
if ($path -notmatch "\\") { $path = "$pwd\$path" }

$excel = new-object -com "Excel.Application"

$excel.Visible = $false
$excel.ScreenUpdating = $false
$excel.DisplayAlerts = $false

$workbooks = $excel.workbooks
$workbook = $workbooks.open($path)
$activesheet = $workbook.worksheets.item($sheetname)

$range = $activesheet.range($firstcell,$lastcell)
$range.Value2

ForEach ($item In $workbooks) { $item.Saved = $true ; $item.Close() }
$excel.Quit()

$range = $activesheet = $workbook = $workbooks = $excel = $null
}

#############################################