Error # 1004 Application-Defined or object-defined error 1

[dellyjm]

This is the code guys. The 6th column in the table is numeric.

I keep getting Error # 1004 Application-Defined or object-defined error when I try to put data into that cell.

conn.Open "DSN=foxweb;UID=;PWD=;SourceDB=c:\HRM;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"

rs.Open "select * from hrdept01", conn, adOpenStatic, adLockOptimistic

iColCnt = rs.Fields().Count
iRecordCnt = rs.RecordCount

xl.Application.Visible = True
xl.Application.Workbooks.Add
xl.Application.Worksheets("Sheet1&quot.Activate

Do Until rs.EOF
For iFor1 = 1 To iRecordCnt
For iFor2 = 1 To iColCnt
If Len(Trim(rs.Fields(iFor2 - 1).Value)) > 0 Then
xl.Cells(iFor1, iFor2) = rs.Fields(iFor2 - 1).Value
End If
Next
rs.MoveNext
Next
Loop

Could someone tell me what could/is going wrong?

Delton.
deltonphillips@hotmail.com

 

[S3066]

I seem to be getting that same error 1004, too. I'm invoking the "Value" property, which seems to be causing the error; don't know why.

If you get any responses, I'd be most curious.

--SL

 

[acron]

What object was assigned to rs ?

The problem probably lies in code before that which reported the error.

AC

 

[dsi]

You can't assign a value to the Cells object. You need to specify what part of the cell you are modifying.

xl.Cells(iFor1, iFor2).Value = ...

 

[acron]

dsi,

Value is the default property of Cells, so does not need to be specified.

Cells(1,1) = 5, will place the value 5 in A1 of the active sheet.

I do think the problem is related to hoe the used objects are initiated.

AC

 

[dsb]

Are you sure the error is coming from the
Cells(..)=rs.fields
line ?


Depending on how smart the Trim() function is, it may occur on the privious line if the recordset returns a NULL.
This is different from a "" and there is no ASCII
character to represent it.

you might try:

If Len(Trim("" & rs.Fields(iFor2 - 1).Value)) > 0 Then

The "" & rs.fields forces a conversion to string and always insures at least a "&quot

dsb

 

[dellyjm]

With regards to the rs question

Set rs = new adodb.recordset

This is code i've done many times before, so i'm not exactly sure why this is happening now.

Oh by the way, I resigned from that job last August.

Delton

 

[acron]

Ok, so we know what rs is. What is xl, an reference to an instance of Excel.

Maybe

xl.Workbooks.Add
xl.Worksheets("Sheet1&quot.Activate

Is what you should be using

AC

 

[dsi]

acron: I did not realize that .Value was the default for the cells object. Thanks for pointing that out!