Nevermind the names. You should be able to address its index number
directly:

TargetRange.Offset(0, intColIndex).Value = rstRecordSet(intColIndex)

Why insert 32k + fields into Excel ? It will probably not make any sense in
a spreadsheet. What happens if you remove that field from your SQL, does it
still err ?

HTH. Best wishes Harald

"Braindeadbeachbum" <Braindeadbeachbum@discussions.microsoft.com> skrev i
melding news:5EA5B149-7254-477F-B58A-376BC1A7A986@microsoft.com...
> 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
> > >
> >
> >
> >