Solved

loop all calendar entries in lotus notes in vba

Posted on 2014-01-15
14
1,126 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

708 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

13 Experts available now in Live!

Get 1:1 Help Now