Solved

loop all calendar entries in lotus notes in vba

Posted on 2014-01-15
14
1,144 Views
Last Modified: 2014-01-16
Hi Experts,

I have this code, that loop calendar entries from current date and the next 14 days.

But the problem is that it only reads 1 entry from each date, not all?

Who can i be sure that it will loop all from each day?

Function lotus_NOTES_A_TIMER()
Dim noSession
Dim noDatabase
Dim noView
Dim noDocument
Dim noNextDocument
Dim Today
Dim text
Dim suffix
Dim vaItem
Dim vaAttachment
Dim i As Long
Dim inti As Integer
Dim E_date As Date
Dim str_start As String
Dim str_slut As String

Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GetDatabase("theServer", "mail\myname.nsf")
Set noView = noDatabase.GetView("Calendar")
Set noDocument = noView.GetFirstDocument
Do Until noDocument Is Nothing
    Set noNextDocument = noView.GetNextDocument(noDocument)
    E_date = CDate(Left(noDocument.GetItemValue("EndDate")(0), 10))
    If E_date >= Date And E_date <= Date + 15 Then
        Debug.Print noDocument.GetItemValue("TOPIC")(0)
        str_start = noDocument.GetItemValue("StartTime")(0)
        str_slut = noDocument.GetItemValue("EndTime")(0)
        Debug.Print E_date & ": " & 450 - (CDate(Left(str_slut, 2) * 60 + Right(Left(str_slut, 5), 2)) - CDate(Left(str_start, 2) * 60 + Right(Left(str_start, 5), 2)))
    End If
    Set noDocument = noNextDocument
Loop
Set noNextDocument = Nothing
Set noDocument = Nothing
Set noView = Nothing
Set noDatabase = Nothing
Set noSession = Nothing

End Function

Open in new window

0
Comment
Question by:DCRAPACCESS
  • 8
  • 3
  • 2
  • +1
14 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 39782806
I don't know the LotusNotes object model and have no way of checking but I would guess that you will need a loop inside the date loop.  The loop you have moves through the days of the calendar.  The inner loop will move through the items for the day.
0
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 39782936
I tested the code on my server, and it correctly stepped through every calendar document.

Note that you don't need to save the "next document."  Delete this line:
Set noNextDocument = noView.GetNextDocument(noDocument)

and change the last line in the loop to this:
Set noDocument = noView.GetNextDocument(noDocument)
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 39782992
Where it starts going wrong (I think) is on lines 24, 27, 28 and 29, where you assume that date fields contain strings, which in fact they don't.

Use a debugger (which is what Thomas implicitly suggests) and check line by line what happens.
0
 

Author Comment

by:DCRAPACCESS
ID: 39783282
Hi Ace, yes it almost works, this my day for tomorrow:
Calendar Sample
If i run my code in Access and print it, i get this, look at the date "16-01-2014", i only get one of the 4 meetings i have that day?:

Intromøde vedr. database til manuelle indtastningsark samt procestrinindex i DSPF - 15-01-2014: 374
e-bolighandel Møde Service Driftstyring - 16-01-2014: 400
Strategidag i P&P - 17-01-2014: -22
Morgenmøde  - 24-01-2014: 408
Morgenmøde  - 24-01-2014: 408
Morgenmøde  - 24-01-2014: 408
Morgenmøde  - 24-01-2014: 408
Morgenmøde  - 24-01-2014: 408
Morgenmøde  - 24-01-2014: 408
0
 
LVL 14

Expert Comment

by:ThomasMcA2
ID: 39783474
Open your calendar, then click on the All Calendar Entries link. Those are the documents that your code will step through.

Your loop may also grab meeting declines and reschedules, which you may not want.
0
 

Author Comment

by:DCRAPACCESS
ID: 39784583
Hi ThomasMcA2,

The problem seems to be the repeating Appointments, that i'm not able to get :-( Single appointments works just fine.
0
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 500 total points
ID: 39784950
You have to repeat repeating appointments yourself. In Notes it is just one document (actually 2, the parent and a response document, and you have to use only the response document) in which some date fields have multiple values, for multiple start and end times of the same appointment.

