Sunday, December 27, 2009

Lessons learned from redesigning my website

Just finished a major re-design of my business website. Most of the work seemed to be to just figure out a basic design (also something that I’m not really good at). In my previous post I included a mockup of the design I had in mind. I managed to get things almost exactly done as I wanted.

There were a few things I changed in the html:

Menu Border Images

In the old design, there were little images at the left side of the menu bar:

image

This added a nice border to the menu bar, but of course this was totally a visual thing and nothing that belonged in the html. It should have been a background image of the div that made up the menu bar. In the new design, the images were no longer needed, so they were removed from the html files.

SmarterSoftware Pen Image

In the header of each page, I have an image of the company logo, a pen writing some digital code (originally designed by Leo Blanchette at Jesterarts):

image

As this is also nothing structural, I’ve moved the reference to the image from the html to the css file as a background image. This is not totally clear-cut as with the menu images. In the first version I made the image a link to the initial splash screen with a large version of the pen. I’m not aware of any way this can be done with a background image (other than with JavaScript, which I may do in another version).

Additional divs

The only thing that was a bit tricky to do in CSS was the way I wanted to “switch” colors in the left “column”:

image

I’ve played around with the “border-left” properties but could quite get it to work.

Here’s a little code snippet of what I finally implemented:

HTML:
<div id="header" class="outer"> <div class="inner">
    <p> Smarter Software </p>
</div> </div>
<div id="nav" class="outer"> <div class="inner"> <ul>
    <li><a id="current" href="home.html"> Home </a> </li>
    <li>&bull; </li>
    <li> <a href="loesungen.html"> L&ouml;sungen </a> </li>
</ul>  </div> </div>
<div id="main" class="outer"> <div class="inner"> 
    <h1>Wir &uuml;ber uns</h1> 
</div> </div>
CSS:
.outer {
    background-color: rgb(0, 102, 153);
    margin-right: 20%;
}

.inner{
    background-color: white;
    margin-left: 15%;
    padding: 10px;
}

div#nav.outer, div#footer.outer{
    background-color: white;
}

div#nav .inner, div#footer .inner{
    background-color: rgb(0, 102, 153);
    padding: 0;
}

The outer divs define the color in the left “column”, usually blue (the rgb-color). Also, with the right margin set here, the color to the right of the text will be the standard background color (usually white). We could move the margin-selector to the inner class, so that the color on the left would be repeated on the right. The outer div only contains an inner div, but no content on its own.

The inner divs then define the left margin and the background color for the “main” content area. Usually, it’s a white background, but for #nav (the navigation or menu bar) and the footer the colors are swapped between the inner and outer divs.

This achieves the desired look, but introduces extra divs that are purely presentational. Does anyone have a better idea how to achieve this look without additional divs? Any ideas are appreciated!

Summary

I was quite happy that there weren’t to many changes required. Once I got the main page done, there weren’t many steps required to get the whole website changed. Total re-design took two evenings on X-mas, maybe 4 to 5 hours of work in total (including this blog-post). Not too bad!

Friday, December 18, 2009

Planning to re-design my website using just CSS

When I became a freelancer more than a year ago, one of the first things I did was to build and deploy my own website.

Back then, I knew basic HTML, but CSS was pretty new to me. What better way to get to know it than with a real project! I’ve read a couple of books and websites and managed to get things done and I’m quite happy with the results from a coding perspective.

Now that I’m starting to talk to a few more people in my network, there are some new people looking at the site. An old colleague/boss remarked recently that it looked like a school project.  Touche!

So I looked a bit around for a new design. One inspiration was a friend’s design for PowerPoint slides. As I also plan to add a “business blog” to my site, I’ve looked into wordpress as blogging platform and some of the more common templates. I’ve picked a good looking template and did some changes to it, so between those two inspirations, I’ve got a baseline.

Here’s a quick mockup:

Website Mockup

Depending on how well I developed the site last year, I managed to split content and structure (in the HTML) and presentation (in the CSS). I hope to get almost all of the changes done just by changing the CSS. My goal is to do minimal changes to HTML (apart from adding maybe a class or id here and there). It’ll be interesting to see how much I’ll be able to do and I’ll post some lessons learned in this blog.

Have you tried similar things? Let me know in the comments or send me an email!

Friday, July 31, 2009

Possible Extensions

The code I currently have is working fine for me. However, there may be a few extensions that can be helpful.

Configure locations

For now, some information is hard coded, for example the initial path that the playlists are to be written to. There should be some easy way of configuring this information and storing the information “somewhere”.

Check existence of files

It only makes sense to put files in the playlist if the files exist at the location that is put into the playlist. The Converter could check if the file actually exists and only include the file if it is already there.

This information could be checked at the time the playlist is displayed in the listbox. Not sure if the runtime of all that checking would be acceptable.

Copy the music files if required

The next step would be to have the Converter copy the file from the local path to the server location if the file is missing at the server location. That way, the file would be there whenever it is referenced in the playlist on the server.

Interested or other ideas?

Let me know if one of these or some other feature would be useful to you – just leave a comment or drop me an email. I will then see if I can put that in the code.

Main

Friday, July 24, 2009

Retrieving track information from the iTunes XML library file

In order to convert a playlist as described in the previous post, we need two pieces of information:

  1. the tracks (represented by their IDs) in a playlist
  2. the location of the file of a certain track

