Help with counting a column 1

[marshyrob]

Hi All

I have the following script below as part of a bigger script.

It basically collects ports up and down on cisco switches and then puts the stats into Excel. That all works fine but i need to read all of column C1 and count the 2's (down)and provide the total amount with an echo.

Can anyone help as the code i have here just tells me the value of the cell each time?

Set objRange = objExcel.Range("C1").EntireColumn
i = 1

Do Until objExcel.Cells(i, 3).Value = ""
strName = objExcel.Cells(i, 3).Value
Set objSearch = objRange.Find(strName)

If objSearch Is Nothing Then
Wscript.Echo strName & " was not found."
Else
Wscript.Echo strName & " was found."
End If
i = i + 1
Loop

 

[PHV]

Perhaps the Excel.Application.CountIf worksheet function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

How about a different approach?

Set cn = CreateObject("ADODB.Connection")

strFile = "C:\Docs\Tek-Tips.xls"

'[URL unfurl="true"]http://www.connectionstrings.com/?carrier=excel[/URL]
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& strFile & ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"

cn.Open strCon
    
Set rs = CreateObject("ADODB.Recordset")

strSQL = "Select Count(ColName) As CountCol From [Sheet1$] Where ColName=2"
rs.Open strSQL, cn

MsgBox rs.Fields("CountCol")

 

[marshyrob]

Hi Remou

Thanks for the code, i am getting an error at the line:

rs.Open strSQL, cn

saying "no value given for one or more parameters"

Any ideas?

 

[Remou]

Did you change ColName to the name of column C? the code is intended for an Excel sheet set up as a table, with column headers (HDR=Yes).

 

[marshyrob]

That will be the issue as the columns dont have names, just data.

Can i get around this, its not really a table?

 

[Remou]

Yes. Column C will be F3, so:

Set cn = CreateObject("ADODB.Connection")

strFile = "C:\Docs\Tek-Tips.xls"

'[URL unfurl="true"]http://www.connectionstrings.com/?carrier=excel[/URL]
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
& strFile & ";Extended Properties='Excel 8.0;HDR=No';"

cn.Open strCon
    
Set rs = CreateObject("ADODB.Recordset")

strSQL = "Select Count(*) As CountCol From [Sheet1$] Where [F3]=2"
rs.Open strSQL, cn

MsgBox rs.Fields("CountCol")

 

[marshyrob]

Brillinat works a charm!!

Really appreciate your help. Have a Star!

Rob

 

[PHV]

What about this (one line ...)?
MsgBox objExcel.CountIf(objExcel.Columns(3),"2")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

Wouldn't objExcel need another line to say what it is?

 

[PHV]

I thought (reading the OP) that the Excel.Application was already instantiated and the workbook open.

 

[marshyrob]

that works too, ill use that as its only one line!

Now i need to paste the value into a cell and not echo it back. Any ideas??

Really appreciate your help guys!

Rob

 

[PHV]

i need to paste the value into a cell
Which cell ? Here an example for X5:
objExcel.Range("X5").Value = objExcel.CountIf(objExcel.Columns(3),"2")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886