kondkarimoshin
New Member
- Joined
- Oct 8, 2013
- Messages
- 4
Search syntax in vba for lotus notes documents in view. Urgent
Need help with vba code. please have look below on the code. thanks
Private Sub CommandButton1_Click()
Dim nSess As Object 'NotesSession
Dim sPwd As String
Dim strCnxn As String
Dim strSQL As String
Dim db As Object
Dim iviews As Object
Dim IView As Object
Set nSess = CreateObject("Lotus.NotesSession") 'New:{29131539-2EED-1069-BF5D-00DD011186B7}
myUsername = ****
myPassword = ****
' Open connection
DSN1 = ("Driver={Lotus NotesSQL Driver (*.nsf)};Server=;Database=;Uid=" & myUsername & ";Pwd=" & myPassword & ";")
Call nSess.Initialize(sPwd)
Set db = nSess.GetDatabase("", "")
Set iviews = db.GetView("QA\QA Schedule")
iviews.AutoUpdate = False ' for greater efficiency.
'Here i want all the document in this view which are in between (1sep2013 - 30sep2013) here its not the document created date it the date which is taken form the column of the view so that i can pull the data in Excel
'below is code which i have build what its doping it it is searching all the document and getting me the data. which is taking lot of time.if can add a filter on the date we can do it little bit faster
Set IView = iviews.AllEntries
'Call IView.FTSearch("AQAExecDate >= 09/01/2013 and AQAExecDate <= 09/30/2013",0)
'Set coll = NotesNewsletter(IView)
Set viewparentEntry = IView.Parent
'colnam = viewparentEntry.Columns()
Set viewEntry = viewparentEntry.GetFirstDocument
For i = 1 To IView.Count
'Set viewEntry = viewparentEntry.GetFirstDocument
Colval = viewEntry.ColumnValues()
For j = 0 To 20
If Colval(0) <> "2013 9" Then
Exit For
ElseIf Colval(18) >= "" Or Colval(18) <= "" Then
Exit For
ElseIf Colval(18) >= "09/01/2013" Or Colval(18) <= "09/30/2013" Then
Sheets("Sheet2").Cells(RowCount, colcount).Value = Colval(j)
colcount = colcount + 1
Else
Exit For
End If
Next
j = 0
colcount = 1
RowCount = RowCount + 1
Set viewEntry = viewparentEntry.GetNextDocument(viewEntry)
Next
End Sub
Need help with vba code. please have look below on the code. thanks
Private Sub CommandButton1_Click()
Dim nSess As Object 'NotesSession
Dim sPwd As String
Dim strCnxn As String
Dim strSQL As String
Dim db As Object
Dim iviews As Object
Dim IView As Object
Set nSess = CreateObject("Lotus.NotesSession") 'New:{29131539-2EED-1069-BF5D-00DD011186B7}
myUsername = ****
myPassword = ****
' Open connection
DSN1 = ("Driver={Lotus NotesSQL Driver (*.nsf)};Server=;Database=;Uid=" & myUsername & ";Pwd=" & myPassword & ";")
Call nSess.Initialize(sPwd)
Set db = nSess.GetDatabase("", "")
Set iviews = db.GetView("QA\QA Schedule")
iviews.AutoUpdate = False ' for greater efficiency.
'Here i want all the document in this view which are in between (1sep2013 - 30sep2013) here its not the document created date it the date which is taken form the column of the view so that i can pull the data in Excel
'below is code which i have build what its doping it it is searching all the document and getting me the data. which is taking lot of time.if can add a filter on the date we can do it little bit faster
Set IView = iviews.AllEntries
'Call IView.FTSearch("AQAExecDate >= 09/01/2013 and AQAExecDate <= 09/30/2013",0)
'Set coll = NotesNewsletter(IView)
Set viewparentEntry = IView.Parent
'colnam = viewparentEntry.Columns()
Set viewEntry = viewparentEntry.GetFirstDocument
For i = 1 To IView.Count
'Set viewEntry = viewparentEntry.GetFirstDocument
Colval = viewEntry.ColumnValues()
For j = 0 To 20
If Colval(0) <> "2013 9" Then
Exit For
ElseIf Colval(18) >= "" Or Colval(18) <= "" Then
Exit For
ElseIf Colval(18) >= "09/01/2013" Or Colval(18) <= "09/30/2013" Then
Sheets("Sheet2").Cells(RowCount, colcount).Value = Colval(j)
colcount = colcount + 1
Else
Exit For
End If
Next
j = 0
colcount = 1
RowCount = RowCount + 1
Set viewEntry = viewparentEntry.GetNextDocument(viewEntry)
Next
End Sub