Author Archives: Stefan Waldherr

About Stefan Waldherr


Set the same text styles in all views for MS Project

Running the following macro from your gantt-like views will standardize the view to use identical text styles.

' works only for gantt like charts
Sub swaMSPSetTextStyles()
    If ActiveProject.Views.Item(ActiveProject.CurrentView).Type = 0 Then
        TextStyles Item:=pjAll, Size:="8", Bold:=False, Italic:=False, Color:=pjBlack
        TextStyles Item:=pjNoncritical, Size:="8", Bold:=False, Italic:=False, Color:=pjBlack
        TextStyles Item:=pjGanttExternalTask, Size:="8", Bold:=False, Italic:=False, Color:=pjGray
        TextStyles Item:=pjMarked, Size:="8", Bold:=False, Italic:=True, Color:=pjNavy
        TextStyles Item:=pjTaskRowColumnTitles, Size:="8", Bold:=True, Italic:=False, Color:=pjBlack
        TextStyles Item:=pjGanttMajorTimescale, Size:="8", Bold:=True, Italic:=False, Color:=pjBlack
    End If
End Sub

How to add VBA code to your computer

First of all, all VBA examples on this website use MS Excel 2003 2010 and/or MS Project 2003 2010. Most of the stuff may run with later versions, YMMV. Also, keep an eye on formulas in Excel: you may need to replace each colon with a comma (or tweak your regional settings in Windows).

Add code to MS Excel

  • I store all my macros in a central place (personal.xls), so that I can use them in all spreadsheets.
  • On my Win7 box, the file is located in %APPDATA%\Microsoft\Excel\XLSTART
  • Read, how to add macros to your personal.xls.
  • Hide personal.xls when you’re not working on your macros (via Window | Hide).

Add code to MS Project

  • I store all my macros in a central place (global.mpt), so that I can use them in all projects.
  • On my Win7 box, the file is located in %APPDATA%\Microsoft\MS Project\11\1033
  • You can move macros and other settings between different project files and global.mpt using MS Project’s organizer function, see Tools | Organizer.

Don’t forget to backup both files on a regular basis.

How to export your XBMC library to CSV

I have set up all my movies in XBMC and wanted to clean the library from movies which I’ll likely never watch. My idea is to export the library to plain text CSV format, read the file with Excel, filter for crappy IMDB ratings and remove the movies files. Here is how:

  1. Export your library to a single file from XBMC. The file likely ends up in C:\Documents and Settings\Administrator\Application Data\XBMC\userdata and is called videodb.xml.
  2. Convert the XML file to something Excel can read.
    1. Download the XSL file which sets the conversion format.
    2. Convert the file with a command line utility from Microsoft. Open a command window and use

      msxsl.exe videodb.xml convert.xsl > movies.csv

  3. Read movies.csv with Excel and analyze to taste.
  4. You’re done.

Creating a movie database

I have a ton of movies in a number of different formats (DVD folders, avi, mpg) and I have no idea whether they are all worth watching. So I was looking for a program to scan through the files, add the rating from IMDB and help my find the good, the bad and the ugly movies. Here is what I did …

  1. Install XBMC
  2. “Add source”- the root folder on my external hard drive
  3. XBMC can now scan all files and folders and retrieve IMDB’s movie information. Problem: some of the filenames are so cryptic, that XBMC cannot find them. So I had to manually correct the filenames. Ideally, filenames adhere to “Titel (Year)”. Helpful was a little tool that renames files with the help of regular expressions. A number of filenames can be easily stripped of unnecessary information with simple \[Nero\] or \(DE\).
  4. Set the source format of the root folder
    1. Set content to “Movies”
    2. Always choose “” as scraper. If you pick more than one scraper (, etc.), the ratings get mixed up.
  5. If XBMC cannot find a movie (the cover icon has not been replaced), go back to step #3. Maybe add “part 1”, “part 3” or change the scraper from to Don’t forget to re-scan the source.
  6. Your’re done.