How to get this from the iTunes XML library file is described in the next sections.

Get Tracks in a playlist

Similar to the playlist member in the library, the trackIDs member of the playlist is built at the fist time it is accessed:

public List<String> getTrackIDs()
{   // lazy initialization
 if (_trackIDs == null)
 {
     _trackIDs = new List<String>();

     //Query XML for TrackIDs
     IEnumerable<XElement> tracks = (from element in _root.Descendants()
                                       where element.Value.Equals("Track ID")
                                       select element);
     foreach (XElement track in tracks)
     {
         _trackIDs.Add((track.NextNode as XElement).Value );
     }
 };
 return _trackIDs;
}

Again, the LinkToXML query retrieves the items with a certain value (“Track ID”) from the XML. The statement uses just the part representing the playlist (stored in the _root member). It then moves over to the NextNode to get the actual value and stores all of these in a member that the form can then iterate over.

Get filenames for a given TrackID

Once you know the IDs of all the tracks in the playlist, you can then retrieve the filename as follows:
internal string GetTrackLocation(string ptrackID)
{
 // get root of XML-Dictionary for this track
 XElement track = (from element in _root.Descendants().Elements("key")
                   where element.Value.Equals(ptrackID)
                   select element).First().NextNode as XElement;

 XElement location = (from element in track.Descendants()
                   where element.Value.Equals ("Location")
                   select element).First().NextNode as XElement;

 string fileName = HttpUtility.UrlDecode((string)(location  as XElement));

 return fileName;
}
This code is in the library class. I could have used a dedicated track class, but apart from this little piece of information, there wasn’t anything else needed – so that felt a little bit too small to actually build a class for that.

The code gets the location in these steps:

  1. Get the dict for this specific track (lines 4 to 6)
  2. Get the “Location” from within this specific dict (lines 8 to 10)
  3. Get the ‘proper’ formatting by URL-Decoding the value from the XML.
I’m sure that there are easier or more compact ways to achieve this, but this code is working fine and seems quite clear to me. Let me know if you have better suggestions ….

Main

Friday, July 17, 2009

Converting the iTunes Playlists into the M3U format

Once the playlists are displayed in the listbox, the user can select the playlists to be exported and then click the “Convert” button. Here’s the code that is triggered by the button click:

private void btnConvert_Click(object sender, EventArgs e)
{
 // check existence of psPath
 DirectoryInfo di = new DirectoryInfo(txtResultPath.Text+"\\");
 if (!di.Exists) {
     MessageBox.Show ("Path to write to does not exist", "Problem",
         MessageBoxButtons.OK,MessageBoxIcon.Error);
     return;
 }

 foreach (Playlist pl in lbPlaylists.SelectedItems)
 {
     WriteToFile(pl, Path.GetDirectoryName(txtResultPath.Text+"\\"));
 }

 MessageBox.Show("Playlists written","Info",MessageBoxButtons.OK,MessageBoxIcon.Information);
}
After some checking for the existence of the path to write the playlists to (lines 4 to 9), the code loops through all the selected playlists and writes out each playlist to a file by calling the auxiliary function “WriteToFile”.

Here’s this method:

private void WriteToFile(Playlist pl, String psPath)
{
 //create the file - overwrite if necessary
 FileInfo fi = new FileInfo (Path.Combine (psPath, pl.name() + ".m3u"));
 StreamWriter fs = new StreamWriter(fi.Open(FileMode.Create));
 // write stuff
 foreach (String trackID in pl.getTrackIDs())
 {
     String trackLocation = _library.GetTrackLocation(trackID);

     // now remove the "local" part of the location
     int pos = trackLocation.IndexOf("/iTunes Music/");
     string newFileName = trackLocation.Substring(pos+13);
     newFileName = "//Marvin/musik" + newFileName;
     newFileName = newFileName.Replace("/", "\\");

     fs.WriteLine(newFileName);
 }
 fs.Close();
}
This could be a member of the Playlists, but I’ve decided to leave it in the Converter class in order not to introduce additional dependencies in the Playlist class. This way, all the Playlist and Library classes do is read the contents of the iTunes library. This way the Converter class does not have to deal with any XML. The code iterates through all the tracks in the playlist (through their IDs, line 7). The tracks are retrieved in the order that they are in the playlist file (which is implicitly the order of the tracks in the playlist). In the loop, the file location of the track is retrieved (line 9). This location is then stripped of the “local part” (the part up to and including “/iTunes Music/”, lines 12&13), then the path for my server is added (line 14) and some proper formatting is applied (line 15) so the new file path is usable by the TVersity media server. Each constructed filename is written out to the file. The next post describes the additional functionality in the library class in order to retrieve the required track information from the XML.

Main | Next

Friday, July 10, 2009

Display iTunes Playlists in the listbox

In the previous post I described how the playlists in an iTunes library are read from the XML file and then added to the listbox.

In order for the listbox to properly display these objects, the ToString() member for each of these playlist objects is called. Here’s this method:

public override string ToString()
{
  return name() + " (ID: " + id() + ", " + getCount() + " titles)";
}
Basically, it just constructs a string that gives out some information about the playlist. Each of these pieces of information is a simple method that retrieves the required information from the part of the playlist from the XML similar to these examples:
public string id()
{
  return (string)((from element in _root.Descendants()
                   where element.Value.Equals("Playlist ID")
                   select element).First().NextNode as XElement);
}

