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:
| Excel 2007 |
|---|
|
|---|
| A | B | C | D | E |
|---|
| 1 | Subject | Start Date | Start Time | End Time | End Date |
|---|
| 2 | JA | 12/30/2013 | 7:00 AM | 7:00 PM | 12/30/2013 |
|---|
| 3 | KW | 12/30/2013 | 7:00 PM | 7:00 AM | 12/31/2013 |
|---|
| 4 | Intern | 12/30/2013 | 2:00 PM | 10:00 PM | 12/30/2013 |
|---|
| 5 | JA | 12/31/2013 | 7:00 AM | 7:00 PM | 12/31/2013 |
|---|
| 6 | LBW | 12/31/2013 | 7:00 PM | 7:00 AM | 1/1/2014 |
|---|
| 7 | JEB | 12/31/2013 | 2:00 PM | 10:00 PM | 12/31/2013 |
|---|
|
|---|
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
