using CountIf in VB

[Guest]

When i am using the following line of code in my VB6.0 Program

"If WorksheetFunction.CountIf(Cells, rs1.Fields(0).Value) = 0 Then"

the application always throws the following Error message which is not
understable
"Method '~' of object '~' failed"

Can anyone throw some light on this issue.

 

[Bob Phillips]

It must depend upon what you have in rs1.Fields(0).Value.

But using Cells is not a good idea, checking over 16M cells is a tad
wasteful.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Bob for ur reply...

But the issue is not with rs1.fields(0).value.... while in debug mode it
contains the data which is present in the excel sheet.

if you want me to avoid using Cells then what should i use to check whether
the given value is present in the excel sheet.

 

[NickHK]

As you are working in VB6, you need to qualify the objects that are not part
of VB (as you have done with the Fields collection of the Recordset),
otherwise VB does not know which library the objects are from.
You can use the Object Browser to see the object hierarchy. e.g.
XLApp.WorksheetFunction...
where XLApp is you instance of Excel

And Cells belongs to a worksheet. But as Bob says, use a range rather than
all cells on the required sheet.

NickHK

 

[Guest]

Hi,

when i use the qualified names for the fucntions it works well in my system
but fails in the production environment. I tried the following lines

Excel.WorksheetFunction.CountIf(oxlsheet.Range(oxlsheet.Cells(1, 1),
oxlsheet.Cells(1, 256)), rs1.Fields(0).Value) = 0 Then

and this line also throws the same error(ref previous post)

oxlsheet.Cells.Find(What:="'" & rs1.Fields(0).Value & "'", After:=[A1],
LookIn:=xlFormulas, _
LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext,
MatchCase:=False).Activate

What could be the problem?

 

[NickHK]

You have to qualify the objects with the reference to the your instance of
Excel, not Excel itself.

I suppose somewhere you have
Dim XLApp as excel.application
set xlapp=new....

