Using Excel, Macros and @Evernote for Course Planning

I use Evernote a lot. I store interesting tweets, grab sections of web pages for later, have a form of GTD in there (The Secret Weapon) to keep me focussed. I took part in the 30-day paperless challenge in September and had a lot of fun seeing what was possible. I’ve even started to get my colleagues interested in using Evernote. And last month I was very happy to discover my work opened up access to the web version of Evernote.

I’ve been thinking about this for a while now: There has been a distinct disconnect in my workflow. Found an interesting link? Send it to Evernote. Got a unit to develop? Plan it up in Evernote and assign a task in my GTD stack. Plan my weekly lessons? A paper desktop planner with scribbled notes on which resources I need to have ready?! It’s really daft but, up until recently, it was the most accessible way to work. I’ve even found myself taking pictures of my planner pages and sending them to Evernote so I can check I’m ready for the coming week!

Two weeks ago I attempted to set up an electronic planner in Evernote from my workplace. The web version didn’t really like being pasted into from a Word document and screwed up all the formatting and numbering so I figured I needed a few hours investigation time and a desktop version of Evernote to make progress. This weekend I made time.

Firstly I wanted to be able to create a template with the correct number of rows for each particular class for each term, so I created a simple macro that took values (class name, start date, end date, number of periods per week) and used them to populate a new Excel worksheet. Here’s the VBA if you want to use it yourself:

Sub btnCreateTermPlanner()
Dim AddSheetQuestion As Variant
Dim StartDate As String
Dim EndDate As String
Dim noPeriods As Integer
Dim noPPW As Integer

StartDate = Worksheets(“Start”).Cells(1, 2)
EndDate = Worksheets(“Start”).Cells(2, 2)
noPeriods = Worksheets(“Start”).Cells(5, 2)
noPPW = Worksheets(“Start”).Cells(4, 2)

AddSheetQuestion = Application.InputBox(“Please enter the name of the sheet you want to add,” & vbCrLf & _
“or click the Cancel button to cancel the addition:”, _
“What sheet do you want to add?”)
‘ create new workbook
Worksheets.Add
ActiveSheet.Name = AddSheetQuestion
ActiveSheet.Move After:=Worksheets(Worksheets.Count)
‘ add header text
Worksheets(AddSheetQuestion).Range(“B2:F2”).Merge
Cells(2, 2) = AddSheetQuestion
Worksheets(AddSheetQuestion).Range(“B3:F3″).Merge
Cells(3, 2) = StartDate & ” – ” & EndDate & “, ” & noPPW & ” periods per week.”
Cells(5, 2) = “#”
Cells(5, 3) = “wb.”
Cells(5, 4) = “Topic”
Cells(5, 5) = “Learning Objectives”
Cells(5, 6) = “Resources”
‘ number rows
Call NumberPlanner(noPeriods)

‘ date rows
Call DatePlanner(noPeriods, StartDate, noPPW)

Worksheets(AddSheetQuestion).Columns(4).ColumnWidth = 30
Worksheets(AddSheetQuestion).Columns(5).ColumnWidth = 30
Worksheets(AddSheetQuestion).Columns(6).ColumnWidth = 30
End Sub

Sub NumberPlanner(ByVal noPeriods As Integer)
Dim count1 As Integer
For count1 = 1 To noPeriods
Cells(count1 + 5, 2) = count1
Next count1
End Sub
Sub DatePlanner(ByVal noPeriods As Integer, ByVal StartDate As Date, ByVal noPPW As Integer)
‘ One date at the start of every week
Dim date1 As Integer
Dim wb As Boolean
Dim daycounter As Integer
Dim weekcounter As Integer
wb = True
daycounter = noPPW
weekcounter = -1
For date1 = 0 To noPeriods – 1
If daycounter = noPPW Then
wb = True
daycounter = 0
weekcounter = weekcounter + 1
End If
If wb Then
Cells(date1 + 6, 3) = StartDate + (weekcounter * 7)
wb = False
End If
daycounter = daycounter + 1

Next date1
Cells.Columns.AutoFit

End Sub

It’s not refined and I have to admit I can’t remember how to make the macro populate a Word document directly yet but the code above generates something a little (ok, exactly) like this:

A little manual formatting after copying and pasting into Word resulted in this:

Finally, copying and pasting into the desktop version of Evernote was a breeze. It even took in the document header and footer (I’d recommend removing the footer as an Evernote note does not have a page break!).

The time consuming part will be linking Evernote notes into the resources column for each lesson, but I’m happy the structure is there. The aim is that, once completed, these plans will not only be synchronised between home and work (reducing the need for those photographs of paper planners!) but that revising them will be much more efficient as I can immediately add in a new resource or interesting news article link when I find it (because they always appear three months before or after you actually need them). If my colleagues need to look at them for any reason I can send it quickly via email.

The ultimate goal? No paper planner at all for my lessons. These folk have already managed it and, if the web version can handle editing the newly created notes, it won’t take long until I manage it too.