Solved

loop all calendar entries in lotus notes in vba

Posted on 2014-01-15
14
1,198 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 3
  • 2
  • +1
14 Comments
 
LVL 38

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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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
 

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

624 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