public int getCount()
{
  return (from element in _root.Descendants()
          where element.Value.Equals("Track ID")
          select element).Count();
}
Each of these queries is quite simple once you have a look at the structure of the XML, but the combination is pretty powerful to display all the playlists in my iTunes library: image

The next post describes how the conversion of the playlists works. Main | Next

Friday, July 3, 2009

Reading the Playlists from the iTunes Library XML

Once you have selected an iTunes Library XML (or if the default location is working), the code reads through the XML file to figure out which playlists are in the file.

The playlist is stored in an instance variable (line 1) that is instantiated with the filename as a parameter (line 3):

private Library _library;
...
_library = new Library(txtLibrary.Text);

In the constructor of the Library class the XML file is read:

public Library (string pfileName) {
 // no other way to set the root
 _root = XElement.Load(pfileName);
 _playlists = null;
}

Line 3 is the first line of code that uses LinqToXML ... not much too it. For a production environment, some kind of error handling would probably have to be added. The instance variable _playlists is not initialized, it will be set up at the time it is accessed.

The fist access happens when the playlists are to be displayed in the list box. This code is from the Playlist Converter Form right after instantiating the _library instance variable:

foreach (Playlist pl in _library.getPlaylists()) {
 lbPlaylists.Items.Add(pl);
}

Getting the playlists requires some XML reading:

public List<Playlist> getPlaylists ()
{   // lazy initialization
 if (_playlists == null)
 {
     _playlists = new List<Playlist>();

     //Query XML for Playlists
     XElement rootList = (from element in _root.Descendants()
                          where element.Value.Equals("Playlists")
                          select element).First().NextNode as XElement;
     IEnumerable<XElement> listIDs = (from listFields in rootList.Descendants()
                                        where listFields.Value.Equals("Playlist Persistent ID")
                                        select listFields);
     foreach (XElement list in listIDs)
     {
         _playlists.Add(new Playlist(list.Parent as XElement));
     }
 };
 return _playlists;
}

The method returns a list of playlists. If the instance variable is already initialized, it is just returned in a getter fashion. Otherwise we build the list in a few simple steps.

First, we find the root element for the playlists in the XML file with the query in lines 8 to 10. We look for a node that has a value of “Playlists”. There is only one in the file, so “.First” returns this single node. The array of playlists is the next node after that, so “.NextNode” returns this array.

Once we have that array, we can then query in it for the individual playlists. Lines 11 to 13 looks for all the “Playlist Persistent ID” nodes in this array. Then we can iterate through these elements and build Playlist objects with the dict for an individual playlist (line 16).

After each of these new playlist objects has been instantiated, they can just be added as an item to the listbox. In order for the listbox to properly display these items in a list, the ToString() member of the playlist will be called. This will be described in the next post …

Main | Next

Friday, June 26, 2009

Base functionality of the Form

Here is a screenshot of the form in designer view:

image

This form is just relatively simple C'# code (the LinQ2XML will be described in future posts). I’ll just point out a few of the important parts:

Choosing the iTunes library

This field holds the fully qualified filename for the iTunes library. There is a sensible default (line 11), but you can choose a different location with the “Choose …” button (code in lines 27ff.)

Populating the listbox of playlists (lbPlaylists)

This listbox holds the playlists in the iTunes library. It is populated at the startup of the form and whenever a different library path is chosen. We’ll have a closer look at the code that achieves this behavior in another post (lines 18ff.).

Select all / Select None of the entries in lbPlaylists

These are just shortcuts to make selecting all or unselecting of the playlists easier. (Or is it select none?!) It’s just a simple loop over all the items in the listbox (lines 64ff and 72ff).

Choosing the path for the converted playlists (noxon Library)

Another simple data field that holds the directory the converted playlists are to be written to. The behavior of the selection dialog is a little bit different than the one for the iTunes library (lines 46ff).

Convert the playlists

The code that is triggered by clicking the “Convert” button will be described in another post.

Here’s the main part of the form code:

public partial class frmConverter : Form
  {
      private Library _library;

      public frmConverter()
      {
          InitializeComponent();
          txtLibrary.Text =
                 Environment.GetFolderPath(Environment.SpecialFolder.MyMusic)
                 + "\\iTunes\\iTunes Music Library.xml";
          txtResultPath.Text = "\\\\Marvin\\musik\\Playlists";
          // for testing
          //txtResultPath.Text = "C:\\temp\\Playlists\\";
          _library = new Library(txtLibrary.Text);
          populate_lbPlaylists();          
      }

      private void populate_lbPlaylists()
      {
          lbPlaylists.Items.Clear();
          foreach (Playlist pl in _library.getPlaylists())
          {
              lbPlaylists.Items.Add(pl);
          }
      }

      private void btnChooseLibrary_Click(object sender, EventArgs e)
      {
          OpenFileDialog dlg = new OpenFileDialog();
          dlg.Filter = "iTunes Library (*.xml)|*.xml";
          dlg.InitialDirectory = Path.GetDirectoryName(txtLibrary.Text);

          if (dlg.ShowDialog() == DialogResult.OK)
          {
              txtLibrary.Text = dlg.FileName;
              _library = new Library(txtLibrary.Text);
              populate_lbPlaylists();
          }
      }

      private void btnClose_Click(object sender, EventArgs e)
      {
          this.Close();
      }

      private void btnChooseResult_Click(object sender, EventArgs e)
      {
          FolderBrowserDialog dlg = new FolderBrowserDialog();
          dlg.SelectedPath = Path.GetDirectoryName(txtResultPath.Text+"\\");
          dlg.Description = "Choose the path to write the playlists to:";
          dlg.ShowNewFolderButton = false;

          if (dlg.ShowDialog() == DialogResult.OK)
          {
              txtResultPath.Text = dlg.SelectedPath;
          }
      }

      private void btnConvert_Click(object sender, EventArgs e)
      {
   // more on this later
      }

      private void btnAll_Click(object sender, EventArgs e)
      {
             for (int i = 0; i < lbPlaylists.Items.Count; i++)
              {
                  lbPlaylists.SetSelected (i, true );
              }
      }

      private void btnNone_Click(object sender, EventArgs e)
      {
          for (int i = 0; i < lbPlaylists.Items.Count; i++)
          {
              lbPlaylists.SetSelected(i, false);
          }
      }
  }

