VBA to update google calendar

p4nny

Board Regular
Joined
Jan 13, 2015
Messages
246
Hi

I have an excel userform with several functions..

What I would like is, when a user selects a date and a time, it would then automatically update Google Calendar. Is this possible at all?

Appreciate the help

Cheers
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Perpa

Well-known Member
Joined
Dec 18, 2012
Messages
635
Hi

I have an excel userform with several functions..

What I would like is, when a user selects a date and a time, it would then automatically update Google Calendar. Is this possible at all?

Appreciate the help

Cheers

P4nny,
'Automatic' this is not...So unless someone else on this forum has an 'automatic update' solution...But here is something
I did for a vetinarian friend to put the doctor's schedules on the company's Google calendar.

You can place the data from your userform into a separate excel sheet (using a macro) and compile a list similar to this in Excel:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-bottom: 1px solid black;;">Subject</td><td style="border-bottom: 1px solid black;;">Start Date</td><td style="border-bottom: 1px solid black;;">Start Time</td><td style="border-bottom: 1px solid black;;">End Time</td><td style="border-bottom: 1px solid black;;">End Date</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">JA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">12/30/2013</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">7:00 AM</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">7:00 PM</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">12/30/2013</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">KW</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">12/30/2013</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">7:00 PM</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">7:00 AM</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">12/31/2013</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">Intern</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">12/30/2013</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">2:00 PM</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">10:00 PM</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #DBEEF3;;">12/30/2013</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">JA</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12/31/2013</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7:00 AM</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7:00 PM</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12/31/2013</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">LBW</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12/31/2013</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7:00 PM</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7:00 AM</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1/1/2014</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">JEB</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12/31/2013</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2:00 PM</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10:00 PM</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12/31/2013</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br />

Then you periodically save the workheet as a CSV file (using a macro or not)... The filename could include the date and time.
The CSV format for the above should look like this:
(The five commas at the end of each line are placeholders for additional headers, if needed. See 'About...' below).

Subject,Start Date,Start Time,End Time,End Date,,,,,
JA,9/30/2013,7:00 AM,7:00 PM,9/30/2013,,,,,
KW,9/30/2013,7:00 PM,7:00 AM,10/1/2013,,,,,
Intern,9/30/2013,2:00 PM,2:00 AM,10/1/2013,,,,,
JA,10/1/2013,7:00 AM,7:00 PM,10/1/2013,,,,,
LBW,10/1/2013,7:00 PM,7:00 AM,10/2/2013,,,,,
JEB,10/1/2013,2:00 PM,2:00 AM,10/2/2013,,,,,

You will need the complete pathname of the CSV file. That can be provided in a Message Box that
appears after a macro saves the worksheet as a CSV file. Whether you use a macro or not, you need to
copy or jot down the complete pathname and filename for use in Google Calendar. I used the same
pathname as my original data.

Then open Google Calendar and import the file to the appropriate calendar as follows:

I selected 'Test Calendar' under the 'My Calendars' heading so I was
viewing only one calendar. You need to deselect any other calendars before
importing the CSV file or they will also be populated with the same info. You do that by
clicking on the colored squares to the left side of each calendar listed…

IMPORTANT NOTE: You can use the 'Browse' button in Google Calendar to search for the CSV file in your
directory. You select the calendar to import the data 'TO' from a drop down list on the input box that
comes up in Google Calendar. And that is it!

I am including some notes here that I got from the internet about creating CSV files.
Like I said in the beginning, this solution is not 'automatic'...but it will import ALL your data to Google Calendar.
I suggest you post a sample of your list and perhaps someone will come along with a more 'automatic' solution,
but you need to show them sample data in the format (which columns and rows) you are working with.
Good Luck!
Perpa

About Importing CSV files (calendar info from Excel) into Google Calendar

https://support.google.com/calendar/answer/45656

CSV stands for 'Comma Separated Values,' so CSV files have a comma between each item
of information they contain. Google Calendar supports most CSV formatted files from
commonly used calendar applications such as Yahoo! Calendar and Microsoft Outlook.

All CSV files must have a correctly formatted header. The minimum amount of header
information required to import events into Google Calendar is: Subject, Start Date.
With that header information, Google Calendar will recognize the event as an All Day
event by default. To add more information to your events, simply add more headers.
Possible headers include: Subject, Start Date, Start Time, End Date, End Time, All Day Event,
Description, Location, and Private.

To edit a CSV file, first make sure that the file is originally saved with the .csv extension
(many text editing applications cannot "Save As..." a CSV, and manually appending the
extension does not make it a readable CSV file). However, Excel can save as a CSV.
An example CSV file looks like this:

Subject,Start Date,Start Time,End Date,End Time,All Day Event,Description,Location,Private
Final Exam,05/12/20,07:10:00 PM,05/12/07,10:00:00 PM,False,Two essay questions that will
cover topics covered throughout the semester,"Columbia, Schermerhorn 614",True

This event, 'Final Exam,' would then appear on May 12, 2020 from 7:10 PM to 10:00 PM. The
location would be 'Columbia, Schermerhorn 614,' the description would be 'Two essay
questions that will cover topics covered throughout the semester,' and it would be a private
event. Note that if any of your fields have commas in them (location, in the example above),
you can include them in your file by making sure to place quotation marks around the text.

Check out this article if you're having trouble importing your CSV file:
https://support.google.com/calendar/answer/45654
 

Perpa

Well-known Member
Joined
Dec 18, 2012
Messages
635
Hi

I have an excel userform with several functions..

What I would like is, when a user selects a date and a time, it would then automatically update Google Calendar. Is this possible at all?

Appreciate the help

Cheers

P4nny,
The following was posted 4/14/2015 in this forum:

"Is it possible to get excel to add entrys into google calender with a macro? "
http://www.mrexcel.com/forum/excel-...el-add-entrys-into-google-calender-macro.html

Kyle123 responded:
"Not without a lot a work and understanding some pretty complex VBA.
The problem is the authentication that google uses, OAuth2 isn't trivial in VBA so you need
to jump through some hoops to make it work. If you have outlook installed, it's much easier"

The following thread shows the 'hoops' Kyle123 speaks of in an earlier post (3/10/2015) by him:
"VBA code to send Google Calendar invitations from an Excel sheet"
http://www.mrexcel.com/forum/excel-...-google-calendar-invitations-excel-sheet.html

The above threads show there is 'some pretty complex VBA' to automate the update of a Google Calendar from Excel. But apparently it is 'possible'.
Good luck.
Perpa
 

Watch MrExcel Video

Forum statistics

Threads
1,129,871
Messages
5,638,773
Members
417,052
Latest member
Noobest

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top