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
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