so use
xlapp.WorksheetFunction.CountIf(oxlsheet.....

And qualify ALL the syntax from Excel used in VB6: e.g.
[A6], xlPart, xlFormulas etc.

NickHK

Hi,

when i use the qualified names for the fucntions it works well in my system
but fails in the production environment. I tried the following lines

Excel.WorksheetFunction.CountIf(oxlsheet.Range(oxlsheet.Cells(1, 1),
oxlsheet.Cells(1, 256)), rs1.Fields(0).Value) = 0 Then

and this line also throws the same error(ref previous post)

oxlsheet.Cells.Find(What:="'" & rs1.Fields(0).Value & "'", After:=[A1],
LookIn:=xlFormulas, _
LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext,
MatchCase:=False).Activate

What could be the problem?

--
Premkumar

As you are working in VB6, you need to qualify the objects that are not part
of VB (as you have done with the Fields collection of the Recordset),
otherwise VB does not know which library the objects are from.
You can use the Object Browser to see the object hierarchy. e.g.
XLApp.WorksheetFunction...
where XLApp is you instance of Excel

And Cells belongs to a worksheet. But as Bob says, use a range rather than
all cells on the required sheet.

NickHK

 

[Guest]

Hi,
Thanks for ur reply.

i tried using the instance of Excel object instead of "Excel". then also my
application fails in the production environment(Windows Server2003).
is there any problem with the DLLs. If so please specify which DLL i need
to update in the production machine.


--
Premkumar

You have to qualify the objects with the reference to the your instance of
Excel, not Excel itself.

I suppose somewhere you have
Dim XLApp as excel.application
set xlapp=new....

so use
xlapp.WorksheetFunction.CountIf(oxlsheet.....

And qualify ALL the syntax from Excel used in VB6: e.g.
[A6], xlPart, xlFormulas etc.

NickHK

Hi,

when i use the qualified names for the fucntions it works well in my system
but fails in the production environment. I tried the following lines

Excel.WorksheetFunction.CountIf(oxlsheet.Range(oxlsheet.Cells(1, 1),
oxlsheet.Cells(1, 256)), rs1.Fields(0).Value) = 0 Then

and this line also throws the same error(ref previous post)

oxlsheet.Cells.Find(What:="'" & rs1.Fields(0).Value & "'", After:=[A1],
LookIn:=xlFormulas, _
LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext,
MatchCase:=False).Activate

What could be the problem?

--
Premkumar

As you are working in VB6, you need to qualify the objects that are not part
of VB (as you have done with the Fields collection of the Recordset),
otherwise VB does not know which library the objects are from.
You can use the Object Browser to see the object hierarchy. e.g.
XLApp.WorksheetFunction...
where XLApp is you instance of Excel

And Cells belongs to a worksheet. But as Bob says, use a range rather than
all cells on the required sheet.

NickHK

When i am using the following line of code in my VB6.0 Program

"If WorksheetFunction.CountIf(Cells, rs1.Fields(0).Value) = 0 Then"

the application always throws the following Error message which is not
understable
"Method '~' of object '~' failed"

Can anyone throw some light on this issue.

 

[NickHK]

You have set a reference to the Excel Type library ?

Then, test this basic code:
Private Sub Command1_Click()
Dim XLApp As Excel.Application
Dim XLWB As Excel.Workbook

Set XLApp = New Excel.Application

With XLApp
.Visible = True
Set XLWB = .Workbooks.Add
End With

With XLWB.Worksheets(1).Range("A1")
.Value = "Some text"
MsgBox .Text
End With


XLWB.Close False
Set XLWB = Nothing

XLApp.Quit
Set XLApp = Nothing

End Sub

NickHK

Hi,
Thanks for ur reply.

i tried using the instance of Excel object instead of "Excel". then also my
application fails in the production environment(Windows Server2003).
is there any problem with the DLLs. If so please specify which DLL i need
to update in the production machine.


--
Premkumar

You have to qualify the objects with the reference to the your instance of
Excel, not Excel itself.

I suppose somewhere you have
Dim XLApp as excel.application
set xlapp=new....

so use
xlapp.WorksheetFunction.CountIf(oxlsheet.....

And qualify ALL the syntax from Excel used in VB6: e.g.
[A6], xlPart, xlFormulas etc.

NickHK

Hi,

when i use the qualified names for the fucntions it works well in my system
but fails in the production environment. I tried the following lines

Excel.WorksheetFunction.CountIf(oxlsheet.Range(oxlsheet.Cells(1, 1),
oxlsheet.Cells(1, 256)), rs1.Fields(0).Value) = 0 Then

and this line also throws the same error(ref previous post)

oxlsheet.Cells.Find(What:="'" & rs1.Fields(0).Value & "'", After:=[A1],
LookIn:=xlFormulas, _
LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext,
MatchCase:=False).Activate

What could be the problem?

--
Premkumar


:

As you are working in VB6, you need to qualify the objects that are

not
part

of VB (as you have done with the Fields collection of the Recordset),
otherwise VB does not know which library the objects are from.
You can use the Object Browser to see the object hierarchy. e.g.
XLApp.WorksheetFunction...
where XLApp is you instance of Excel

And Cells belongs to a worksheet. But as Bob says, use a range

rather
than

all cells on the required sheet.

NickHK

When i am using the following line of code in my VB6.0 Program

"If WorksheetFunction.CountIf(Cells, rs1.Fields(0).Value) = 0 Then"

the application always throws the following Error message which is not
understable
"Method '~' of object '~' failed"

Can anyone throw some light on this issue.

 

[Guest]

Hi,

i tried the code u sent... i works fine in production machine(i got the
message box).

but the CountIf function alone fails in the machine.

what should I do next?

Thanks.
--
Premkumar

You have set a reference to the Excel Type library ?

Then, test this basic code:
Private Sub Command1_Click()
Dim XLApp As Excel.Application
Dim XLWB As Excel.Workbook

Set XLApp = New Excel.Application

With XLApp
.Visible = True
Set XLWB = .Workbooks.Add
End With

With XLWB.Worksheets(1).Range("A1")
.Value = "Some text"
MsgBox .Text
End With


XLWB.Close False
Set XLWB = Nothing

XLApp.Quit
Set XLApp = Nothing

End Sub

NickHK

Hi,
Thanks for ur reply.

i tried using the instance of Excel object instead of "Excel". then also my
application fails in the production environment(Windows Server2003).
is there any problem with the DLLs. If so please specify which DLL i need
to update in the production machine.


--
Premkumar

You have to qualify the objects with the reference to the your instance of
Excel, not Excel itself.

I suppose somewhere you have
Dim XLApp as excel.application
set xlapp=new....

so use
xlapp.WorksheetFunction.CountIf(oxlsheet.....

And qualify ALL the syntax from Excel used in VB6: e.g.
[A6], xlPart, xlFormulas etc.

NickHK


Hi,

when i use the qualified names for the fucntions it works well in my
system
but fails in the production environment. I tried the following lines

Excel.WorksheetFunction.CountIf(oxlsheet.Range(oxlsheet.Cells(1, 1),
oxlsheet.Cells(1, 256)), rs1.Fields(0).Value) = 0 Then

and this line also throws the same error(ref previous post)

oxlsheet.Cells.Find(What:="'" & rs1.Fields(0).Value & "'", After:=[A1],
LookIn:=xlFormulas, _
LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext,
MatchCase:=False).Activate

What could be the problem?

--
Premkumar


:

As you are working in VB6, you need to qualify the objects that are not
part
of VB (as you have done with the Fields collection of the Recordset),
otherwise VB does not know which library the objects are from.
You can use the Object Browser to see the object hierarchy. e.g.
XLApp.WorksheetFunction...
where XLApp is you instance of Excel

And Cells belongs to a worksheet. But as Bob says, use a range rather
than
all cells on the required sheet.

NickHK

When i am using the following line of code in my VB6.0 Program

"If WorksheetFunction.CountIf(Cells, rs1.Fields(0).Value) = 0 Then"

the application always throws the following Error message which is not
understable
"Method '~' of object '~' failed"

Can anyone throw some light on this issue.

 

[NickHK]

Show what code you are using, with the basic below that is working AND
qualifying all the object and constants that you use.

NickHK

Hi,

i tried the code u sent... i works fine in production machine(i got the
message box).

but the CountIf function alone fails in the machine.

what should I do next?

Thanks.
--
Premkumar

You have set a reference to the Excel Type library ?

Then, test this basic code:
Private Sub Command1_Click()
Dim XLApp As Excel.Application
Dim XLWB As Excel.Workbook

Set XLApp = New Excel.Application

With XLApp
.Visible = True
Set XLWB = .Workbooks.Add
End With

With XLWB.Worksheets(1).Range("A1")
.Value = "Some text"
MsgBox .Text
End With


XLWB.Close False
Set XLWB = Nothing

XLApp.Quit
Set XLApp = Nothing

End Sub

NickHK

Hi,
Thanks for ur reply.

i tried using the instance of Excel object instead of "Excel". then

also
my

application fails in the production environment(Windows Server2003).
is there any problem with the DLLs. If so please specify which DLL i need
to update in the production machine.


--
Premkumar


:

You have to qualify the objects with the reference to the your

instance
of

Excel, not Excel itself.

I suppose somewhere you have
Dim XLApp as excel.application
set xlapp=new....

so use
xlapp.WorksheetFunction.CountIf(oxlsheet.....

And qualify ALL the syntax from Excel used in VB6: e.g.
[A6], xlPart, xlFormulas etc.

NickHK


Hi,

when i use the qualified names for the fucntions it works well in my
system
but fails in the production environment. I tried the following lines

Excel.WorksheetFunction.CountIf(oxlsheet.Range(oxlsheet.Cells(1, 1),
oxlsheet.Cells(1, 256)), rs1.Fields(0).Value) = 0 Then

and this line also throws the same error(ref previous post)

oxlsheet.Cells.Find(What:="'" & rs1.Fields(0).Value & "'", After:=[A1],
LookIn:=xlFormulas, _
LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext,
MatchCase:=False).Activate

What could be the problem?

--
Premkumar


:

As you are working in VB6, you need to qualify the objects that

are
not

part
of VB (as you have done with the Fields collection of the Recordset),
otherwise VB does not know which library the objects are from.
You can use the Object Browser to see the object hierarchy. e.g.
XLApp.WorksheetFunction...
where XLApp is you instance of Excel

And Cells belongs to a worksheet. But as Bob says, use a range rather
than
all cells on the required sheet.

NickHK

When i am using the following line of code in my VB6.0 Program

"If WorksheetFunction.CountIf(Cells, rs1.Fields(0).Value) = 0 Then"

the application always throws the following Error message

which is
not

understable
"Method '~' of object '~' failed"

Can anyone throw some light on this issue.

 

[Guest]

thanks for ur help...

here is the complete code i used to test the countif function
Public XLApp As Excel.Application
Public XLWB As Excel.Workbook

Private Sub Command1_Click()
If WorksheetFunction.CountIf(Cells, "Some text") = 0 Then
MsgBox "Not Found..."
Else
MsgBox "found...."
End If


End Sub

Private Sub Command2_Click()
XLWB.Close False
Set XLWB = Nothing

XLApp.Quit
Set XLApp = Nothing
End Sub

Private Sub Form_Load()
Set XLApp = New Excel.Application

With XLApp
.Visible = True
Set XLWB = .Workbooks.Add
End With
With XLWB.Worksheets(1).Range("A1")
.Value = "Some text"
MsgBox .Text
End With

End Sub

One more important information is the countif function runs(shows the
msgbox) successfully for the first time. From the second time onwards it
throws the error message as "The method ~ of Object ~ failed".

if you need any further information i am pleased to give..

--
Premkumar

Show what code you are using, with the basic below that is working AND
qualifying all the object and constants that you use.

NickHK

Hi,

i tried the code u sent... i works fine in production machine(i got the
message box).

but the CountIf function alone fails in the machine.

what should I do next?

Thanks.
--
Premkumar

You have set a reference to the Excel Type library ?

Then, test this basic code:
Private Sub Command1_Click()
Dim XLApp As Excel.Application
Dim XLWB As Excel.Workbook

Set XLApp = New Excel.Application

With XLApp
.Visible = True
Set XLWB = .Workbooks.Add
End With

With XLWB.Worksheets(1).Range("A1")
.Value = "Some text"
MsgBox .Text
End With


XLWB.Close False
Set XLWB = Nothing

XLApp.Quit
Set XLApp = Nothing

End Sub

NickHK


Hi,
Thanks for ur reply.

i tried using the instance of Excel object instead of "Excel". then also
my
application fails in the production environment(Windows Server2003).
is there any problem with the DLLs. If so please specify which DLL i need
to update in the production machine.


--
Premkumar


:

You have to qualify the objects with the reference to the your instance
of
Excel, not Excel itself.

I suppose somewhere you have
Dim XLApp as excel.application
set xlapp=new....

so use
xlapp.WorksheetFunction.CountIf(oxlsheet.....

And qualify ALL the syntax from Excel used in VB6: e.g.
[A6], xlPart, xlFormulas etc.

NickHK


Hi,

when i use the qualified names for the fucntions it works well in my
system
but fails in the production environment. I tried the following lines

Excel.WorksheetFunction.CountIf(oxlsheet.Range(oxlsheet.Cells(1, 1),
oxlsheet.Cells(1, 256)), rs1.Fields(0).Value) = 0 Then

and this line also throws the same error(ref previous post)

oxlsheet.Cells.Find(What:="'" & rs1.Fields(0).Value & "'",
After:=[A1],
LookIn:=xlFormulas, _
LookAt:=xlPart,
SearchOrder:=xlByRows, _
SearchDirection:=xlNext,
MatchCase:=False).Activate

What could be the problem?

--
Premkumar


:

As you are working in VB6, you need to qualify the objects that are
not
part
of VB (as you have done with the Fields collection of the
Recordset),
otherwise VB does not know which library the objects are from.
You can use the Object Browser to see the object hierarchy. e.g.
XLApp.WorksheetFunction...
where XLApp is you instance of Excel

And Cells belongs to a worksheet. But as Bob says, use a range
rather
than
all cells on the required sheet.

NickHK

When i am using the following line of code in my VB6.0 Program

"If WorksheetFunction.CountIf(Cells, rs1.Fields(0).Value) = 0
Then"

the application always throws the following Error message which is
not
understable
"Method '~' of object '~' failed"

Can anyone throw some light on this issue.