PDA

View Full Version : Solved: VBA Function to ADO Recordset Data in SHeet



MUSASHI
11-12-2006, 06:54 PM
Hello,

Long time no see. I have been piddling with ADO a bit to deal with various systems at work...working on a simple query from a database, that is then copied into a sheet....once in the sheet, I would like to further manipulate it programatically.

Specifically, If Termination_Date = "" Or Rehire_Date > Termination_Date then Employee = "Current"

Here's my Code
Sub GetTimEmp()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim WSOrig As Worksheet
Dim WSTemp As Worksheet
Dim sSQL As String
Dim FinalRow As Long
Set WSOrig = ActiveSheet
'Build SQL String..
sSQL = "SELECT MASTER_PRM_EMPLOYEE.Employee, Employee_Name, Hire_Date, Rehire_Date, Termination_Date, Custom_Sort FROM MASTER_PRM_EMPLOYEE"
MyConn = "Provider=MSDASQL.1;DRIVER={Timberline Data};DBQ=J:\NEW_PSG;UID=dxxx;PWD=xxxxy;"
Set cnn = New ADODB.Connection
With cnn
.Provider = "MSDASQL.1"
.Open MyConn
End With
MsgBox "You Are Updating the Employee Data"

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=sSQL, ActiveConnection:=cnn, _
CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, _
Options:=adCmdText
' Create the report in a new worksheet
Set WSTemp = Worksheets.Add
'Add Headings
Range("A1:F1").Value = Array("Employee", "Employee_Name", "Hire_Date", "Rehire_Date", "Termination_Date", "Custom_Sort")
'Copy From Recordset to sheet
Range("A2").CopyFromRecordset rst
'Close the connections
rst.Close
cnn.Close
'Format the report
FinalRow = Range("A65536").End(xlUp).Row
'If No Records, then shut it down..
If FinalRow = 1 Then
Application.DisplayAlerts = False
WSTemp.Delete
Application.DisplayAlerts = True
WSOrig.Activate
MsgBox "There are no records"
Exit Sub
End If
Application.DisplayAlerts = False
WSTemp.Delete
Application.DisplayAlerts = True
End Sub

I want to do that basic "calculation" in Range G2:G, using Hire_Date from C2:C, Rehire_Date D2:D, and Termination_Date E2:E.

I've been pouring over various books, just looking for some help.
Any help is appreciated.

Thanks
MUSASHI

Ken Puls
11-12-2006, 10:42 PM
Hi Musashi,

I modified your code a little bit. Personally, I would just apply a formula to the sheet via VBA, rather than try and generate your calculation in the ADO portion. Either could be done, but I find this route quick enough:

Sub GetTimEmp()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim WSOrig As Worksheet
Dim WSTemp As Worksheet
Dim sSQL As String
Dim FinalRow As Long
Set WSOrig = ActiveSheet
'Build SQL String..
sSQL = "SELECT MASTER_PRM_EMPLOYEE.Employee, Employee_Name, Hire_Date, Rehire_Date, Termination_Date, Custom_Sort FROM MASTER_PRM_EMPLOYEE"
MyConn = "Provider=MSDASQL.1;DRIVER={Timberline Data};DBQ=J:\NEW_PSG;UID=dxxx;PWD=xxxxy;"
Set cnn = New ADODB.Connection
With cnn
.Provider = "MSDASQL.1"
.Open MyConn
End With
MsgBox "You Are Updating the Employee Data"

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=sSQL, ActiveConnection:=cnn, _
CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, _
Options:=adCmdText
' Create the report in a new worksheet
Set WSTemp = Worksheets.Add

With WSTemp
'Add Headings
.Range("A1:F1").Value = Array("Employee", "Employee_Name", "Hire_Date", "Rehire_Date", "Termination_Date", "Custom_Sort")
'Copy From Recordset to sheet
.Range("A2").CopyFromRecordset rst
'Close the connections
rst.Close
cnn.Close

'Format the report
If .Range("A" & .Rows.Count).End(xlUp).Row = 1 Then
'If No Records, then shut it down..
Application.DisplayAlerts = False
WSTemp.Delete
Application.DisplayAlerts = True
WSOrig.Activate
MsgBox "There are no records"
Exit Sub
Else
'Insert formulas
With .Range("G2:G" & .Range("A" & .Rows.Count).End(xlUp).Row)
.FormulaR1C1 = "=IF(OR(RC[-2]="""",RC[-3]>RC[-2]),""Current"")"
End With
End If
End With

Application.DisplayAlerts = False
WSTemp.Delete '??Really??
Application.DisplayAlerts = True
End Sub

HTH,

MUSASHI
11-13-2006, 08:33 AM
Ken,

Thanks! Duh!

One issue...say the activesheet = kenpuls

With my original code, if the code is executed from there, then the ranges from A:F on kenpuls (WSOrig) would be populated...a temporary sheet would capture the recordset, then be deleted....

Now, I get the range of formulas to show current employees...and the data, on a totally new sheet...each time code is executed...so sheets are adding up fast!!!! I want to just make it write into the range of that sheet everytime WSOrig - kenpuls)..not add and keep a new sheet each time.

The formula code....worked great, ugh! everytime I learn one thing...gotta learn another...!!!!R1C1..

Any ideas on how to do that?

Thanks much

MUSASHI
11-13-2006, 06:00 PM
NM, Duh again, I got it!!!!

Thanks again. Appreciate it.

Ken Puls
11-14-2006, 06:26 AM
Sorry, Musashi!

I was away from my computer all day yesterday. Glad you got it sorted out though. :)