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

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

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

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

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

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

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

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