B
brittonsm
I have been using this old for a while and now I've discovered an
error. What it does is extract data from a Lotus Notes database
(*.nsf), which I have no control over, and places the selected info
into an Excel file. Problem I'm having is that its truncating the
data it's grabbing - So the question, with this code, how do I
determine the field size and ensure I "collecting" all of the
information within that field? I'm a "good novice" - So any advice
would help...
-Thanks
Steve
Public Sub ECN_Status_Script()
Dim myServerName As String
Dim myDbName As String
Dim strSQL As String
Dim End_Row As Long
Dim i As Integer
Dim strTableNames() As Variant
Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset
strTableNames = Array("ArlManEcn", "Cum1ManEcn", "Cum2ManEcn",
"DanManEcn", "ECN", "PipManEcn", "ProdPlan", "ReyManEcn", "RosManEcn",
"SalManEcn", "SPWECN")
myServerName = "ARLNotes1/USMARINE"
myDbName = "ECNWorkf.nsf"
'Connect to a Database
Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = "DRIVER={Lotus NotesSQL Driver
(*.nsf)};SERVER=" & myServerName & ";DATABASE=" & myDbName
oConn.Open
'Create a recordset object
Set rs = CreateObject("ADODB.RecordSet")
rs.ActiveConnection = oConn
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockPessimistic
'Turn off filter if on.
If Application.Sheets("ECN Status").FilterMode = True Then
Application.ActiveSheet.ShowAllData
End If
End_Row = 4
Range("A4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Clear
DoEvents
For i = 0 To UBound(strTableNames)
strSQL = "SELECT ACProcessName, EcnNumber, Title, ACOriginator,
ACSubmittedDate, ACCurrentApprovers, ACAssignedDate_d " _
& "FROM " & strTableNames(i) & " WHERE (((" &
strTableNames(i) & ".ACStatus)='In Process'));"
rs.Open strSQL
Worksheets("ECN Status").Range("A" & End_Row).CopyFromRecordset rs
Range("A65536").Select
Selection.End(xlUp).Select
End_Row = ActiveCell.Row
rs.Close
Next
Set rs = Nothing
End Sub
error. What it does is extract data from a Lotus Notes database
(*.nsf), which I have no control over, and places the selected info
into an Excel file. Problem I'm having is that its truncating the
data it's grabbing - So the question, with this code, how do I
determine the field size and ensure I "collecting" all of the
information within that field? I'm a "good novice" - So any advice
would help...
-Thanks
Steve
Public Sub ECN_Status_Script()
Dim myServerName As String
Dim myDbName As String
Dim strSQL As String
Dim End_Row As Long
Dim i As Integer
Dim strTableNames() As Variant
Dim oConn As ADODB.Connection
Dim rs As ADODB.Recordset
strTableNames = Array("ArlManEcn", "Cum1ManEcn", "Cum2ManEcn",
"DanManEcn", "ECN", "PipManEcn", "ProdPlan", "ReyManEcn", "RosManEcn",
"SalManEcn", "SPWECN")
myServerName = "ARLNotes1/USMARINE"
myDbName = "ECNWorkf.nsf"
'Connect to a Database
Set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = "DRIVER={Lotus NotesSQL Driver
(*.nsf)};SERVER=" & myServerName & ";DATABASE=" & myDbName
oConn.Open
'Create a recordset object
Set rs = CreateObject("ADODB.RecordSet")
rs.ActiveConnection = oConn
rs.CursorLocation = adUseClient
rs.CursorType = adOpenDynamic
rs.LockType = adLockPessimistic
'Turn off filter if on.
If Application.Sheets("ECN Status").FilterMode = True Then
Application.ActiveSheet.ShowAllData
End If
End_Row = 4
Range("A4").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Clear
DoEvents
For i = 0 To UBound(strTableNames)
strSQL = "SELECT ACProcessName, EcnNumber, Title, ACOriginator,
ACSubmittedDate, ACCurrentApprovers, ACAssignedDate_d " _
& "FROM " & strTableNames(i) & " WHERE (((" &
strTableNames(i) & ".ACStatus)='In Process'));"
rs.Open strSQL
Worksheets("ECN Status").Range("A" & End_Row).CopyFromRecordset rs
Range("A65536").Select
Selection.End(xlUp).Select
End_Row = ActiveCell.Row
rs.Close
Next
Set rs = Nothing
End Sub