Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace several words to One | Excel Discussion (Misc queries) | |||
multiple sets words replace | New Users to Excel | |||
Search And Replace Whole Words | Excel Programming | |||
How can i find and replace words mean from one file to another?? | Excel Programming | |||
Excel to replace words ? | New Users to Excel |