Solved

loop all calendar entries in lotus notes in vba

Posted on 2014-01-15
14
1,153 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 35

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Ms Access VBA Variables 6 26
object variable or with block not set 6 28
sql statement - 9 24
Delete QueryDef IF it Exists: Access VBA 5 34
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

776 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