Software Development in North Wales

My pet hate is entering events in my calendar - it's so tediously manual and error prone. So here's a way of generating a calendar file that can be imported into any calendar that supports the vCal standard (in my case, FirstClass, but in your case, probably MS Outlook).

Confession time: I often use spreadsheets to quickly generate chunks of code. Tedious though it may be to devise formulae to generate text, it's easy to re-use. There are plenty of alternatives, but if you've got Excel or similar taking up valuable disk space, I reckon you might as well use it. So here's an Excel spreadsheet that lets you enter (or paste in) a list of dates, as a first step to generating a vCal file that you can import into your Calendar:



The data you enter goes in columns A to G (Date, What, Where, Start Time, End Time , Comments, and Available [Y or N = whether or not you are free to accept other appointments that clash with this one]).

Column K contains the vCal formatted events - just copy the contents of this column (minus the heading), paste it into Notepad (or a similar text editor), and (this is important) do a global replace to remove any double quotes (Excel adds these around each item, but we don't want them). Then save the file as something.vcs, and import it into your calendar - hey presto!

Here's the spreadsheet (save this as vCal-helper.xml and it will open in Excel when you double-click on it):

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Version>11.6360</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>1560</WindowHeight>
<WindowWidth>7515</WindowWidth>
<WindowTopX>360</WindowTopX>
<WindowTopY>120</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s21">
<Font ss:FontName="Arial Narrow" x:Family="Swiss"/>
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s22">
<Font ss:FontName="Arial Narrow" x:Family="Swiss"/>
<NumberFormat/>
</Style>
</Styles>
<Worksheet ss:Name="Events">
<Table ss:ExpandedColumnCount="11" ss:ExpandedRowCount="6" x:FullColumns="1"
x:FullRows="1" ss:StyleID="s21">
<Column ss:StyleID="s21" ss:Width="45.75"/>
<Column ss:StyleID="s21" ss:Width="30"/>
<Column ss:StyleID="s21" ss:Width="33"/>
<Column ss:StyleID="s21" ss:Width="45"/>
<Column ss:StyleID="s21" ss:Width="41.25"/>
<Column ss:StyleID="s21" ss:Width="47.25"/>
<Column ss:StyleID="s21" ss:Width="45.75"/>
<Column ss:StyleID="s21" ss:Width="77.25" ss:Span="1"/>
<Column ss:Index="10" ss:StyleID="s21" ss:AutoFitWidth="0" ss:Width="93"/>
<Column ss:StyleID="s22" ss:AutoFitWidth="0"/>
<Row>
<Cell><Data ss:Type="String">Date</Data></Cell>
<Cell><Data ss:Type="String">What</Data></Cell>
<Cell><Data ss:Type="String">Where</Data></Cell>
<Cell><Data ss:Type="String">Start Time</Data></Cell>
<Cell><Data ss:Type="String">End Time</Data></Cell>
<Cell><Data ss:Type="String">Comments</Data></Cell>
<Cell><Data ss:Type="String">Available?</Data></Cell>
<Cell><Data ss:Type="String">Start Time (vCal)</Data></Cell>
<Cell><Data ss:Type="String">End Time (vCal)</Data></Cell>
<Cell><Data ss:Type="String">vCal prep</Data></Cell>
<Cell><Data ss:Type="String">vCal item</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">LEAVE THIS ROW EMPTY</Data></Cell>
<Cell ss:Index="10"><Data ss:Type="String">BEGIN:VCALENDAR|VERSION:1.0</Data></Cell>
<Cell ss:Formula="=SUBSTITUTE(RC[-1],"|",CONCATENATE(CHAR(13),CHAR(10)))"><Data
ss:Type="String">BEGIN:VCALENDAR VERSION:1.0</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">39056</Data></Cell>
<Cell><Data ss:Type="String">Work</Data></Cell>
<Cell><Data ss:Type="String">Chester</Data></Cell>
<Cell><Data ss:Type="String">0900</Data></Cell>
<Cell><Data ss:Type="String">1730</Data></Cell>
<Cell ss:Index="7"><Data ss:Type="String">N</Data></Cell>
<Cell
ss:Formula="=CONCATENATE(YEAR(RC1),IF(LEN(MONTH(RC1))=1,"0",""),MONTH(RC1),IF(LEN(DAY(RC1))=1,"0",""),DAY(RC1),"T",IF(LEN(RC[-4])>0,RC[-4],"0000"),"00")"><Data
ss:Type="String">20061205T090000</Data></Cell>
<Cell
ss:Formula="=CONCATENATE(YEAR(RC1),IF(LEN(MONTH(RC1))=1,"0",""),MONTH(RC1),IF(LEN(DAY(RC1))=1,"0",""),DAY(RC1),"T",IF(LEN(RC[-4])>0,RC[-4],"0000"),"00")"><Data
ss:Type="String">20061205T173000</Data></Cell>
<Cell
ss:Formula="=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('vCal Template'!R1C1,"%end%",Events!RC9),"%where%",Events!RC3),"%start%",Events!RC8),"%what%",Events!RC2),"%comments%",Events!RC6),"%available%",IF(Events!RC7="N","0","10"))"><Data
ss:Type="String">BEGIN:VEVENT|CLASS:PUBLIC|DTEND:20061205T173000Z|LOCATION;ENCODING=QUOTED-PRINTABLE:Chester|PRIORITY:0|DTSTART:20061205T090000Z|SUMMARY;ENCODING=QUOTED-PRINTABLE:Work|X-COLOR:4291624704|X-SHOWTIME:0|DESCRIPTION;ENCODING=QUOTED-PRINTABLE:|END:VEVENT</Data></Cell>
<Cell ss:Formula="=SUBSTITUTE(RC[-1],"|",CONCATENATE(CHAR(13),CHAR(10)))"><Data
ss:Type="String">BEGIN:VEVENT CLASS:PUBLIC DTEND:20061205T173000Z LOCATION;ENCODING=QUOTED-PRINTABLE:Chester PRIORITY:0 DTSTART:20061205T090000Z SUMMARY;ENCODING=QUOTED-PRINTABLE:Work X-COLOR:4291624704 X-SHOWTIME:0 DESCRIPTION;ENCODING=QUOTED-PRINTABLE: END:VEVENT</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">39059</Data></Cell>
<Cell><Data ss:Type="String">Day off</Data></Cell>
<Cell ss:Index="7"><Data ss:Type="String">Y</Data></Cell>
<Cell
ss:Formula="=CONCATENATE(YEAR(RC1),IF(LEN(MONTH(RC1))=1,"0",""),MONTH(RC1),IF(LEN(DAY(RC1))=1,"0",""),DAY(RC1),"T",IF(LEN(RC[-4])>0,RC[-4],"0000"),"00")"><Data
ss:Type="String">20061208T000000</Data></Cell>
<Cell
ss:Formula="=CONCATENATE(YEAR(RC1),IF(LEN(MONTH(RC1))=1,"0",""),MONTH(RC1),IF(LEN(DAY(RC1))=1,"0",""),DAY(RC1),"T",IF(LEN(RC[-4])>0,RC[-4],"0000"),"00")"><Data
ss:Type="String">20061208T000000</Data></Cell>
<Cell
ss:Formula="=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('vCal Template'!R1C1,"%end%",Events!RC9),"%where%",Events!RC3),"%start%",Events!RC8),"%what%",Events!RC2),"%comments%",Events!RC6),"%available%",IF(Events!RC7="N","0","10"))"><Data
ss:Type="String">BEGIN:VEVENT|CLASS:PUBLIC|DTEND:20061208T000000Z|LOCATION;ENCODING=QUOTED-PRINTABLE:|PRIORITY:0|DTSTART:20061208T000000Z|SUMMARY;ENCODING=QUOTED-PRINTABLE:Day off|X-COLOR:4291624704|X-SHOWTIME:10|DESCRIPTION;ENCODING=QUOTED-PRINTABLE:|END:VEVENT</Data></Cell>
<Cell ss:Formula="=SUBSTITUTE(RC[-1],"|",CONCATENATE(CHAR(13),CHAR(10)))"><Data
ss:Type="String">BEGIN:VEVENT CLASS:PUBLIC DTEND:20061208T000000Z LOCATION;ENCODING=QUOTED-PRINTABLE: PRIORITY:0 DTSTART:20061208T000000Z SUMMARY;ENCODING=QUOTED-PRINTABLE:Day off X-COLOR:4291624704 X-SHOWTIME:10 DESCRIPTION;ENCODING=QUOTED-PRINTABLE: END:VEVENT</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="Number">39062</Data></Cell>
<Cell><Data ss:Type="String">Work</Data></Cell>
<Cell><Data ss:Type="String">Bangor</Data></Cell>
<Cell><Data ss:Type="String">0900</Data></Cell>
<Cell><Data ss:Type="String">1730</Data></Cell>
<Cell ss:Index="7"><Data ss:Type="String">N</Data></Cell>
<Cell
ss:Formula="=CONCATENATE(YEAR(RC1),IF(LEN(MONTH(RC1))=1,"0",""),MONTH(RC1),IF(LEN(DAY(RC1))=1,"0",""),DAY(RC1),"T",IF(LEN(RC[-4])>0,RC[-4],"0000"),"00")"><Data
ss:Type="String">20061211T090000</Data></Cell>
<Cell
ss:Formula="=CONCATENATE(YEAR(RC1),IF(LEN(MONTH(RC1))=1,"0",""),MONTH(RC1),IF(LEN(DAY(RC1))=1,"0",""),DAY(RC1),"T",IF(LEN(RC[-4])>0,RC[-4],"0000"),"00")"><Data
ss:Type="String">20061211T173000</Data></Cell>
<Cell
ss:Formula="=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE('vCal Template'!R1C1,"%end%",Events!RC9),"%where%",Events!RC3),"%start%",Events!RC8),"%what%",Events!RC2),"%comments%",Events!RC6),"%available%",IF(Events!RC7="N","0","10"))"><Data
ss:Type="String">BEGIN:VEVENT|CLASS:PUBLIC|DTEND:20061211T173000Z|LOCATION;ENCODING=QUOTED-PRINTABLE:Bangor|PRIORITY:0|DTSTART:20061211T090000Z|SUMMARY;ENCODING=QUOTED-PRINTABLE:Work|X-COLOR:4291624704|X-SHOWTIME:0|DESCRIPTION;ENCODING=QUOTED-PRINTABLE:|END:VEVENT</Data></Cell>
<Cell ss:Formula="=SUBSTITUTE(RC[-1],"|",CONCATENATE(CHAR(13),CHAR(10)))"><Data
ss:Type="String">BEGIN:VEVENT CLASS:PUBLIC DTEND:20061211T173000Z LOCATION;ENCODING=QUOTED-PRINTABLE:Bangor PRIORITY:0 DTSTART:20061211T090000Z SUMMARY;ENCODING=QUOTED-PRINTABLE:Work X-COLOR:4291624704 X-SHOWTIME:0 DESCRIPTION;ENCODING=QUOTED-PRINTABLE: END:VEVENT</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">LEAVE THIS ROW EMPTY</Data></Cell>
<Cell ss:Index="10"><Data ss:Type="String">END:VCALENDAR</Data></Cell>
<Cell ss:Formula="=SUBSTITUTE(RC[-1],"|",CONCATENATE(CHAR(13),CHAR(10)))"><Data
ss:Type="String">END:VCALENDAR</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Print>
<ValidPrinterInfo/>
<PaperSizeIndex>9</PaperSizeIndex>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<Selected/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="vCal Template">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:StyleID="s21">
<Row>
<Cell><Data ss:Type="String">BEGIN:VEVENT|CLASS:PUBLIC|DTEND:%end%Z|LOCATION;ENCODING=QUOTED-PRINTABLE:%where%|PRIORITY:0|DTSTART:%start%Z|SUMMARY;ENCODING=QUOTED-PRINTABLE:%what%|X-COLOR:4291624704|X-SHOWTIME:%available%|DESCRIPTION;ENCODING=QUOTED-PRINTABLE:%comments%|END:VEVENT</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>