Main | Next

Thursday, June 11, 2009

A look at the iTunes XML and playlist formats

The playlist converter basically grabs the playlists in iTunes and converts them into a different format. Before looking at the code, here is a short description of the relevant formats.

iTunes XML

iTunes stores the “meta-information” about the music, playlists etc. that you can use and set up in iTunes in an XML file called “iTunes Music Library.xml” which is usually stored in the iTunes music folder (usually My Documents\My Music\iTunes).

There is a couple of web documents that describe the format. I have used an article by Niel Bornstein called “Hacking iTunes”. Some examples here are taken from this article.

There are three main sections in the file:

  1. some meta info (such as general location infos, version etc.)
  2. tracks info (a large dictionary of track information)
  3. playlists info (a large dictionary of playlist information)
Tracks

For each track, there is number of key – value pairs. ‘Key’ can be Track ID, Name, Artist and Location, whereas value can be a number or string which denotes the key’s value, e.g.

 <key>Track ID</key><integer>839</integer>
<key>Name</key><string>Sweet Georgia Brown</string>
<key>Artist</key><string>Count Basie & His Orchestra</string>
<key>Composer</key><string>Bernie/Pinkard/Casey</string>
<key>Location</key><string>file://localhost/Users/niel/Music/iTunes/iTunes%20Music/Count%20Basie%20&%20His%20Orchestra/Prime%20Time/03%20Sweet%20Georgia%20Brown.m4p</string>
Playlists
Each playlist also has some meta information (such as name etc., lines 2 to 4) and an array with the TrackIDs of the tracks that make up the playlist (lines 7 to 14):
<dict>
<key>Name</key><string>Funky</string>
<key>Playlist ID</key><integer>6652</integer>
<key>Playlist Persistent ID</key><string>88CED99A2F698F3C</string>
<key>All Items</key><true/>
<key>Playlist Items</key>
<array>
<dict>
<key>Track ID</key><integer>837</integer>
</dict>
<dict>
<key>Track ID</key><integer>754</integer>
</dict>
</array>
</dict>
This playlist (called “Funky”) contains two tracks (with the IDs 837 and 754). Information about these tracks can be obtained from the tracks section using the IDs. As this is a “plain” XML file, it can be queried using LinQ To XML.

M3U playlist

The M3U playlist format is very simple, it is just a list of the files to be played, each title in its own line:
\\Marvin\musik\Download\Run\01 01 Mornin'.mp3
\\Marvin\musik\Download\Run\13 08 Man Of La Mancha (I, Don Quixote).mp3
\\Marvin\musik\Download\Bike\01 01 Child's Anthem.mp3
This playlist contains three titles, each denoted by the location of their MP3 files. Additional information (Artist, Title, Album etc.) can only be obtained from the meta-information stored in the MP3 files. The file is a plain text file – as I only have to write to this file, I’m just using a simple TextStream to write to. If the format was a bit more involved (say another, maybe differently formatted XML file), I could have used LinQToXML again, but it would be overkill for writing M3U files.

Main | Next

Saturday, June 6, 2009

A first look at the PlaylistConverter

The general idea behind the PlaylistConverter is described in a previous post.

Here’s a screenshot of the PlaylistConverter GUI:

image

On the top is the location of your iTunes library. The program uses a sensible default so usually there is no need to point the program to a specific location using the Choose-button to the right.

The middle part lists all the playlists in your iTunes library. It also displays some “internals” (such as the ID of the playlist) and the number of titles in the playlist. From the available lists, you can choose one or more lists to be exported. The ‘Select All’ and ‘Unselect All’ buttons make life a bit easier.

Under the list of playlists is the location that the playlists will be exported to (the so-called ‘noxon Library’ which is actually a folder on my Windows Home Server). I’ve hard-coded a default for my environment, but again you can choose a different location with the Choose-Button.

If you click ‘Convert’ the selected playlists will be converted and saved in the noxon library directory. Each playlist will be saved in an individual file. The file locations of the titles will be changed according to the paths on the Home Server.

So with this program, my wife can set up a playlists on her own computer in iTunes and convert it into a format and locations on the Windows Home Server. There, TVersity picks up the new content and makes it available to the noxon.

