B
Braindeadbeachbum
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
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