Hello, I am trying to assign the value of a recordset to a variable (iEmployeeCode) in Excel VBA. The data I am trying to assign to the variable is held in an SQL database and I am using Excel 2003.
Any help that can be given gratefully appreciated.
Thank you
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Thank you, but I am 99% sure that bit of the code works okay. The sEmployeeIdRangeA variable is set to change each time so that it selects the next row. The problem is that I am unable to assign the value of the recordset to the variable iEmployeeCode. The code returns a "Run-time error 13 Type Mismatch" error when I try to run it. I know that its erroring at the "iEmployeeCode = rs.GetRows" line because when I rem it out the code runs through error free.
Thank you
Hi,
Instead of:
iEmployeeCode = rs.GetRows
Use:
If Not(rs.EOF) Then
If Not IsNull(rs.Fields("employee
If IsNumeric(rs.Fields("emplo
iEmployeeCode = rs.Fields("employeecode").
End If
End If
End If
However, I would advise changing your subroutine to...
Sub getRowsTest()
Dim cn As ADODB.Connection
Dim path As String
Dim rs As ADODB.Recordset
Dim sql As String
Dim sEmployeeIdRangeA As String
Dim sEmployeeIdRangeB As String
Dim i As Integer
Dim iEmployeeCode As Integer
i = 2
Set cn = New ADODB.Connection
cn.ConnectionString = "PROVIDER=SQLOLEDB; Password=password;Persist Security Info=True;User ID=crown;DATA SOURCE=APSTO001;INITIAL CATALOG=oodb;"
cn.Open
Do Until i = 148
sEmployeeIdRangeA = "A" & i
sEmployeeIdRangeB = "B" & i
sql = "SELECT employeecode FROM employee WHERE employeeid = '" & Range(sEmployeeIdRangeA) & "'"
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText
If Not (rs.EOF) Then
If Not IsNull(rs.Fields("employee
If IsNumeric(rs.Fields("emplo
iEmployeeCode = rs.Fields("employeecode").
End If
End If
End If
i = i + 1
rs.Close
Loop
Set rs = Nothing
Set cn = Nothing
End Sub
BFN,
fp.
...and, of course, within the Do Until...Loop, iEmployeeCode is set to the return value from the SQL statement but is then never used.
Perhaps the syntax required is:
Cells("B", i) = ""
If Not (rs.EOF) Then
If Not IsNull(rs.Fields("employee
If IsNumeric(rs.Fields("emplo
Cells("B", i) = rs.Fields("employeecode").
End If
End If
End If
BFN,
fp.
Business Accounts
Answer for Membership
by: saurabh726Posted on 2008-10-22 at 04:51:32ID: 22775469
Replace these lines with...
sEmployeeIdRangeA = "A" & i
sEmployeeIdRangeB = "B" & i
This with....
sEmployeeIdRangeA=cells(i,
sEmployeeIdRangeB = cells(i,2)
Saurabh...