search string in Excel

Hi! I am trying to make a PowerShell script to search a string in Excel.

Excel looks like this
A B
-------------------
abcdv 7
zswd 2
dfergh 1
swqwer 8

1. check if $input is same as A1 then $output = 7 then exit
if not check next cell A2 if $input -eq A2 then $output = 2 then exit
if not check next cell A3 if $input -eq A3 then $output = 1 then exit

How can I write this after enabling Excel object like:
$a = New-Object -comobject Excel.Application

$a.Visible = $TRUE

$b = $a.Workbooks.Add()
$c = $b.Worksheets.Item(1)

Thanks in advance
pjo

pjo wrote:
Will it be this simple? I know in the past, I've had problems with
figuring out how to tell Excel to stop processing rows (when it hits the
first empty row). In other words, if you have a dynamic list of rows,
it becomes a bit more difficult.

Have you considered whether you want to save this data in a CSV format,
then you could easily import into PowerShell, and save you having to use
Excel's COM object?

Marco

--
Microsoft MVP - Windows PowerShell
http://www.microsoft.com/mvp

PowerGadgets MVP
http://www.powergadgets.com/mvp

Blog:
http://marcoshaw.blogspot.com

Thanks Marco,

Excel sheet will be dynamic.
So I should consider CSV.

pjo

"Marco Shaw [MVP]" wrote:

How to escape "," in CSV ?
The original Excel cell has "abscdf, gerfs, trf" and this is put as "abscdf,
gerfs, trf"
and this causes confusion to match
$temp = Import-Csv d:\work\test.csv| Where-Object {$_.name -eq "abscdf,
gerfs, trf"}

"pjo" wrote:

$temp = Import-Csv d:\work\test.csv| Where-Object {$_.name -eq "abscdf`,
gerfs`, trf"}


I tried "`" but did not work.

"pjo" wrote:

This was solved by myself.
Thanks !

pjo

"pjo" wrote:

"pjo"
And since others (besides the OP)
are reading these messages too,
one can also get this information (using
almost any Windows language) by using
the free IIS's Microsoft's data parser,
Log Parser 2.2 (with built-in Microsoft
ChartSpace chart maker) and chart the
output too!

Notice: IIS does not need to be running
or installed in order to use Log Parser
for either data parsing or chart making.

And using PowerShell:

# Remove any old test chart.
remove-item testChart.gif

$data = @("A,B",
"abcdv,7",
"zswd,2",
"dfergh,1"
"swqwer,8")

# Log Parser command line version.
$data | LogParser.exe "SELECT
A AS Name,
TO_INT(B) AS Number
FROM STDIN " -i:csv -statsff

# Log Parser command line ChartSpace version.
$data | LogParser.exe "SELECT
A AS Name,
TO_INT(B) AS Number
INTO testChart.gif
FROM STDIN " -i:csv -o:chart `
-chartType:ColumnClustered `
-statsff -viewn -valuesn

# Or if the data is in a file:

$data | out-file testData.csv -encoding ascii

# Did we create the CSV file? To see file,
# use Log Parser's datagrid.
LogParser.exe "SELECT text
FROM testData.csv " `
-i:textline -statsff -o:datagrid

# For general Log Parser CSV information.
LogParser.exe -h -i:csv testData.csv -headerRown

# Remove any old test chart.
remove-item testChart.gif

# Log Parser command line ChartSpace version.
LogParser.exe "SELECT
A AS Name,
TO_INT(B) AS Number
INTO testChart.gif
FROM testData.csv " -i:csv -o:chart `
-chartType:ColumnClustered `
-statsff -viewn -valuesn

Exit

Search the Internet for usage of:

Microsoft's Log Parser
that is fully script enabled
(from the IIS group)

and

Microsoft's ChartSpace Charting
as used within Log Parser, Excel
or even stand alone scripting usage
(via the Office group)

Just two other PowerShell
fully script enabled tools!