Replace words from ADO recordset
 

Hi expert,

I need to write a little excel vbscript with ADO recordset code to
change the first column, whenever the cell starts with first letter 'D'
(which means a district number), then I need to replace the cell to the
a district manager name.

First of all, I need to loop every row in column A only to find any
cell with a 'D' prefixed word, for example, D1009.
Then open a ADO connect to SQL Server, as long as I find the cell to
match DISTRICT, then it needs to be replaced by DISTRICT_MGR

Can someone help me to finish this VBScript? Thank you!

-------------------------------------------------------------------

Sub ReplaceTheDs()
Dim MaxRows As Long
Dim RowCounter As Long
Dim FoundRow
With Worksheets("Growing Real Sales")
MaxRows = .Range("a1").End(xlDown).Row
For RowCounter = 1 To MaxRows
If Left(.Range("a" & RowCounter).Value, 1) = "D" Then
'Open ADO Recordset here
Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Server_Name = "myservername"
Database_Name = "mydbname" ' Enter your database name here
User_ID = "id"
Password = "pw"
SQLStr = "SELECT 'D' + CAST(District_Num AS char(5)) AS District,
District_Mgr FROM micros.Store_Table"

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name &
";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic

' HOW TO REPLACE THE D TO DISTRICT MANAGER'S NAME????

rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing

End If
Next RowCounter
End With
End Sub


--
jenhu
------------------------------------------------------------------------
jenhu's Profile: http://www.excelforum.com/member.php...o&userid=28969
View this thread: http://www.excelforum.com/showthread...hreadid=527186