B
BerkshireGuy
Hello,
I have the following code, which works, but it drags.
What I need to do, is loop through each worksheet and if the name has
Detail in it, read in the policy number and then connect to another
datasource to lookup information based on this policy. Any suggestions
would be appreicated:
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConn As String
Dim strPolicyNumber As String
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim i As Integer
Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
Set rng = Cells(Rows.Count, 1).End(xlUp)
' zzzzed out server information
strConn = "Driver={SQL Server};" & _
"Server=zzzzz;" & _
"Database=zzzzz;" & _
"Uid=izzzzz;" & _
"Pwd="
Set cn = New ADODB.Connection
cn.Open strConn
Set rst = New ADODB.Recordset
rst.Open "Select polnum, F_EntryDate From tblPolicyInformation", cn,
adOpenForwardOnly, adLockReadOnly, adCmdText
For Each ws In wb.Worksheets
If InStr(1, ws.Name, "Detail") > 0 Then
ws.Select
ws.Range("I:I").Select
rst.MoveFirst
For i = rng.Row To 3 Step -1
rst.MoveFirst ' Make sure pojnter is at the top
strPolicyNumber = Cells(i, "B").Value
If strPolicyNumber <> "" Then
rst.Find "polnum = '" & strPolicyNumber & "'" -
This is where a delay happens
If Not rst.EOF Then
ws.Cells(i, "I").Value = rst("F_EntryDate")
End If
End If
Next i
End If
Next ws
Thanks,
Brian
I have the following code, which works, but it drags.
What I need to do, is loop through each worksheet and if the name has
Detail in it, read in the policy number and then connect to another
datasource to lookup information based on this policy. Any suggestions
would be appreicated:
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strConn As String
Dim strPolicyNumber As String
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As Range
Dim i As Integer
Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
Set rng = Cells(Rows.Count, 1).End(xlUp)
' zzzzed out server information
strConn = "Driver={SQL Server};" & _
"Server=zzzzz;" & _
"Database=zzzzz;" & _
"Uid=izzzzz;" & _
"Pwd="
Set cn = New ADODB.Connection
cn.Open strConn
Set rst = New ADODB.Recordset
rst.Open "Select polnum, F_EntryDate From tblPolicyInformation", cn,
adOpenForwardOnly, adLockReadOnly, adCmdText
For Each ws In wb.Worksheets
If InStr(1, ws.Name, "Detail") > 0 Then
ws.Select
ws.Range("I:I").Select
rst.MoveFirst
For i = rng.Row To 3 Step -1
rst.MoveFirst ' Make sure pojnter is at the top
strPolicyNumber = Cells(i, "B").Value
If strPolicyNumber <> "" Then
rst.Find "polnum = '" & strPolicyNumber & "'" -
This is where a delay happens
If Not rst.EOF Then
ws.Cells(i, "I").Value = rst("F_EntryDate")
End If
End If
Next i
End If
Next ws
Thanks,
Brian