End result: You set up a playlist on your own PC and you can play this list on the noxon. Pretty cool!

Here's a list of the posts that describe the PlaylistConverter:

If the current functionality is not sufficient, here are some ideas for extensions. Also, when you're interested in the full source code or an installer, drop me an email.

Friday, June 5, 2009

Introducing the iTunes Playlist Converter (with C# and LinQ)

Here’s the scenario that needed a solution:

  • My wife and I both have our individual PCs with our music collections (not too much overlap between us ;-)). Originally it was mostly ripped CDs but since iTunes und Amazon started selling MP3s without any DRM, we’ve mostly switched to “digital downloads”.
  • We also have a Noxon “internet radio” in our living room so we can listen to Internet streams through wireless LAN.
  • Occasionally we also want to play music from our individual collections (e.g. background music for parties, especially “seasonal” music at Christmas) on our living room stereo.

We could have copied the MP3s to a thumb drive and hooked that up to the Noxon, but that sounded a bit manual. (C’mon - you buy your music digitally from Apple or Amazon, it gets delivered to your PC from America or wherever, but in order to play it on your stereo you have to “sneakernet” it to the living room!) So I was looking for a better solution …

After looking around a bit, I tried TVersity. It is a “streaming server” using the UPnP protocol. TVersity publishes a list of available media to the Noxon, and can stream titles or playlists that are then played on the Noxon using my local wireless LAN.

TVersity even works on my Windows Home Server. So just copy all our music on a regular basis to the Home Server where TVersity can find it, and your almost done. This works great for individual titles and full albums, but playlists don’t.

First, we both use iTunes to manage our music, and iTunes has its own way of storing the playlists (using one larfge file an XML format, and it’s a format that is not supported by the Noxon which uses an individual M3U file for each playlist). Second, all the references in the playlist are to files local to each PC and that is different from where the files are stored on the home server.

A good scenario to try out some C# programming! Also, as iTunes uses an XML file to store all its information, I could play around with LinQ to query the contents.

The book I used to get up to speed on LinQ was this:

I found this to be quite well written, but I needed the work of “doing it myself” before I was able to figure out which parts I had not quite understood. Pretty normal for anything new!

Next: A first look at the PlaylistConverter

Wednesday, May 20, 2009

Sending an Email from VBA

The Excel ToDo list has a keyboard shortcut that sends an email containing all the “red” todo items (i.e. those that are due today or before today). I use this to send my open items from “home” to my email address at my current customer site. That way, if I have to remember my todos for a given day at work, I always have them available.

The code that sets up the email body is probably not very interesting, here is just a short excerpt that builds the email body in the variable text by concatening the titles of the open items that are due until today

Do While Cells(i, ncNr) <> ""
 If Cells(i, ncStatus) = ToDo.scStatusOpen _
  And Cells(i, ncWhen) <= Date Then
      text = text _
          & "(" & Cells(i, ncNr) & ")" & vbTab & Cells(i, ncText) _
          & vbCrLf
 End If
 i = i + 1
Loop

(For the full code, check out the Worksheet sub SendToDoMail.)

After the body is built, it is then sent to the sub DoSendMail in the Mail module. (This call is commented out in the version that you can download. In order to enable this functionality, you’ll have to do add some configuration changes in the code.) Here’s the code that is delivered:
Sub DoSendMail(pmailBody As String)

Dim objConfig As CDO.Configuration
Dim objMessage As CDO.Message

' Set up the configuration
Set objConfig = New CDO.Configuration
objConfig.fields.Item(cdoSendUsingMethod) = cdoSendUsingPort
objConfig.fields.Item(cdoSMTPServer) = "<SMTPServer>"
objConfig.fields.Item(cdoSMTPServerPort) = 25
objConfig.fields.Item(cdoSMTPConnectionTimeout) = 10
objConfig.fields.Item(cdoSMTPAuthenticate) = cdoBasic
objConfig.fields.Item(cdoSendUserName) = "<username>"
objConfig.fields.Item(cdoSendPassword) = "<password>"
objConfig.fields.Update

' Build and send the message
Set objMessage = New CDO.Message
Set objMessage.Configuration = objConfig
With objMessage
  .To = "<EMail to send to>"
  .From = "<EMail sender>"
  .Subject = "ToDos for Today"
  .TextBody = pmailBody
  .Send
End With

'Tear Down
Set objMessage = Nothing
Set objConfig = Nothing

End Sub

This code instantiates a few objects from Microsoft's Collaborative Data Object (CDO). The CDO API allows sending e-mail using SMTP. You have to include a reference to the Microsoft CDO Library. (The full title is "Microsoft CDO for Windows 2000 library", but it worked fine on my XP and Vista machines.) The best reference I could find on CDO is by John Peterson and can be found here.

First, the configuration for the SMTP-server that you want to use has to be set up (lines 7 to 15). In order for this code to work properly, you have to enter the information specific to your SMTP-server:
  • “Name” of the server (check with your email provider)
  • UserName of your account at the email provider
  • Password of your account
