using CountIf in VB
 

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.

--
Premkumar

using CountIf in VB
 

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)



"Prem" wrote in message
...
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.

--
Premkumar

using CountIf in VB
 

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.

--
Premkumar


"Bob Phillips" wrote:

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)



"Prem" wrote in message
...
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.

--
Premkumar

using CountIf in VB
 

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

"Prem" wrote in message
...
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.

--
Premkumar

using CountIf in VB
 

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(oxl sheet.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


"NickHK" wrote:

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

"Prem" wrote in message
...
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.

--
Premkumar

using CountIf in VB
 

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


"Prem" wrote in message
...
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(oxl sheet.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


"NickHK" wrote:

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

"Prem" wrote in message
...
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.

--
Premkumar

using CountIf in VB
 

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


"NickHK" wrote:

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


"Prem" wrote in message
...
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(oxl sheet.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


"NickHK" wrote:

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

"Prem" wrote in message
...
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.

--
Premkumar

using CountIf in VB
 

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


"Prem" wrote in message
...
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


"NickHK" wrote:

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


"Prem" wrote in message
...
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(oxl sheet.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


"NickHK" wrote:

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

"Prem" wrote in message
...
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.

--
Premkumar

using CountIf in VB
 

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


"NickHK" wrote:

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


"Prem" wrote in message
...
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


"NickHK" wrote:

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


"Prem" wrote in message
...
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(oxl sheet.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


"NickHK" wrote:

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

"Prem" wrote in message
...
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.

--
Premkumar

using CountIf in VB
 

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

NickHK

"Prem" wrote in message
...
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


"NickHK" wrote:

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


"Prem" wrote in message
...
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


"NickHK" wrote:

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


"Prem" wrote in message
...
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(oxl sheet.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


"NickHK" wrote:

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

"Prem" wrote in message
...
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.

--
Premkumar

using CountIf in VB
 

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


"NickHK" wrote:

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

NickHK

"Prem" wrote in message
...
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


"NickHK" wrote:

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


"Prem" wrote in message
...
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


"NickHK" wrote:

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


"Prem" wrote in message
...
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(oxl sheet.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


"NickHK" wrote:

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

"Prem" wrote in message
...
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.

--
Premkumar