Replace words from ADO recordset

[jenhu]

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

 

[Tim Williams]

Some revisions, and untested since I don't hace SQL server to run it
against.
It's much better not to open and close the DB connection for every single
query - just open, run all queries and then close it at the end.

It would be tidier if instead of replacing the query value (the district
number) with the manager name, you had another column to put the manager
into. That way you're not overwriting the original data.

Tim

'***************************************************************************
Sub ReplaceTheDs()

Const Server_Name As String = "myservername"
Const Database_Name As String = "mydbname"
Const User_ID As String = "id"
Const PW As String = "pw"
Dim SQLStr As String
Dim rngVal As Range
Dim DistrictNum As String

Dim Cn As ADODB.Connection
Dim rs As ADODB.Recordset

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

Set rs = New ADODB.Recordset


Set rngVal = Worksheets("Growing Real Sales").Range("A1")

Do While rngVal.Value <> ""
If Left(rngVal.Value, 1) = "D" Then

DistrictNum = Right(rngVal.Value, Len(rngVal.Value) - 1)
SQLStr = "select t.District_Mgr FROM micros.Store_Table t " & _
" where t.District_Num=" & DistrictNum

rs.Open SQLStr, Cn, adOpenStatic

If Not rs.EOF Then
rngVal.Value = rs(0).Value
Else
rngVal.Value = "Not found: " & DistrictNum
End If

If rs.State = adstateopen Then rs.Close

End If

Set rngVal = rngVal.Offset(1, 0)
Loop

On Error Resume Next
Set rs = Nothing
Cn.Close
Set Cn = Nothing


End Sub
'*************************************************