Hi Harald

When I import using the complete recordset (Range("A2").CopyFromRecordset
rstRecordSet) I receive the following error:
Error - Automation error Unspecified error

I will bet that it's due to the RTF field being to long or unrecognizable. I
know that the Excel limit per cell is 32 767 characters and don't believe the
field is larger than this but I still think that the RTF field is causing the
problem as it's the only thing that changed since the query worked.

If I use the code attached the error is as follows:
Error - Item cannot be found in the collection corresponding to the
requested name or ordinal.

If I change:
TargetRange.Offset(i, intColIndex).Value = rstRecordSet!strFieldName
To:
TargetRange.Offset(i, intColIndex).Value = rstRecordSet!fldName
,where fldName is the actual field name, the query works but this means I
will have to hard code all the field names.

I even tried the following in vain:
rstRecordSet!rstRecordSet.Fields(intColIndex).Name

I know I know, it was a long shot in frustration!

Bottom line is that I will have to loop through the records to enable me to
edit the RTF field but I don't want to hardcode the field names as this just
does not look right and there must be a easier way of doing this.



"Harald Staff" wrote:

> The "an error" is trying to tell you what's wrong. What does it say ?
>
> You dont' specify a cursor. If it's forwardonly then you can't move back and
> forth.
>
> And your paste method is painfully slow. Looping record by record is only
> required if you manipulate them on the fly. This single line ought to do the
> whole job:
>
> Range("A2").CopyFromRecordset rstRecordSet
>
> HTH. Best wishes Harald
>
> "Braindeadbeachbum" <Braindeadbeachbum@discussions.microsoft.com> skrev i
> melding news:E65F95CA-25F4-425D-A781-5D298B20F4D2@microsoft.com...
> > Hi Guys
> >
> > I need to go through each record one at a time to convert RTF to Text from
> > SQL. The code I currently use is the following. My problem is that I
> cannot
> > define the rstRecordSet!strFieldName like I'm doing it now as I receive an
> > error. Is there any way around hard coding each fieldname?
> >
> >
> > Private Sub GetData()
> > Dim i As Integer
> > Dim strCellLocation As String
> > Dim strRecord As String
> > Dim strFieldName As String
> >
> >
> > Sheets("Raw Data").Select
> > Range("A1").Select
> >
> > On Error GoTo ErrorHandler
> >
> > Set connConnect = CreateObject("ADODB.Connection")
> > Set rstRecordSet = CreateObject("ADODB.Recordset")
> >
> > ActiveSheet.Cells.Clear
> > Set TargetRange = ActiveSheet.Cells(1, 1)
> >
> > With connConnect
> > .provider = "SQLOLEDB"
> > .connectionString = "Data Source=?????????;Integrated
> > Security=SSPI;Initial Catalog=???????"
> > .Open
> > End With
> >
> > With rstRecordSet
> > .Open strSQLCommAND, connConnect
> > For intColIndex = 0 To rstRecordSet.Fields.Count - 1
> > TargetRange.Offset(0, intColIndex).Value =
> > rstRecordSet.Fields(intColIndex).Name
> > Next
> > End With
> >
> > i = 2
> > rstRecordSet.movefirst
> > Do Until rstRecordSet.EOF
> > For intColIndex = 0 To rstRecordSet.Fields.Count - 1
> > strFieldName = rstRecordSet.Fields(intColIndex).Name
> > TargetRange.Offset(i, intColIndex).Value =
> rstRecordSet!strFieldName
> > Next
> > i = i + 1
> > rstRecordSet.movenext
> > Loop
> >
> > rstRecordSet.Close
> > Set rstRecordSet = Nothing
> > connConnect.Close
> > Set connConnect = Nothing
> >
> > Exit Sub
> > ErrorHandler:
> > If connConnect.State = 1 Then
> > connConnect.Close
> > Set connConnect = Nothing
> > End If
> > MsgBox "Error - " & Err.Description, vbCritical, Err.Source
> >
> > End Sub
> >
>
>
>