Hi Stuk,
The following is C# code to read, write, delete events in Google
Calendar
Three files copied in plain text look for "NEW FILE"
<Flame Style="on">:
Why am I copying all my hard work ;-) ?? because the documentation
is useless, no complete examples, only fragments. No support for VBA/
VB6.
</Flame> :->
But I do like Google Calendar now I know how to use it. Using arrays
as I have done may not be the best way to pass to VBA/VB6 but it works
for the small data set that is passed.
Regards
Peter
NEW FILE C# CODE TO READ WRITE GOOGLE CALENDAR
using System;
using System.Collections;
using System.Net;
using System.Reflection; // ADD THIS.
using System.Runtime.InteropServices;
using Google.GData.Client;
using Google.GData.Extensions;
using Google.GData.Calendar;
namespace GoogleCalendar4VBA
{
// http://www.codeguru.com/Csharp/Csharp/cs_misc/com/article.php/c6747/
public delegate void StatusEventHandler();
public delegate void AbortEventHandler();
[Guid("aa1dcdf5-3ace-23de-a2a8-0013728b9ef9"),
InterfaceType(ComInterfaceType.InterfaceIsDual)]
public interface ReadCalendarInterface
{
[DispId(1)] int GetEvents(string sURI,
string sUserID,
string sPassword,
string sProxyIP,
string sStartDate,
string sEndDate,
ref string[,] comObject,
ref string sError);
[DispId(2)] int SetEvents(string sURI,
string sUserID,
string sPassword,
string sProxyIP,
ref string[,] comObject,
ref string sError);
[DispId(3)] string StatusProperty{ get; set; }
}
[Guid("aa1dcdf6-3ace-24de-a2a8-0013728b9ef9"),
InterfaceType(ComInterfaceType.InterfaceIsIDispatch),
ComVisible(true)]
public interface ReadCalendarEvents
{
//
[DispId(1)] void StatusEvent();
[DispId(2)] void AbortConnection();
}
// ComSourceInterfaces(typeof(ReadCalendarEvents)),
[Guid("aa1dcdf7-3ace-25de-a2a8-0013728b9ef9"),
ClassInterface(ClassInterfaceType.None), // required to see
event and method names
ComSourceInterfacesAttribute
("GoogleCalendar4VBA.ReadCalendarEvents"), // required to throw
events
ComVisible(true)]
public class ReadCalendar : ReadCalendarInterface
{
//private ArrayList entryList;
private string msStatusProperty = "";
public event StatusEventHandler StatusEvent;
public event AbortEventHandler AbortConnection;
const int mciEventIdentifyer = 0;
const int mciEventStartDate = 1;
const int mciEventStartTime = 2;
const int mciEventEndDate = 3;
const int mciEventEndTime = 4;
const int mciEventTitle = 5;
const int mciEventDescription = 6;
const int mciEventLocation = 7;
const int mciEventAuthor = 8;
const int mciEventAuthorEmail = 9;
const int mciEventVisible = 10;
const int mciEventUpdatedDateTime = 11;
const int mciEventOriginalID = 12;
const int mciEventRecursTot = 13;
const int mciEventDeleteThis = 14;
const string mcsNULLDate = "01/01/0001 00:00:00";
//
//public event Event1Handler Event1;
// A COM class must have a public parameterless
constructor.
// Otherwise the class might not be registered for COM
and
// cannot be created by CreateObject.
public ReadCalendar() : base()
{
#if DEBUG
string sName = System.Environment.UserName;
string sMachineName = System.Environment.MachineName;
System.Diagnostics.Debug.WriteLine(sName.ToLower().IndexOf
("px1504", 0));
if (sName.ToLower().IndexOf("px1504") >= 0 || sName.ToLower
().IndexOf("pmxan") >= 0)
{
if (!System.Diagnostics.Debugger.IsAttached)
System.Diagnostics.Debugger.Launch();
}
#endif
}
//
//
[ComVisible(true)]
public string StatusProperty
{
get { return msStatusProperty; }
set { msStatusProperty = value; }
}
[ComVisible(true)]
public int GetEvents(string sURI,
string sUserID,
string sPassword,
string sProxyIP,
string sStartDate,
string sEndDate,
ref string[,] sArrEvents,
ref string sError)
{
int iTotEvents=0;
int iMaxEvents = 0;
int iMaxProperties = 0;
int iTotTimes = 0;
msStatusProperty = "Connecting to Google Calendar " +
sURI;
if (StatusEvent != null) StatusEvent(); // equals null if
there are no event handlers from calling app
iMaxEvents = sArrEvents.GetUpperBound(0);
iMaxProperties = sArrEvents.GetUpperBound(1);
if (iMaxProperties < mciEventDeleteThis)
{
sError = "Array dimension is incorrect should be Array
(Rows," + mciEventDeleteThis + ") Where rows can be any number but 2nd
demension must be at least " + mciEventDeleteThis + " and not: " +
iMaxProperties;
System.Diagnostics.Debug.WriteLine(sError);
return 0;
}
//this.entryList = new ArrayList(50);
ArrayList dates = new ArrayList(50);
EventQuery query = new EventQuery();
CalendarService service = new CalendarService
("CalendarApp");
if (sUserID != null && sUserID.Length > 0 && sPassword !=
null && sPassword.Length > 0)
{
service.setUserCredentials(sUserID, sPassword);
}
else
{
sError = "One or both UserID or Password is blank";
System.Diagnostics.Debug.WriteLine(sError);
return 0;
}
//Default is unnamed private calendar for user
if (sURI.Length == 0)
{
sURI = "http://www.google.com/calendar/feeds/default/
private/full";
}
if (sURI.ToLower().Contains("@group") && !sURI.ToLower
().Contains("http://www.google.com/calendar/feeds/";))
{
query.Uri = new Uri("http://www.google.com/calendar/
feeds/" + sURI + "/public/full");
}
else
{
query.Uri = new Uri(sURI);
}
DateTime result;
if (sStartDate.Length > 0)
{
if (DateTime.TryParse(sStartDate, out result))
{
query.StartTime = Convert.ToDateTime(sStartDate);
}
else
{
sError = "Start date is not a date: " +
sStartDate;
System.Diagnostics.Debug.WriteLine(sError);
return 0;
}
}
else
{
query.StartTime = DateTime.Now.AddDays(-28);
}
if (sEndDate.Length > 0)
{
if (DateTime.TryParse(sEndDate, out result))
{
query.EndTime = Convert.ToDateTime(sEndDate);
}
else
{
sError = "End date is not a date: " + sEndDate;
System.Diagnostics.Debug.WriteLine(sError);
return 0;
}
}
else
{
query.EndTime = DateTime.Now.AddMonths(6);
}
if (sProxyIP.Length > 0)
{
GDataRequestFactory f = (GDataRequestFactory)
service.RequestFactory;
IWebProxy iProxy = WebRequest.DefaultWebProxy;
//iProxy.Credentials = this.ProxyIPTextBox.Text;
WebProxy myProxy = new WebProxy(iProxy.GetProxy
(query.Uri)); // potentially, setup credentials on the proxy
here
myProxy.Address = new System.Uri(sProxyIP);
myProxy.Credentials =
CredentialCache.DefaultCredentials;
myProxy.UseDefaultCredentials = true;
f.Proxy = myProxy;
}
EventFeed calFeed=null;
try
{
calFeed = service.Query(query) as EventFeed;
}
catch (Exception ex)
{
sError = "Failed in attempt to query service Using
URI: " + query.Uri + " Error: " + ex.Message;
System.Diagnostics.Debug.WriteLine(ex);
return 0;
} // now populate the calendar
msStatusProperty="Loading Google Calendar data (" +
calFeed.Entries.Count + ")";
if (StatusEvent != null) StatusEvent();
if (calFeed.Entries.Count == 0)
{
sError = "No Events found for Start date: " +
sStartDate + ", End date: " + sEndDate;
}
while (calFeed != null && calFeed.Entries.Count > 0)
{
foreach (EventEntry entry in calFeed.Entries)
{
//this.entryList.Add(entry);
if (entry.OriginalEvent != null)
{
System.Diagnostics.Debug.WriteLine
(entry.OriginalEvent.IdOriginal.ToString());
sArrEvents[iTotEvents, mciEventOriginalID] =
entry.OriginalEvent.IdOriginal.ToString();
sArrEvents[iTotEvents, mciEventRecursTot] =
entry.Times.Count.ToString();
}
else
{
System.Diagnostics.Debug.WriteLine("No
Original Event");
}
try
{
for (iTotTimes = 0; iTotTimes <
entry.Times.Count; iTotTimes++)
{
sArrEvents[iTotEvents, mciEventIdentifyer]
= entry.EventId;
sArrEvents[iTotEvents, mciEventTitle] =
entry.Title.Text;
if (entry.Content.Content != null)
sArrEvents[iTotEvents, mciEventDescription] = entry.Content.Content;
sArrEvents[iTotEvents, mciEventLocation] =
entry.Locations[0].ValueString;
sArrEvents[iTotEvents, mciEventAuthor] =
entry.Authors[0].Name;
sArrEvents[iTotEvents,
mciEventAuthorEmail] = entry.Authors[0].Email;
if (entry.EventVisibility != null)
sArrEvents[iTotEvents, mciEventVisible] = entry.EventVisibility.Value;
if (entry.Times.Count > 0)
{
sArrEvents[iTotEvents,
mciEventStartDate] = entry.Times[iTotTimes].StartTime.Date.ToString();
sArrEvents[iTotEvents,
mciEventStartTime] = entry.Times
[iTotTimes].StartTime.TimeOfDay.ToString();
sArrEvents[iTotEvents,
mciEventEndDate] = entry.Times[iTotTimes].EndTime.Date.ToString();
sArrEvents[iTotEvents,
mciEventEndTime] = entry.Times[iTotTimes].EndTime.TimeOfDay.ToString
();
}
sArrEvents[iTotEvents,
mciEventUpdatedDateTime] = entry.Updated.ToString();
iTotEvents++;
if (iTotEvents == iMaxEvents)
{
if (calFeed.Entries.Count >
iMaxEvents) sError = "More Events available but Array too small.";
break;
} }
}
catch (Exception ex)
{
sError = "To Get Event Entry Details: " +
query.Uri + " Error: " + ex.Message;
System.Diagnostics.Debug.WriteLine(ex);
return 0;
}
}
if (iTotEvents == iMaxEvents)
{
break;
}
// just query the same query again.
if (calFeed.NextChunk != null)
{
msStatusProperty = "Getting Next Chunk of Events
(" + iTotEvents + ")";
if (StatusEvent != null) StatusEvent();
if (msStatusProperty == "Abort")
{
if (AbortConnection != null) AbortConnection
();
break;
}
query.Uri = new Uri(calFeed.NextChunk);
calFeed = service.Query(query) as EventFeed;
}
else
calFeed = null;
}
if (msStatusProperty == "Abort")
{
msStatusProperty = "Connection Aborted after (" +
iTotEvents + ") events.";
}
else
{
msStatusProperty = "Complete (" + iTotEvents + ")";
}
if (StatusEvent != null) StatusEvent();
return iTotEvents;
}
[ComVisible(true)]
public int SetEvents(string sURI,
string sUserID,
string sPassword,
string sProxyIP,
ref string[,] sArrEvents,
ref string sError)
{
int iTotEvents = 0;
int iMaxEvents = 0;
int iMaxProperties = 0;
int i = 0;
string sStartDate = "";
string sEndDate = "";
DateTime result;
EventEntry entry;
msStatusProperty = "Connecting to Google Calendar " +
sURI;
if (StatusEvent != null) StatusEvent();
iMaxEvents = sArrEvents.GetUpperBound(0);
iMaxProperties = sArrEvents.GetUpperBound(1);
if (iMaxProperties < mciEventDeleteThis)
{
sError = "Array dimension is incorrect should be Array
(Rows," + iMaxProperties + ") Where rows can be any number but 2nd
demension must be at least " + mciEventDeleteThis + " and not: " +
iMaxProperties;
System.Diagnostics.Debug.WriteLine(sError);
return 0;
}
//this.entryList = new ArrayList(50);
ArrayList dates = new ArrayList(50);
EventQuery query = new EventQuery();
CalendarService service = new CalendarService
("CalendarApp");
if (sUserID != null && sUserID.Length > 0 && sPassword !=
null && sPassword.Length > 0)
{
service.setUserCredentials(sUserID, sPassword);
}
else
{
sError = "One or both UserID or Password is blank";
System.Diagnostics.Debug.WriteLine(sError);
return 0;
}
//Default is unnamed private calendar for user
if (sURI.Length == 0)
{
sURI = "http://www.google.com/calendar/feeds/default/
private/full";
}
// only get event's for today - 1 month until today + 1
year
if (sURI.ToLower().Contains("@group") && !sURI.ToLower
().Contains("http://www.google.com/calendar/feeds/";))
{
sURI = "http://www.google.com/calendar/feeds/"; + sURI
+ "/public/full";
}
query.Uri = new Uri(sURI);
//find the max and min dates in the array of calendar
information
for (i = 0; i < iMaxEvents; i++)
{
if (sArrEvents[i, mciEventTitle]==null || sArrEvents
[i, mciEventTitle].Length==0)
{
iMaxEvents = i;
break;
}
if (sArrEvents[i, mciEventStartTime]==null ||
sArrEvents[i, mciEventStartTime].Length == 0) sArrEvents[i,
mciEventStartTime] = "00:00:00";
if (sArrEvents[i, mciEventEndTime]==null || sArrEvents
[i, mciEventEndTime].Length == 0) sArrEvents[i, mciEventEndTime] =
"00:00:00";
if (sArrEvents[i, mciEventStartDate] == null)
sArrEvents[i, mciEventStartDate] =
System.DateTime.Now.ToShortDateString();
if (sArrEvents[i, mciEventEndDate] == null) sArrEvents
[i, mciEventEndDate] = sArrEvents[i, mciEventStartDate];
sArrEvents[i, mciEventStartDate]=sArrEvents[i,
mciEventStartDate].Replace("00:00:00", "");
sArrEvents[i, mciEventStartDate] = sArrEvents[i,
mciEventStartDate].Replace("0:00:00", "");
sArrEvents[i, mciEventStartDate] = sArrEvents[i,
mciEventStartDate].Replace("0:00", "");
sArrEvents[i, mciEventEndDate] = sArrEvents[i,
mciEventEndDate].Replace("00:00:00", "");
sArrEvents[i, mciEventEndDate] = sArrEvents[i,
mciEventEndDate].Replace("0:00:00", "");
sArrEvents[i, mciEventEndDate] = sArrEvents[i,
mciEventEndDate].Replace("0:00", "");
sStartDate = sArrEvents[i, mciEventStartDate];
sEndDate = sArrEvents[i, mciEventEndDate];
if (sArrEvents[i, mciEventVisible] !=
EventEntry.Visibility.CONFIDENTIAL_VALUE &&
sArrEvents[i, mciEventVisible] !=
EventEntry.Visibility.PRIVATE_VALUE &&
sArrEvents[i, mciEventVisible] !=
EventEntry.Visibility.PUBLIC_VALUE &&
sArrEvents[i, mciEventVisible] !=
EventEntry.Visibility.DEFAULT_VALUE)
{
sArrEvents[i, mciEventVisible] =
EventEntry.Visibility.DEFAULT_VALUE;
}
if (sStartDate.Length > 0)
{
if (DateTime.TryParse(sStartDate, out result))
{
if (query.StartTime == Convert.ToDateTime
(mcsNULLDate))
{
query.StartTime = Convert.ToDateTime
(sStartDate);
}
else
{
if (query.StartTime < Convert.ToDateTime
(sStartDate))
{
query.StartTime = Convert.ToDateTime
(sStartDate);
}
}
}
else
{
sError = "Start date is not a date: " +
sStartDate;
System.Diagnostics.Debug.WriteLine(sError);
return 0;
}
}
else
{
query.StartTime = DateTime.Now.AddDays(-28);
}
if (sEndDate.Length > 0)
{
if (DateTime.TryParse(sEndDate, out result))
{
if (query.EndTime == Convert.ToDateTime
(mcsNULLDate))
{
query.EndTime = Convert.ToDateTime
(sEndDate);
}
else
{
if (query.EndTime > Convert.ToDateTime
(sEndDate))
{
query.EndTime = Convert.ToDateTime
(sEndDate);
}
}
}
else
{
sError = "End date is not a date: " +
sEndDate;
System.Diagnostics.Debug.WriteLine(sError);
return 0;
}
}
else
{
query.EndTime = DateTime.Now.AddMonths(6);
}
}
if (sProxyIP.Length > 0)
{
GDataRequestFactory f = (GDataRequestFactory)
service.RequestFactory;
IWebProxy iProxy = WebRequest.DefaultWebProxy;
//iProxy.Credentials = this.ProxyIPTextBox.Text;
WebProxy myProxy = new WebProxy(iProxy.GetProxy
(query.Uri)); // potentially, setup credentials on the proxy
here
myProxy.Address = new System.Uri(sProxyIP);
myProxy.Credentials =
CredentialCache.DefaultCredentials;
myProxy.UseDefaultCredentials = true;
f.Proxy = myProxy;
}
EventFeed calFeed = null;
try
{
calFeed = service.Query(query) as EventFeed;
}
catch (Exception ex)
{
sError = "Failed in attempt to query service Using
URI: " + query.Uri + " Error: " + ex.Message;
System.Diagnostics.Debug.WriteLine(ex);
return 0;
} // now populate the calendar
msStatusProperty = "Updating Google Calendar data (" +
calFeed.Entries.Count + ")";
if (StatusEvent != null) StatusEvent();
for (i = 0; i < iMaxEvents; i++)
{
msStatusProperty = "Updating Events (" + i+1 + " of "
+ iMaxEvents + ")";
if (StatusEvent != null) StatusEvent();
if (msStatusProperty == "Abort")
{
if (AbortConnection != null) AbortConnection();
break;
}
if (sArrEvents[i, mciEventIdentifyer].Length == 0 &
sArrEvents[i, mciEventTitle].Length > 0)
{
entry = new EventEntry();
AtomPerson author = new AtomPerson
(AtomPersonType.Author);
author.Name = sArrEvents[i, mciEventAuthor];
author.Email = sArrEvents[i, mciEventAuthorEmail];
entry.Authors.Add(author);
entry.Title.Text = sArrEvents[i, mciEventTitle];
entry.Content.Content = sArrEvents[i,
mciEventDescription];
entry.Locations.Add(new Where("","",sArrEvents[i,
mciEventLocation]));
EventEntry.Visibility ev = new
EventEntry.Visibility(sArrEvents[i, mciEventVisible]);
entry.EventVisibility = ev;
When EventTime = new When();
if (sArrEvents[i, mciEventStartDate].Length > 0)
{
EventTime.StartTime = Convert.ToDateTime
(sArrEvents[i, mciEventStartDate] + " " + sArrEvents[i,
mciEventStartTime]);
}
if (sArrEvents[i, mciEventEndDate].Length > 0)
{
EventTime.EndTime = Convert.ToDateTime
(sArrEvents[i, mciEventEndDate] + " " + sArrEvents[i,
mciEventEndTime]);
}
entry.Times.Add(EventTime);
if (entry.Times[0].StartTime > entry.Times
[0].EndTime) entry.Times[0].EndTime = entry.Times[0].StartTime;
Uri postUri = new Uri(sURI);
// Send the request and receive the response:
try
{
EventEntry insertedEntry = service.Insert
(postUri, entry);
sArrEvents[i, mciEventIdentifyer] =
insertedEntry.EventId;
}
catch (Exception ex)
{
sError = "Failed in attempt to insert Calendar
Event Using URI: " + query.Uri + " Error: " + ex.Message;
System.Diagnostics.Debug.WriteLine(ex);
return 0;
}
}
else if (sArrEvents[i, mciEventIdentifyer].Length>0)
{
if (sArrEvents[i, mciEventDeleteThis] == "True" |
sArrEvents[i, mciEventDeleteThis] == "1")
{
try
{
EventQuery DeleteQuery = new EventQuery();
DeleteQuery.Uri = new Uri(sURI + "/" +
sArrEvents[i, mciEventIdentifyer]);
EventFeed atomFeed = service.Query
(DeleteQuery);
entry = (EventEntry)atomFeed.Entries[0];
entry.Delete();
}
catch (Exception ex)
{
sError = "Failed in attempt to delete
Calendar Event Using URI: " + query.Uri + " CalEntry(" + sArrEvents[i,
mciEventIdentifyer] + ") Error: " + ex.Message;
System.Diagnostics.Debug.WriteLine(ex);
return 0;
}
}
else
{
try
{
EventQuery updateQuery = new EventQuery();
updateQuery.Uri = new Uri(sURI + "/" +
sArrEvents[i, mciEventIdentifyer]);
EventFeed atomFeed = service.Query
(updateQuery);
entry = (EventEntry)atomFeed.Entries[0];
}
catch (Exception ex)
{
sError = "Failed in attempt to read (prior
to update) Calendar Event Using URI: " + query.Uri + " CalEntry(" +
sArrEvents[i, mciEventIdentifyer] + ") Error: " + ex.Message;
System.Diagnostics.Debug.WriteLine(ex);
return 0;
}
entry.Title.Text = sArrEvents[i,
mciEventTitle];
if (sArrEvents[i, mciEventDescription].Length
> 0) entry.Content.Content = sArrEvents[i, mciEventDescription];
if (sArrEvents[i, mciEventLocation] != null &&
sArrEvents[i, mciEventLocation].Length>0) entry.Locations
[0].ValueString = sArrEvents[i, mciEventLocation];
if (sArrEvents[i, mciEventAuthor] != null &&
sArrEvents[i, mciEventAuthor].Length>0) entry.Authors[0].Name =
sArrEvents[i, mciEventAuthor];
if (entry.EventVisibility == null ||
entry.EventVisibility.Value != sArrEvents[i, mciEventVisible])
{
EventEntry.Visibility evu = new
EventEntry.Visibility(sArrEvents[i, mciEventVisible]);
entry.EventVisibility = evu;
}
if (sArrEvents[i, mciEventStartDate].Length >
0)
{
entry.Times[0].StartTime =
Convert.ToDateTime(sArrEvents[i, mciEventStartDate] + " " + sArrEvents
[i, mciEventStartTime]);
}
if (sArrEvents[i, mciEventEndDate].Length > 0)
{
entry.Times[0].EndTime = Convert.ToDateTime
(sArrEvents[i, mciEventEndDate] + " " + sArrEvents[i,
mciEventEndTime]);
}
if (entry.Times[0].StartTime > entry.Times
[0].EndTime) entry.Times[0].EndTime = entry.Times[0].StartTime;
try
{
service.Update(entry);
}
catch (Exception ex)
{
sError = "Failed in attempt to update
Calendar Event Using URI: " + query.Uri + " CalEntry(" + sArrEvents[i,
mciEventIdentifyer] + ") Error: " + ex.Message;
System.Diagnostics.Debug.WriteLine(ex);
return 0;
}
}
}
}
if (msStatusProperty == "Abort")
{
msStatusProperty = "Connection Aborted after updating
(" + iTotEvents + 1 + ") events.";
}
else
{
msStatusProperty = "Complete (" + iTotEvents + ")";
}
if (StatusEvent != null) StatusEvent();
return i;
}
}
}
NEW FILE COM HOW TO FILE
//===============================================
Build Dll
Then Re-Create tlb file
insert GUIDS using uuidgen tool to create GUID
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\RegAsm.exe C:\Dev
\GoogleCalendar4VBA\GoogleCalendar4VBA\bin\Debug
\GoogleCalendar4VBA.dll /tlb:C:\Dev\GoogleCalendar4VBA
\GoogleCalendar4VBA\bin\Debug\GoogleCalendar4VBA.tlb
Call it DOH or it will crash :-)
Sub x()
Dim c As New GoogleCalendar4VBA.ReadCalendar
c.GetEvents "c", "t", "x", "x", "x"
End Sub
Debugging:
1:
You can just attach to the native application and see breakpoint, view
stacks, watches etc. normally. You'll need to attach after the COM
object is created.
I put a Afx MsgBox when the object is created to stop the
application's flow and then attach the debugger.
2:
#if DEBUG
if (!System.Diagnostics.Debugger.IsAttached)
Debugger.Launch();
#endif
int numEvents=LoadComObjectIntoStringArray(ref
sArrEvents, ref sError);
return numEvents;
private int LoadComObjectIntoStringArray(ref string[]
sArrEvents, ref string sError)
{
int numEvents=0;
Type thisType = sArrEvents.GetType();
Type strType = Type.GetType("System.String[*]");
int numEntries = sArrEvents.Length;
for (int j1 = 0; j1 < numEntries; j1++)
{
try
{
sArrEvents[j1] = Convert.ToString(j1 + 1);
numEvents=10;
}
catch (Exception ex)
{
sError = ex.Message;
System.Diagnostics.Debug.WriteLine(ex);
break;
}
}
return numEvents;
} // End LoadComObjectIntoStringArray()
private double[] LoadComObjectIntoDoubleArray(object comObject)
{
Type thisType = comObject.GetType();
Type dblType = Type.GetType("System.Double[*]");
double[] doubleArray = new double[1]; // temporary
allocation to
keep compiler happy.
if(thisType == dblType)
{
object[] args = new object[1];
int numEntries =
(int)thisType.InvokeMember("Length",
BindingFlags.GetProperty, null, comObject, null);
doubleArray = new double[numEntries];
for(int j1=0; j1 < numEntries; j1++)
{
args[0] = j1+1; // since VB arrays
index from 1
doubleArray[j1] =
(double)thisType.InvokeMember("GetValue",
BindingFlags.InvokeMethod, null, comObject, args);
}
} // End if(thisType == dblType)
return doubleArray;
} // End LoadComObjectIntoDoubleArray()
To create COM InterOP
1>Open AssemblyInfo.cs file in VS 2005
Set Com Visible true
[assembly: ComVisible(true)]
2>Go to Poject Properties -> Build
Check the option
Register For Com InterOp to 'selected'
3>Go to class file.
Add namespace
using System.Runtime.InteropServices
[Note: No need to add reference of InteropServices dll in
Application references]
For Interface
3.1)Define GUID as
[Guid("EC87B398-B775-4e6f-BE2C-997D4594CFAA")]
[InterfaceType(ComInterfaceType.InterfaceIsDispatch)]
//Note : to create guid goto tools -> create guid -> set guid
format to registry format
//copy guid and add into your code using above syntax
3.2)Write Interface Methods as
[DispId(1)] int PerformAddition(int a, int b);
[DispId(2)] int PerformDeletion(int a, int b);
For class
3.3> Add statements above class
[Guid("5674D47E-6B2A-456e-85C4-CB7AA6AIF24A")]
[ClassInterface(ClassInterfaceType.None)]
[ProgId("ComInterOpClass")]
4>register assembly using sdk command prompt of vs 2005
(goto release folder of your project)
regasm LWIISO_DotNet.dll /tlb: LWIISO_DotNet.tlb
refer this tlb in vb
For late binding u can use it:
If strong key is not assigned the assembly will be private..so copy
the assembly in folder where u wanna use it.
For making assembly public assign strong key to assembly using 'sn'
tool of VS 2005
Or goto Project properties->Signing->Sign the Assembly->Choose strong
name key->Select New
This creates the *.snk file
=============================================================
Problems!!!
If you rename or delete a function/property/event, you will need to
recompile the calling excel VBA routine as it appears to save
function names even if they are not used in the VBA routine
May also be a prob for adding new functions/properties, but do the
same as above
NEW FILE VBA CODE or VB6 CODE
//=================================================================
Option Explicit
Const mciEventIdentifyer As Integer = 0
Const mciEventStartDate As Integer = 1
Const mciEventStartTime As Integer = 2
Const mciEventEndDate As Integer = 3
Const mciEventEndTime As Integer = 4
Const mciEventTitle As Integer = 5
Const mciEventDescription As Integer = 6
Const mciEventLocation As Integer = 7
Const mciEventAuthor As Integer = 8
Const mciEventAuthorEmail As Integer = 9
Const mciEventTransparency As Integer = 10
Const mciEventUpdatedDateTime As Integer = 11
Const mciEventOriginalID As Integer = 12
Const mciEventrecursCnt As Integer = 13
Const mciEventDelete As Integer = 14
Public WithEvents m_oComInterop As GoogleCalendar4VBA.ReadCalendar
'Public m_oComInterop As Object
Public Sub GetGoogleCalendarData()
On Error GoTo errorroutine
Dim sArr(200, mciEventDelete) As String
Dim sError As String
Dim iEvents As Integer
Dim i As Integer
Dim x As Integer
Dim iLastRow As Integer
iLastRow = LastRowUsed
ActiveSheet.Cells.ClearContents
Set m_oComInterop = New GoogleCalendar4VBA.ReadCalendar
'"[email protected]"
'iEvents = m_oComInterop.GetEvents("",
' "7/Aug/2009", _
' "9/Aug/2009",
' CStr(DateAdd("d", -3, Now)), _
CStr(DateAdd("d", 370, Now)),
'iEvents = m_oComInterop.GetEvents("",
iEvents = m_oComInterop.GetEvents
("[email protected]", _
"pmxman", _
"Candle13", _
"http://195.99.232.555:8080";, _ ' only
required if you are using a proxy, otherwise leave blank
CStr(DateAdd("d", -8, Now)), _
CStr(DateAdd("d", 300, Now)), _
sArr, _
sError)
Cells(1, 1) = "Identifyer"
Cells(1, 2) = "StartDate"
Cells(1, 3) = "StartTime"
Cells(1, 4) = "EndDate"
Cells(1, 5) = "EndTime"
Cells(1, 6) = "Title"
Cells(1, 7) = "Description"
Cells(1, 8) = "Location"
Cells(1, 9) = "Author"
Cells(1, 10) = "Author Email"
Cells(1, 11) = "Visibility"
Cells(1, 12) = "Update Date"
Cells(1, 13) = "Original EventID"
Cells(1, 14) = "Recurs Count"
Cells(1, 15) = "Delete This"
If iEvents = 0 Then
MsgBox "Error retrieving calendar information: " & sError
Else
SortDateArray sArr, mciEventStartDate
For i = 0 To iEvents - 1
For x = 0 To mciEventDelete - 1
If IsDate(sArr(i, x)) Then
Cells(i + 2, x + 1) = CDate(sArr(i, x))
Else
Cells(i + 2, x + 1) = sArr(i, x)
End If
Next x
Cells(i + 2, x + 1).RowHeight = 20
If i < iEvents - 1 Then
If sArr(i, mciEventDelete) <> "1" And Len(sArr(i,
mciEventStartDate)) > 0 Then
If CDate(Format(sArr(i, mciEventStartDate), "dd/mmm/
yyyy")) = CDate(Format(sArr(i + 1, mciEventStartDate), "dd/mmm/yyyy"))
Then
If (Len(sArr(i, mciEventOriginalID)) > 0 And sArr(i,
mciEventOriginalID) = sArr(i + 1, mciEventIdentifyer)) Then
Cells(i + 3, mciEventDelete + 1) = "1" ' assume
next event is the original so keep current version
ElseIf (Len(sArr(i + 1, mciEventOriginalID)) > 0 And
sArr(i + 1, mciEventOriginalID) = sArr(i, mciEventOriginalID)) Then
Cells(i + 2, mciEventDelete + 1) = "1" ' assume
current event is the original so keep next version
ElseIf CDate(Format(sArr(i, mciEventUpdatedDateTime),
"dd/mmm/yyyy")) > CDate(Format(sArr(i + 1, mciEventUpdatedDateTime),
"dd/mmm/yyyy")) Then
Cells(i + 3, mciEventDelete + 1) = "1" ' current
event update date is newer so keep current
ElseIf CDate(Format(sArr(i, mciEventUpdatedDateTime),
"dd/mmm/yyyy")) < CDate(Format(sArr(i + 1, mciEventUpdatedDateTime),
"dd/mmm/yyyy")) Then
Cells(i + 2, mciEventDelete + 1) = "1" ' Next
event update date is newer so keep next event
ElseIf CDate(Format(sArr(i, mciEventUpdatedDateTime),
"dd/mmm/yyyy")) = CDate(Format(sArr(i + 1, mciEventUpdatedDateTime),
"dd/mmm/yyyy")) Then
If Len(sArr(i, mciEventOriginalID)) > 0 Then
Cells(i + 3, mciEventDelete + 1) = "1" '
Current event is a copy so is possibly the newest, delete next event
ElseIf Len(sArr(i + 1, mciEventOriginalID)) > 0
Then
Cells(i + 2, mciEventDelete + 1) = "1" '
Otherwise keep current
ElseIf Len(sArr(i, mciEventrecursCnt)) > 0 Then
Cells(i + 2, mciEventDelete + 1) = "1" ' keep
next
ElseIf Len(sArr(i + 1, mciEventrecursCnt)) > 0
Then
Cells(i + 3, mciEventDelete + 1) = "1" ' keep
current
Else
' keep both
End If
End If
End If
End If
End If
Next i
End If
errorroutine:
Set m_oComInterop = Nothing
If Err.Number <> 0 Then
Debug.Print " Error in GetGoogleCalendarData " & Err.Description &
" " & Err.Number
End If
Exit Sub
Resume
End Sub
Public Function LastRowUsed(Optional sSheetname As String) As Integer
On Error GoTo errorroutine
If Len(sSheetname) = 0 Then
LastRowUsed = ActiveSheet.UsedRange.Rows.Count
Else
LastRowUsed = Sheets(sSheetname).UsedRange.Rows.Count
End If
errorroutine:
If Err.Number <> 0 Then
Debug.Print "Error in LastRowUsed using sheetname (" & sSheetname &
") " & Err.Description
End If
End Function
Private Sub m_oComInterop_AbortConnection()
Debug.Print "Abort Connection recieved"
End Sub
Private Sub m_oComInterop_StatusEvent()
'Static si As Integer
'si = si + 1
'If si = 4 Then
' m_oComInterop.StatusProperty = "Abort"
' Debug.Print "Abort Requested"
'Else
Debug.Print m_oComInterop.StatusProperty
'End If
End Sub
Public Sub SetGoogleCalendarData()
On Error GoTo errorroutine
Dim sArr(200, mciEventDelete) As String
Dim sError As String
Dim iEvents As Integer
Dim i As Integer
Dim iTotEvents As Integer
Dim x As Integer
Dim iLastRow As Integer
iLastRow = LastRowUsed
Set m_oComInterop = CreateObject("GoogleCalendar4VBA.ReadCalendar")
For i = 2 To iLastRow
If Len(Cells(i, mciEventTitle)) > 0 Then
For x = 1 To mciEventDelete + 1
sArr(iTotEvents, x - 1) = Cells(i, x).Text
Next x
sArr(iTotEvents, mciEventEndDate) = Format(sArr(iTotEvents,
mciEventEndDate), "dd/mmm/yyyy")
sArr(iTotEvents, mciEventStartDate) = Format(sArr(iTotEvents,
mciEventStartDate), "dd/mmm/yyyy")
sArr(iTotEvents, mciEventEndTime) = Format(Cells(i,
mciEventEndTime + 1).Text, "hh:mm:ss")
sArr(iTotEvents, mciEventStartTime) = Format(Cells(i,
mciEventStartTime + 1).Text, "hh:mm:ss")
iTotEvents = iTotEvents + 1
End If
Next i
'"[email protected]"
iEvents = m_oComInterop.SetEvents("", _
"pmxman", _
"Candle13", _
"http://199.132.552.121:8080";, _ ' only
required if you are using a proxy, otherwise leave blank
sArr, _
sError)
If iEvents = 0 Then
MsgBox "Error updating calendar information: " & sError
Else
For i = 0 To iEvents - 1
If sArr(i, mciEventDelete) = "1" Or sArr(i, mciEventDelete) =
"True" Then
Rows(i + 2 & ":" & i + 2).ClearContents
ElseIf Len(Cells(i + 2, 1)) = 0 Then
Cells(i + 2, 1) = sArr(i, mciEventIdentifyer)
End If
Next i
End If
errorroutine:
Set m_oComInterop = Nothing
If Err.Number <> 0 Then
Debug.Print " Error in GetGoogleCalendarData " & Err.Description &
" " & Err.Number
End If
Exit Sub
Resume
End Sub
Private Sub SortDateArray(arr() As String, iDatePos As Integer, _
Optional ByVal bSortDesc As Boolean = False)
On Error GoTo errorroutine
' simple bubble sort
Dim sDate As String
Dim index As Long
Dim firstItem As Long
Dim indexLimit As Long
Dim lastSwap As Long
Dim iUbound2 As Integer
Dim i As Integer
Dim sArrSaved() As String
firstItem = LBound(arr)
lastSwap = UBound(arr)
iUbound2 = UBound(arr, 2)
ReDim sArrSaved(iUbound2)
Do
indexLimit = lastSwap - 1
lastSwap = 0
For index = firstItem To indexLimit
sDate = arr(index, iDatePos)
If Not IsDate(sDate) Or Not IsDate(arr(index + 1,
iDatePos)) Then
If IsDate(arr(index + 1, iDatePos)) Then
For i = 0 To iUbound2
sArrSaved(i) = arr(index, i)
Next i
For i = 0 To iUbound2
arr(index, i) = arr(index + 1, i)
Next i
For i = 0 To iUbound2
arr(index + 1, i) = sArrSaved(i)
Next i
lastSwap = index
End If
ElseIf (CDate(sDate) > CDate(arr(index + 1, iDatePos)))
Xor bSortDesc Then
' if the items are not in order, swap them
For i = 0 To iUbound2
sArrSaved(i) = arr(index, i)
Next i
For i = 0 To iUbound2
arr(index, i) = arr(index + 1, i)
Next i
For i = 0 To iUbound2
arr(index + 1, i) = sArrSaved(i)
Next i
lastSwap = index
End If
Next
Loop While lastSwap
errorroutine:
If Err.Number <> 0 Then
Debug.Print "Error in SortDateArray: " & Err.Description
End If
Exit Sub
Resume
End Sub
On Jun 16, 1:27 am, Stuk <[email protected]> wrote:
> I, i have create a httpsendrequest with google and i have my auth, now
> i want to add new event but i don't understand how can i do this in
> visual basic...Anybody can help me?
> Thanks
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Google Calendar Data API" group.
To post to this group, send email to
[email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/google-calendar-help-dataapi?hl=en
-~----------~----~----~----~------~----~------~--~---