The other possibility is to use a NotesViewNavigator, in which case you walk through the view element by element, and repeated appointments will show up.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:DCRAPACCESS
ID: 39785019
Hi Sjef Bosman,

I get what you are saying. I just hoped that I cloud get around that part :-)

Thanks for the information, I did not know that Notes was handling it that way.
0
 

Author Comment

by:DCRAPACCESS
ID: 39785167
Hi all, I need a bit more help.

All my code works almost. Now i just need to loop through this:
noDocument.GetItemValue("CalendarDateTime")(0)

The "(0)" gives me the first record of the Range list and "(1)" gives me the next on. But the problem is that i don't know how long the range is :-(

And my code ignores my "On Error GoTo err", and then give me a error message
0
 

Author Comment

by:DCRAPACCESS
ID: 39785180
Got it:
vaItem = noDocument.GetItemValue("CalendarDateTime")
x = LBound(vaItem)
y = UBound(vaItem)
0
 

Author Comment

by:DCRAPACCESS
ID: 39785184
Total solution:
Dim noSession
Dim noDatabase
Dim noView
Dim noDocument
Dim noNextDocument
Dim Today
Dim text
Dim suffix
Dim vaItem
Dim vaAttachment
Dim i As Long
Dim inti As Integer
Dim str_dates() As String
Dim str_datest As String
Dim E_date As Date
Dim str_start As String
Dim str_slut As String
Dim lng_1 As Long
Dim lng_2 As Long
Dim x As Integer
Dim y As Integer
CurrentDb.Execute "DELETE * FROM tbl_MYTIME"
Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GetDatabase("SERVERNAME", "mail\XXXXX.nsf")
Set noView = noDatabase.GetView("($Calendar)")
Set noDocument = noView.GetFirstDocument

Do Until noDocument Is Nothing
    Set noNextDocument = noView.GetNextDocument(noDocument)
    vaItem = noDocument.GetItemValue("CalendarDateTime")
    x = LBound(vaItem)
    y = UBound(vaItem)
    Do Until x = y
        E_date = CDate(Left(noDocument.GetItemValue("CalendarDateTime")(x), 10))
        If E_date >= Date And E_date < Date + 15 Then
            str_start = Left(Right(noDocument.GetItemValue("StartTime")(0), 8), 5)
            str_slut = Left(Right(noDocument.GetItemValue("EndTime")(0), 8), 5)
            If Len(str_start) > 0 And Len(str_slut) > 0 Then
                If IsNull(DLookup("DATO", "tbl_MYTIME", "DATO=#" & sql_date(CStr(E_date)) & "# AND Subject=" & Chr(34) & Replace(noDocument.GetItemValue("Subject")(0), Chr(34), Chr(39)) & Chr(34))) And E_date >= Date And E_date < Date + 15 Then
                    lng_1 = CDate(CLng(Left(str_slut, 2) * 60) + CLng(Right(str_slut, 2)))
                    lng_2 = CDate(CLng(Left(str_start, 2) * 60) + CLng(Left(str_start, 2)))
                    CurrentDb.Execute "INSERT INTO tbl_MYTIME(DATO,Used_time,Subject) VALUES(#" & sql_date(CStr(E_date)) & "#," & (lng_1 - lng_2) & "," & Chr(34) & Replace(noDocument.GetItemValue("Subject")(0), Chr(34), Chr(39)) & Chr(34) & ")"
                End If
            End If
        End If
    x = x + 1
    Loop
nextI:
    Set noDocument = noNextDocument
Loop
Set noNextDocument = Nothing
Set noDocument = Nothing
Set noView = Nothing
Set noDatabase = Nothing
Set noSession = Nothing

Exit Function
err:
Resume Next

Open in new window

0
 

Author Comment

by:DCRAPACCESS
ID: 39785194
Ups! need one more thing:

"Do Until x = y" should be "Do Until x = y+1"
0
 

Author Closing Comment

by:DCRAPACCESS
ID: 39785196
This message was the one i needed to make my solution.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 39785317
Ok! :-)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now