Once you’ve got the proper configuration, you can then build the message (lines 20 to 24). Interestingly, you can make this look as if the mail comes from any EMail-address (probably not even a valid one). After you’ve built the complete message, you can then send the email (line 25) to another EMail account (like my “work” address). Of course, this code does not do any error handling in case the server is not available (e.g. by buffering the message somewhere and then trying to resend at a later time). However, this has not been necessary for me (as we’re having a DSL line at home and are thus always connected to the Internet).

Monday, May 11, 2009

AutoFiltering with a Macro and a Keyboard Shortcut

In order to make the filtering easier to use, there is a keyboard shortcut Ctrl-Shift-A that calls a macro:

image

Here’s the code of the macro AutofilterUpdate that is called:

Sub AutofilterUpdate()
 If Selection.Cells.Count = 1 Then
     If Selection.Value <> "" Then
         Selection.AutoFilter
         Selection.AutoFilter
     End If
 End If
 Selection.AutoFilter Field:=ncRelevance, Criteria1:=scRelevanceCurrent
End Sub

First (lines 2 & 3), this code checks if the selection is within the list of items and only one cell is selected. (Otherwise, the call to Selection.AutoFilter will cause a strange runtime error.) This requires two ifs because I could not find a way to “short-circuit” the evaluation.

Then there are two calls to Selection.AutoFilter (lines 4 & 5). This extends the reach of the autofilter to newly added items in the list.

Finally, in line 8 the filter is set to only select those items that have a relevance (as calculated with the User Defined Function) of “current”. This simple code takes care of “removing” old items from your view without deleting them. Next: Sending an EMail.

Struggling with Access

Here’s one of the issues I’m currently working on. At work, we’ve “inherited” a non-trivial access application – maybe 20 tables, a bunch of forms, queries and reports. For now, we’re struck with the architecture, but we still have to add a bunch of functionality (new/changed tables and forms).

Getting accustomed to developing in Access was pretty quick – if you have a relatively good understanding of databases and application development, you can get some functionality working pretty quick. Some “interesting” issues, but nothing major so far.

The thing that has been bugging me (as I’m the one stuck with the development) is the “development cycle”. When you’re used to Enterprise Development in environments like SAP, this is a big shock. Development, QA and production environment? Forget about it, it’s three separate copies of the Access file. Moving functionality “up the chain”? Replace the production file with the qa file. Add a new table and a new form – no (simple) way to “move” just that part of the functionality. Push new functionality while people are using the application? Wait till everyone’s gone home … quite frustrating. And don’t get me started on multi-user handling, groups, rights …

We’ve tried to make things a bit more workable, but we’re still struggling:

  • Changing data and developing the Access file at the same time Without any tricks, when you want to work on the application you need exclusive access to the file, so that no one else can change data in the application’s database. Of course, that is not tolerable if you have more than a handful of users. One workaround is to “split” the Access file into a database or backend file (which contains just the tables and the data) and application file (which contains the queries and forms). The application file has “links” to the tables in the backend file. This seems to make working on code and data in parallel possible, it’s still a bit tricky to add new tables or change existing ones, but that’s okay for now.
  • Performance Especially after splitting, the performance has gone South – it is now on the verge of being unusable. All our files have to be on the network (which is not very fast, but shouldn’t be much of a problem as the file is just a few MBs). Also the variations of performance are very strange – and it has been degrading over the last days without any reasonable explanation.
  • Usage Reports Just one example: A form relies on a query for its data. You can change the name of the query, but the form does not pick up this change. So the next time it is called, you just get an error. We’ve got a couple of “Query 1” or “Macro 1” or “Makro 1” etc., but are afraid to delete or rename them because we don’t know where they are used. Any “compilers” for Access code that would show us these errors or help us in renaming?
  • I’ll be adding more over the next few days …

Maybe some of you have some ideas on what we could do to improve the user and developer “experience”? Let me know in the comments …

Calculating Relevance with a User Defined Function

Here’s the code to properly calculate the relevance of a todo list item:

Function Relevance(ByRef pStatus As String, ByRef pdLastUpdate As Date, ByRef pdDue As Date) As String
'calculates the relevance of an item based on the parameters
'possible results:
' - old (done and hasn't been touched for x days - constant ncUnchangedForDays)
' - future (open and due after the next y days - constant ncDueInDays
'           or someday)
' - current (open and due in the next y days - constant ncDueInDays
'           or closed with the last x days - constant ncUnchangedForDays)
Const ncDueInDays = 7
Const ncUnchangedForDays = 3

Select Case pStatus
   Case ToDo.scStatusClosed
       If pdLastUpdate < Now() - ncUnchangedForDays Then
           Relevance = scRelevanceOld
       Else
           Relevance = scRelevanceCurrent
       End If
   Case ToDo.scStatusSomeday
       Relevance = scRelevanceFuture
   Case Else 'at least "open"
       If pdDue < Now() + ncDueInDays Then
           Relevance = scRelevanceCurrent
       Else
           Relevance = scRelevanceFuture
       End If
End Select
End Function

This started as a simple Excel “If” formula, but that quickly got too complicated and very tricky to understand, so I switched to a UDF (user defined function). In order for the UDF to be available, it has to be defined in a module. Then you have the regular function wizard available:

Screenshot function wizard

It’s important to properly name the parameters, I have not yet found out a way to provide more detailed help to the wizard or to properly format the parameter values.

The code used the following constants to define status and relevance values:

Public Const scStatusOpen = "open"
Public Const scStatusClosed = "done"
Public Const scStatusWaiting = "waiting"
Public Const scStatusSomeday = "someday"

