Another 1004 Application-defined or Object -defined Error problem

[DatixRon via OfficeKB.com]

Hi folks - just learning VBA so please excuse any silly coding. The
following procedure produces the 1004 error and debug highlights the line
"Cells(rownum, colnum + 1) = rs.Fields(colnum).Value". The first line after
rs.MoveFirst successfully brings in the column headers, the Line after the
While Not rs.EOF starts to bring in the column data - first two are string
and work OK, then 6 numeric fields which error out, then 2 more string fields.
If I cycle through manually using F8, the error still occurs but I can see
that the rs.Fields(colnum).Value is being successfully populated with the SQL
data but seems to have a problem populating the Excel sheet. I have
successfully used the non-SQL part of the routine with other tables without
problem but don't know what's wrong here - any ideas from the experts? By
the way, I'm stuck with Excel97 which doesn't support the CopyRecordset
function so have had to use this approach to manipulate the recordset.

Regards and thanks for any help / ideas.

DatixRon

Public Sub Datix_FRS12_Info1()

Dim OS_User As String
OS_User = Environ("username")

Dim cnn As ADODB.Connection
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "DSN=datixcrm;SERVER=datix;UID=sysadm;PWD=" & DPW

Dim rs As New ADODB.Recordset
rs.ActiveConnection = cnn

Dim sSQL As String

sSQL = sSQL & "SELECT claims_main.cla_otherref, claims_main.cla_name, "
sSQL = sSQL & "claims_main.cla_frs12_ulimit, claims_main.cla_frs12_uprob, "
sSQL = sSQL & "claims_main.cla_frs12_mlimit, claims_main.cla_frs12_mprob, "
sSQL = sSQL & "claims_main.cla_frs12_llimit, claims_main.cla_frs12_lprob, "
sSQL = sSQL & "claims_main.cla_estset, claims_main.cla_type "
sSQL = sSQL & "FROM datixcrm.dbo.claims_main claims_main "
sSQL = sSQL & "WHERE claims_main.cla_otherref = 'CAU/CN/55/GB' "
sSQL = sSQL & "OR claims_main.cla_otherref = 'CAU/CN/41/GB' "
sSQL = sSQL & "ORDER BY claims_main.cla_type"

rs.Open sSQL

rs.MoveFirst
For colnum = 0 To rs.Fields.Count - 1: Cells(1, colnum + 1) = rs.Fields
(colnum).Name: Next 'rem use zeroth element but need x+1 to start at column
1
rownum = 2 'rem start values import at row 2 - row 1 = column headers
While Not rs.EOF
For colnum = 0 To rs.Fields.Count - 1
Cells(rownum, colnum + 1) = rs.Fields(colnum).Value
Next
rs.MoveNext
rownum = rownum + 1 'increment sheet row counter
Wend

rs.Close

Set cnn = Nothing

End Sub

 

[Guest]

Since the Access side seems to be working, problem is probably within the
Cells(rownum, colnum+1)
portion of the statement.
Remember that a database can easily offer up more rows of information than
can be held on an Excel worksheet. I suspect that when it errors out that
rownum is at a value of 65537 or greater. Just hover over rownum while the
line is highlighted in Debug mode and it will show you the value of rownum.

You can check colnum in the same way, I don't see how it could have a
problem, but max value for column number in Excel is 256, so if colnum+1
equates to a value greater than 256, that would be a problem.

 

[DatixRon via OfficeKB.com]

Hi - thanks for prompt reply. Like you I can't work out why the error
because the script works fine for many other tables - some even larger than
this target table. As it happens, there are only some 20 fields in this
table and few thousand rows so doesn't look like a problem with the database
size - especially given that it works with other much larger tables. Doesn't
appear to be a problem with the table data either because I can successfully
access it it with Crystal Reports. It's by using your suggested 'hover mode'
that I was able to establish that the SQL data is being read successfully but
Excel won't play ball. Again, many thanks for your prompt reply and, at
least, you don't seem to have 'died laughing' at my code attempts !

DatixRon

Since the Access side seems to be working, problem is probably within the
Cells(rownum, colnum+1)
portion of the statement.
Remember that a database can easily offer up more rows of information than
can be held on an Excel worksheet. I suspect that when it errors out that
rownum is at a value of 65537 or greater. Just hover over rownum while the
line is highlighted in Debug mode and it will show you the value of rownum.

You can check colnum in the same way, I don't see how it could have a
problem, but max value for column number in Excel is 256, so if colnum+1
equates to a value greater than 256, that would be a problem.

Hi folks - just learning VBA so please excuse any silly coding. The
following procedure produces the 1004 error and debug highlights the line

[quoted text clipped - 58 lines]

End Sub

 

[Guest]

Sometimes it takes me a while.

Your ".Value" is on the wrong side of the =, try:
Cells(rownum, colnum_1).Value = rs.Fields(colnum)
instead.

Hi - thanks for prompt reply. Like you I can't work out why the error
because the script works fine for many other tables - some even larger than
this target table. As it happens, there are only some 20 fields in this
table and few thousand rows so doesn't look like a problem with the database
size - especially given that it works with other much larger tables. Doesn't
appear to be a problem with the table data either because I can successfully
access it it with Crystal Reports. It's by using your suggested 'hover mode'
that I was able to establish that the SQL data is being read successfully but
Excel won't play ball. Again, many thanks for your prompt reply and, at
least, you don't seem to have 'died laughing' at my code attempts !

DatixRon

Since the Access side seems to be working, problem is probably within the
Cells(rownum, colnum+1)
portion of the statement.
Remember that a database can easily offer up more rows of information than
can be held on an Excel worksheet. I suspect that when it errors out that
rownum is at a value of 65537 or greater. Just hover over rownum while the
line is highlighted in Debug mode and it will show you the value of rownum.

You can check colnum in the same way, I don't see how it could have a
problem, but max value for column number in Excel is 256, so if colnum+1
equates to a value greater than 256, that would be a problem.

Hi folks - just learning VBA so please excuse any silly coding. The
following procedure produces the 1004 error and debug highlights the line

[quoted text clipped - 58 lines]

End Sub