Retrieving data from Excel and store it into string variable

[marsulein]

Oooppssss, my bad. Missed out that line.


Code:
--------------------
Dim strName As String
Dim strPosition as String

Dim objExcel As Object
Set objExcel = CreateObject("ADODB.Recordset")

Dim rsData as ADODB.RecordSet
Set rsData = New ADODB.RecordSet
--------------------


After I have opened the connection of the spreadsheet, all the data are
stored in objExcel.

After I have opened the connection to my database, all the data are
stored in rsData which then will be used as filter.


Code:
--------------------
objExcel.MoveFirst

Do While Not objExcel.EOF
strPosition = objExcel.Fields("Position").Value

rsData.Filter = "Position = ' " & strPosition & " ' "

If Not rsData.EOF Then
strName = objExcel.Fields("Name").Value
End If

objExcel.MoveNext
Loop

 

[NickHK]

marsulein,
Why do you use 2 different methods to achieve the same ?

1. Dim objExcel As Object

Set objExcel = CreateObject("ADODB.Recordset")

2. Dim rsData as ADODB.RecordSet

Set rsData = New ADODB.RecordSet

I'm still not clear if you are using VBA in Excel or VB to achieve this ?

NickHK