Public Const scRelevanceCurrent = "current"
Public Const scRelevanceOld = "old"
Public Const scRelevanceFuture = "future"

This makes it easier to change the language of the sheet (like I had to do for this blog post), and it also makes it easier to think about what needs to be done if you want to add some more detailed status or relevance values. Next: Updating the AutoFilter.

Thursday, May 7, 2009

Automatically updating Changed and setting up a new line

In order to make things easier, a couple of things are triggered when you work on a todo list item. The following code snippet achieves two things:

  • When you edit an existing todo item, it automatically updates the “lastChanged” column.
  • When you are working on a new line to create a new todo item, it sets up the line with the proper formatting and formulas.

Const ncNr = 1
Const ncText = 2
Const ncNew = 4
Const ncWhen = 5
Const ncStatus = 6
Const ncChanged = 7
Const ncStatus = 8
Const ncPrio = 9
Const ncComment = 10

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If (Target.Cells.Count = 1) And _
    (Target.Column <> ncChanged) And _
    (Target.Row <> 1) Then
    Cells(Target.Row, ncChanged) = Now
End If

If (Target.Cells.Count = 1) And _
    (Target.Column <> ncNew) And _
    (Target.Row <> 1) And _
    Cells(Target.Row, ncNew) = "" Then
        Cells(Target.Row, ncNew) = Now
        Cells(Target.Row, ncStatus) = ToDo.scStatusOpen
        'copy formulas to id and status columns
        Cells(Target.Row - 1, ncNr).Copy Destination:=Cells(Target.Row, ncNr)
        Cells(Target.Row - 1, ncRelevance).Copy Destination:=Cells(Target.Row, ncRelevance)
End If

End Sub

Here’s a brief explanation of this code:

  • Constants (lines 1 to 9) In order to reference the columns in the code, I’ve created constants so that when the layout of the sheet changes (adding/deleting a column), all is needed is a change to the constant.
  • Sub Workbook_SheetChange (line 11) In order for the code to be automatically called, you have to implement the sub Workbook_SheetChange. Whenever a cell in the workbook is changed, Excel calls this sub with two parameters denoting the sheet that was changed and the particular cells that were changed. (You can change more than one cell at a time, e.g. when selecting a range of cells and changing the cell’s background color.)
  • Updating the Changed-Column (lines 13-17) The if-statement checks that only one cell is selected (line 13), it’s not the changed-column (line 14 – this would trigger an endless loop), and we’re not in the first row (line 15). Typically, this means that the user has changed something in an existing todo item, for example the title, the due-date, the status or the comment. If these conditions apply, then the changed-column is updated to the current date/time (line 16).
  • Setting up a new line (lines 19-28) The if-statement performs similar checks as above, but also checks if the new column is still empty (i.e. that we have a new todo item). Then the new line is set up accordingly. The new column is set to Now (line 23) and the status is set to open (line 24). Then we have to set up the formulas in the id and relevance columns. The way this is done is by copying the cells from the line above. If these are set up correctly (e.g. id as a formula adding one to the value above), then the formulas work in the new row as well.

This way, you can just focus on working with the todo items without having to think about how to make the filtering work.

Next:Calculating the relevance of an item.

Tuesday, May 5, 2009

Coding Overview

There are three main parts of the code to make the ToDo List work:

  1. Workbook Code This is code that is directly related to the Workbook – mainly one sub Workbook_SheetChange that is triggered by Excel when a user changes something. This also contains a couple of constants referring to the column numbers and the code to build the text to be sent by mail.
  2. Module ToDo This module contains the code changing todo list items like calculating relevance, filtering and updating. It also contains constants for the status and relevance values. It changes some contents of the sheet, so it’s not completely decoupled from the sheet, but if required, the required changes are pretty minimal. This could be code in the workbook, but Excel insists that some types of code (like user defined functions) have to be in a module.
  3. Module Mail This is a pretty much self contained module to send an email over SMTP. Most code is a number of constant definitions to properly call the mail sending.

The code in each of these parts is described in the following posts:

Next: Automatically updating a changed item.

Monday, May 4, 2009

Using the Excel Sheet

Once you’ve downloaded the todo sheet, you can then start playing around with it. Here’s a couple of pointers:

Working on an existing item

If you have an existing item, you can enter some information to report what you have been working on. The “update column” (Column G) will be automatically updated with the current timestamp to show when the last changes were made.

Something I like to do at the start of the day is go through my open items and decide if I’m going to try to do them today or at a later date. If I change the “due column” to today, the status column will get a red background. If the item is open but due at a future date, the background will be yellow. Once you complete an item and change its status to “done”, the background color will be removed (i.e. “white”).

Adding a new item

If you have something new to do and want to add a new item, just start typing in the first empty line. Once you leave the cell, the rest of the line will be properly set up by the code. For example, the id and status columns will be set up, the new column will be set to the current date and the status (with its conditional formatting) will be set to “open”. You can just enter the information you want to capture.

Filtering

Once you’ve completed the items for a while (the default is three days, but you can change that in the code), there is no longer a need to show this item. The sheet uses Excel’s Autofilter to display just the items that are still relevant. For the keyboard users, there is a keyboard shortcut Ctrl-Shift-A (for “Autofilter”) to automatically update the autofilter and only display the relevant items (i.e. those with a value in the Relevance column of “current”). Another feature is that items that I plan to do in the further future (next month, next year, …) get a relevance of “future” and aren’t displayed until the due date comes nearer.

