DCRAPACCESS
asked on
loop all calendar entries in lotus notes in vba
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?
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
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.
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(noD ocument)
and change the last line in the loop to this:
Set noDocument = noView.GetNextDocument(noD ocument)
Note that you don't need to save the "next document." Delete this line:
Set noNextDocument = noView.GetNextDocument(noD
and change the last line in the loop to this:
Set noDocument = noView.GetNextDocument(noD
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.
Use a debugger (which is what Thomas implicitly suggests) and check line by line what happens.
ASKER
Hi Ace, yes it almost works, this my day for tomorrow:
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
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
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.
Your loop may also grab meeting declines and reschedules, which you may not want.
ASKER
Hi ThomasMcA2,
The problem seems to be the repeating Appointments, that i'm not able to get :-( Single appointments works just fine.
The problem seems to be the repeating Appointments, that i'm not able to get :-( Single appointments works just fine.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
Hi all, I need a bit more help.
All my code works almost. Now i just need to loop through this:
noDocument.GetItemValue("C alendarDat eTime")(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
All my code works almost. Now i just need to loop through this:
noDocument.GetItemValue("C
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
ASKER
Got it:
vaItem = noDocument.GetItemValue("C alendarDat eTime")
x = LBound(vaItem)
y = UBound(vaItem)
vaItem = noDocument.GetItemValue("C
x = LBound(vaItem)
y = UBound(vaItem)
ASKER
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
ASKER
Ups! need one more thing:
"Do Until x = y" should be "Do Until x = y+1"
"Do Until x = y" should be "Do Until x = y+1"
ASKER
This message was the one i needed to make my solution.
Ok! :-)