Excel to Google Calendar

[madmarki]

Hi


I often find that I have lists of events in Excel that I want to add to a Google Calendar.


The easiest way I have found to do this is use a spot of Custom Formatting in Excel and then Copy & Paste the cells to Notepad and create a .txt file. You can then import this .txt into Google Calendar.


Google requires the following columns, in this order:


"Subject","Start Date","Start Time","End Date","End Time","All day event","Description"


E.g.


"What's new in Gengerbread 2.3.6?","09/05/2011","9:00 AM","09/05/2011","10:00 AM","TRUE","write blog post"


It is very particular about quotation marks and date and time formats, so I use the following Custom Formats


"@", for Text

"MMDDYY", for Dates

"H:MM AM/PM", for Times


My question is, how can I create the .txt file from Excel. I've tried both CSV and TXT exports, but both insist on putting extra quotation marks around all the fields. So I end up with something like:


"""What's new in Gengerbread 2.3.6?""","""09/05/2011""","""9:00 AM""","""09/05/2011""","""10:00 AM""","""TRUE""","""write blog post"""


If I don't add the quotation marks in Excel, then in the export the text fields get quotes but the dates and times do not. So I end up with something like:


"What's new in Gengerbread 2.3.6?",09/05/2011,9:00 AM,09/05/2011,10:00 AM,"TRUE","write blog post"


Neither of which import correctly into Google.


Does anyone have any ideas about how I might get an export with the required number of quotes and commas, please?


Thanks

Mark

 

[vijaySharma]

Hi,


Try the below code and let us know...

Sub createTextFile()

Dim strFileName As String

Dim myFreeFile As Long


    myFreeFile = FreeFile


    strFileName = "c:test.csv"


    Open strFileName For Output As myFreeFile


    Range("A2").Select

Do While ActiveCell.Value <> ""


        For cntr = 0 To ActiveCell.Columns.CurrentRegion.Columns.Count - 1

tempstr = tempstr & Chr(34) & ActiveCell.Offset(0, cntr) & Chr(34) & ","

Next

tempstr = Mid(tempstr, 1, Len(tempstr) - 1)

Print #myFreeFile, tempstr

ActiveCell.Offset(1, 0).Select

Loop

Close myFreeFile

End Sub


~VijaySharma

 

[madmarki]

Hi VijaySharma


many thanks for the super speedy reply.


some bits are working, the output .csv file has the correct number of quotation marks and commas.


but two things are not working:

1) the time columns are coming out as "0.375" and "0.416" rather "9:00 AM" and "10:00 AM" - they are stored as Time and Date in Excel.

2) all the rows appear on one line in the CSV file, and each line in the output file is the repeated, so I get 50 copies of a very long text field. (I have 50 rows of data in my test file).

 

[madmarki]

sorted the looping problem now (number 2 above)


ADDED Chr(13) to the end of 'tempstr'


tempstr = Mid(tempstr, 1, Len(tempstr) - 1) & Chr(13)


SET 'tempstr' to "" before it starts on the next row


Print #myFreeFile, tempstr

ActiveCell.Offset(1, 0).Select

tempstr = ""


Still getting the Time data in the wrong format though, any suggestions?

 

[vijaySharma]

Hi,


Here is the updated code to take care of the issue...


Just one point to keep in mind.... the Start and End Time columns should be formatted as h:mm:ss AM/PM

Sub createTextFile()

Dim strFileName As String

Dim myFreeFile As Long


    myFreeFile = FreeFile


    strFileName = "c:test.csv"


    Open strFileName For Output As myFreeFile


    Range("A2").Select

Do While ActiveCell.Value <> ""


        For cntr = 0 To ActiveCell.Columns.CurrentRegion.Columns.Count - 1

tempstr = tempstr & Chr(34) & ActiveCell.Offset(0, cntr).Text & Chr(34) & ","

Next

tempstr = Mid(tempstr, 1, Len(tempstr) - 1) & vbCrLf

Print #myFreeFile, tempstr

ActiveCell.Offset(1, 0).Select

Loop

Close myFreeFile

End Sub

~VijaySharma

 

[madmarki]

Cheers


I've placed a working copy of this spreadsheet along with some instructions in my public Skydrive folder, if its of any use to anyone else.


http://goo.gl/UIlQZ


it's called "g-cal export.xlsm" - Excel 2007 format


~Mark

 

[jacoblindberg]

"Path not found". What am I doing wrong?

 

[jacoblindberg]

Two things I notice:

* I get 11/04/YY instead of 11/04/11.

* I get H:01 fm instead of AM (maybe because I have a swedish version of excel).

 

[Debraj]

Hi jacoblindberg!

What am I doing wrong?

* You are posting your query in someone's post.

* You are trying to download a file which was submitted 1 Year ago.

* You are trying to match your look a like similar problem with may be fully different topic.


Best part you have done is at-least you have inform us that you are using Swedish Excel.


Please start a new topic, provide proper and as much as possible details of your problem and try to upload a sample file for your data..


Please read three green sticky post.. for more detail.. so that we can help you better..


Regards,

Deb