Send Mail

The sheet is just an Excel sheet, so there is not much “web-type” functionality. As I’m mainly working at home (with my home todos) or at work (with the work todos), this has not been much of a problem for me. If I need my home todos at work, I can send all items due today in an email by hitting the keyboard shortcut Ctrl-Shift-M (for “Mail”).

Even though there is not much functionality in the sheet, I have found this sheet to be very useful in keeping tracks of what needs to be done. I have added some little things over time, but the code has been very stable in the last years. I hope you’ll like the sheet as well .. let me know if you have ideas for further improvements.

Next: Coding Overview.

Saturday, May 2, 2009

First look at the Excel ToDo Sheet

This is the (almost) empty Excel sheet you can download:

image

Here's a rough overview of the sheet:

  • column A : ID a unique identifier for each entry
  • column B: What a short title of the todo item
  • column C: Tag a simple way of grouping related todo items (e.g. project A/B/C, phone, email, private) - for simple use cases this column can be hidden
  • column D: New timestamp the item was created
  • column E: Due day this item should be done or worked on
  • column F: Status status of this item, mainly open or done. This column has a Conditional Formatting, so you can easily see today's items (red) or those that are still open (yellow).
  • column G: Update timestamp this item was last updated. This column is automatically updated by the sheet, see another post.
  • column H: Relevance indicates if this item is still relevant and should be displayed when filtered
  • column I: Priority priority of the todo item - usually hidden because I don't work with priorities (similar to GTD). This is a feature I thought I would need at the start, but which turned out were not very useful and just ended up using real estate space.
  • column J: Comments just a big column to let you enter any comments to the item, like stuff you've done, who you're waiting for etc.
Column H (Relevance) can have the following values:
  • Old The item has been closed for more than a few days
  • Future The item is open but with a due date more than a couple of days in the future
  • Current All other items. This includes some closed items as well, which is great to look back on the last week and see the items you've been working on.

Based on relevance we can then use a filter to just display the items that are current. So there is no need to delete old, completed items - rather you can always go back to stuff you've done in the past and get back to your notes.

Next: Using the Excel Sheet.

Friday, May 1, 2009

Excel ToDo (with VBA)

For the longest time I have been using my own ToDo List. I started with a simple Excel table and added features over time. You can find an Excel 2003 version here. A lot of it depends on some relatively simple VBA macros, but it's gotten quite powerful over time.

Here are some basic thoughts behind what I have now:

  • Excel is almost universally installed on any machine. As I am working as a consultant and am usually on-site at a customer and have to use the machine I was provided with, a custom program was not an option.
  • There may be some good online choices by now, but the beginnings of my sheet are much older than prevalent internet access, and using the internet is tricky at most customers. For example, some have strange browser versions, blocked sites, inability to add plug-ins, lack of rights for code execution etc.
  • The style of list has been influenced by David Allen's GTD (Getting Things Done) methodology (even if I didn't know that when I started). So I tend to have a lot of "small" todos (like call person x, small coding tasks), rather than humongous items (implement new data warehouse) that follow me around for a long time.
  • My own ToDo list gives me the ultimate flexibility. When I want some feature, I can add it. Usually, this is not very critical to using the sheet, so I can add the things in on my own schedule .. and I enjoy playing with code. So it's a little fun thing for me which gives me quite a bit of satisfaction.

If you enjoy tinkering with code as well, this series of posts (tagged ExcelToDo) might be for you. If you just want to use the sheet, that's fine with me as well (but remember that it doesn't come with any warranty).

Here's a short overview of the posts describing the Excel ToDo list:

The code is described in the following posts: Next: First look at the Excel Sheet.

Friday, April 24, 2009

Deutsch oder English

Ich bin ein Deutscher und möchte auch in absehbarer Zeit in Deutschland arbeiten, dennoch habe ich mich nach einigem Nachdenken dafür entschieden, mein Programmier-Blog in Englisch zu schreiben. Dabei haben folgende Überlegungen eine Rolle gespielt:
  • Mit Englisch kann man einen größeren Kreis an Lesern erreichen. Damit kann man auch einen größeren Kreis von Personen in Diskussionen einbinden und deren Feedback erhalten.
  • Englisch ist die „Lingua Franca“ in Entwicklerkreisen. Die meisten Referenzen sind in Englisch, die meisten Blogs sind in Englisch. Englisch ist die natürliche Wahl für ein Entwickler-Blog.
  • Ich bekomme eine weitere Gelegenheit, mein Englisch zu üben und meine Sprachkenntnisse auf dem Laufenden zu halten.
  • Vielleicht ergeben sich durch ein englisches Blog neue, interessante Kontakte zu Entwicklern aus anderen Ländern.
Sollten sich Verständnisfragen zu Blog-Posts ergeben, kann ich auch gerne Erläuterungen in Deutsch geben. Einfach eine Email an SmarterCoding@SmarterSoftware.de senden oder einen deutsch-sprachigen Kommentar zu einem Blog-Post einstellen. Ich würde mich auch über Feedback zu meiner Sprachwahl freuen.