Source for the Podcast Generator




Over the last few years I've found it genuinely life-changing to listen to great sermons from various podcasts. When I took on a new role at church, I wanted to find an easy and persistent way to share sermons from around the web with other people - our congregation, a mentoree and friends. I couldn't find anything that suited my needs exactly, so I decided to create my own.

What does it do?

It allows you to fill your spreadsheet with details about MP3 files you've found on the web (titles, descriptions, links, topic tags, etc.) and turns them into a complete website (of static HTML files) and a set of podcast (XML) feeds which you can upload via FTP to your web host to create a public website like this one (noting that you also need to upload the 'components' folder - see The Source for more information). The website includes four different types of pages:

You can also:

Why Visual Basic and Excel?

Because I know Visual Basic and didn't have time to learn the finer points of Python to make a web application. The downside of using Excel is that only one person can maintain it, but I've realised that this is a useful constraint for two reasons:

Design (or lack thereof)

Make no mistakes about it, this code is a dodgy hack. I'm a self-taught programmer who doesn't comment as much as he should. Worse, I haven't had the time to optimise the code to the extent I wanted to - I don't reuse code as much as I should, and for speed at runtime I probably should have sucked the entire worksheet into an array rather than keep calling worksheet references. But I didn't. It works, doesn't take a painfully long time to build (~30s to build 670 HTML and XML files from 275 talks on my laptop, and it doesn't seem to have a big-N problem) and I don't have time to optimise it further. (Any tips for improving the code are, however, always welcome).

The Source


This source is licensed under a GN licence - Go Nuts. It's yours - do with it what you will.

The source

This is the Excel spreadsheet (which is the latest copy of my spreadsheet, containing all of the talks that appear on my podcast website.

You also need to download this zipped folder, unzip it and FTP it to the root directory of your podcasting system (i.e. put the 'Components' folder in the same directory as the files generated by the Excel spreadsheet). This zipped folder contains:

  1. CSS files from Twitter Bootstrap plus another CSS file I made (which calls Bootstrap code),
  2. The RSS, iTunes & favicon images,
  3. PHP code that takes keywords entered into the search bar and calls the Sphider search engine if you choose to install it in your site. (NOTE: You will need to change the location path in the gosearch.php file to suit your website or your search query will go to the search engine on my site!), and
  4. A flash audio player (which is not needed if you use the HTML5 audio player option in the spreadsheet).

Basic operation (Getting started, etc.)

  1. Open the Excel spreadsheet, enabling macros if necessary (Excel will likely disable them by default),
  2. Go to the 'Settings' tab and change the settings to suit your website,
  3. Add or remove talks (information about talks) as desired,
  4. Press the [Create HTML & XML Files] button,
  5. FTP the files to your server (to the same directory as your value for the 'Website URI' setting on the settings tab), and
  6. Validate your 'all episodes' xml feed (to check there's nothing in your item descriptions that feed readers can't parse) using the link at the bottom of bytopic.htm.

Other site components

Other components that add additional functionality to the site are as follows:

Search engine

I'm no fan of Google and their desire to track everyone on the web so I haven't used their search engine in my implementation (although you could easily modify the code to do so). I've used the Sphider search engine which is a free download and can be installed on your site.

Note, however, that Sphider doesn't reindex itself - you need to set a Cron job (via cPanel at your webhost) to schedule reindexing. The command that works on my host is as follows:

    cd /home/[hosting account name]/public_html/resources/search/admin; php spider.php -all

Note, however, that I changed the Sphider directory name to 'search' - if you used the default Sphider parameters the correct command would be:

    cd /home/[hosting account name]/public_html/resources/sphider/admin; php spider.php -all

Of course, the file path for your host will be different to mine and can be discovered using tools in your cPanel, such as File Manager.

If that Cron command doesn't work for you, there's a discussion on the Sphider forums which might be helpful to you.

To turn off the search functionality on web pages (and remove the search bar at the top of each page), all you need to do is go to the Excel spreadsheet and delete the contents of the cell below the cell which says:

     Full or relative URI to PHP redirect file for search function (to remove search option, leave blank).

Wiki (for Notes links on web pages)

To help people share notes on sermons, you can instal a wiki system from MediaWiki on your webserver and, by setting the right path in the Excel spreadsheet (under the 'Wiki location' cell), automatically create a link to a wiki page for every talk on your website (like this page). A handy feature of the wiki is that even if a wiki article doesn't exist for a particular talk, it will allow users to create a new page at the link specified by the Notes link on the podcast website.

Note that evil spambots will quickly find your Wiki and fill it with thousands of fake wiki articles if you allow anyone to create a web page without a username and password. The simplest solution I could come up with was to lock down the wiki and make a username and password (not your administrator username and password!) visible to humans using the site in the site image (see this page, noting the image at top left). To lock down the wiki (to prevent people from creating an article without a username and password, and to prevent anyone from creating a new username and password, which the spambots do), add the following code (and comments) to the end of your LocalSettings.php file in the Wiki root directory:

         # Prevent new user registrations except by sysops
         $wgGroupPermissions['*']['createaccount'] = false;

         # Disable anonymous editing
         $wgGroupPermissions['*']['edit'] = false;

         # Now that the wiki is locked down to spam, turn off the nofollow attribute so Sphider will follow links
         $wgNoFollowLinks = false;

How to ...

Create a course podcast

A course podcast (example) is much like a topic podcast (example) except that:

  1. a podcast is a list of talks ordered by date - a course allows you to nominate a listening order (1-N),
  2. The HTML page layout for courses is different to that for podcasts, encouraging people to subscribe to the course podcast rather than pick and choose talks on that page, and
  3. the course podcast is independent of topics and authors, and can contain any talk that appears in the spreadsheet.
The idea behind course podcasts is that they'll be useful in directing people in self-study (e.g. encouraging newcomers to hear a number of talks in a particular order) and to collect talks from events such as conferences.

Courses are created on two sheets of the spreadsheet. On the 'Item' sheet, courses are managed on the right-hand side of the spreadsheet, the section marked in blue (starting at column 'N' at the time of writing this).

  1. Pick the leftmost blank (blue) column and in Row 3 type the name of the course e.g. 2013 Annual Conference.
  2. Scroll down or search to find the talks (that are already in the spreadsheet) that you'd like to appear in the course, giving them numbers 1, 2, 3, etc. - sequentially with no overlaps or gaps.

Then, on the 'Courses' sheet:

  1. Pick the topmost blank row and enter the same name as you gave the course on the Item sheet.
  2. If that's a long name you can change the filename by entering the filename suffix (e.g. entering ac2013 will create a HTML filename course_ac2013.htm and feed name course_ac2013.xml).
  3. You can add a website link and a description as you see fit.
Then return to the Item sheet, click on the [Create HTML and XML files] button, review the files outputted by the spreadsheet and FTP them to your site.


Are churches happy for you to do this with their talks?
Yes, it would seem so. Originally I thought I'd have to host talks myself - the idea of just pointing to the talks already hosted on their servers hadn't occurred to me - and I contacted a few churches for permission to rehost their talks on my server. All of them except one said no - they wanted me to point to the talk already on their server. It was basically their idea to do the podcast system this way (and it was a better idea than mine). Besides, the talks are on the public web so linking to them by any means is to be expected.

What's the difference between the 'Item Title' and the 'Item Subtitle' in the Excel spreadsheet?
In a bid to keep URIs short, the Item Title file is used to create the URI, page title and headers of the talk's web page, whereas the Item Subtitle appears only in the page title and text. Note that the spreadsheet does not check that the Item Title is unique, because it's reasonable for different authors to give talks the same name. Instead the macro adds the author's initials as a suffix in the URI to differentiate between homonymic titles. Also, the code truncates URIs (where the Item Title is long) except where the title contains a number, so that talks in a series (e.g. Praying in the Holy Spirit 1, Praying in the Holy Spirit 2, etc.) can be differentiated (where otherwise the distinguishing characteristic, the number, might be truncated). The bottom line for you as a user is:

Do I need to submit these feeds to the iTunes store?
No. Users can subscribe by clicking on the iTunes link or taking the podcast feed URI and pasting it into iTunes (in Windows version of iTunes, it's under [File] then [Subscribe to Podcast...].)]. Note that iPads, iPhones can't subscribe directly from the link, however, although there might be a way to make it work (does anyone know?).

What version is this? Where are the release notes? Where is the user forum?
Version? Release notes? User forum? Bah. As far as I know I'm the only person on the planet using this. But if the global user base increases beyond 2 - or maybe 3 - I can do something that resembles version control, release notes (of a kind) and maybe a forum. Contact me to put your order in.

Known Problems / Gotchas / To-Do

Check your feed validity
At the bottom of the bytopic.htm page is an 'Admin' section that allows you to validate your podcast feed. This is a good habit to get into as it's easy to find dodgy characters in your description (that you've cut and paste from a website) or to put the wrong info in the wrong field (e.g. a URL into the duration column), any of which can invalidate your feed. Validating the 'All episodes' feed will catch most problems, but you can check others individually if you're having problems. Feel free to contact me if you have any persistent problems.

Channel image
The podcast generator correctly uses the channel image tag to specify a channel (podcast) image for iTunes and other feed readers. While Firefox picks it up (and your browser might as well), iTunes doesn't - it picks up images from episodes. I assume that iTunes is recognising the GUIDs (MP3 links) found other podcasts found in its database and using those episode / channel images instead.

Check the MP3 URI
Some sites track MP3 downloads from their site by using a URI which goes something like this: My software will pass it through to the feed but iTunes doesn't like it and won't display the podcast episode. To make it work you need to strip the tracking code away and list only the MP3. This isn't entirely polite, but given that I'm also pointing back to the author's website (and my audience is much smaller than the author's, meaning that their stats won't be skewed too badly) I don't think it's a serious breach of etiquette.

iPads, iPhones ...
iPads can't use the podcast subscription links - Apple seems to have locked them down so that you have to subscribe to podcasts through the iTunes store. Nor can they use the Flash audio player. They can, however, see the HTML5 audio player via Safari browser.

HTML5 audio player
Support for HTML5 audio in browsers is inconsistent at best. Fortunately v21 of Firefox now supports MP3 files in their HTML5 player, as do Chrome and Safari. Internet Explorer doesn't seem to work, but what are you using Internet Explorer for? Get thee Firefox.

Alphabetical order of topics and authors
I couldn't find some Visual Basic code that put arrays into alphabetical order so I created my own. For the sake of coding time it only sorts on the first two letters of the string, creating some misordering. It's on my to-do list.

How publishing dates are set
The macro sets the publishing date automatically, for any row in which the 'Date (Auto-Generate) cell value is not have a valid date. This means you can manually set dates or overwrite any auto-generated date of your choice and the macro will not overwrite your date (provided that you gave it a valid date). Note, however, that:

Stuck? Questions?

Feel free to contact me via my personal site.


Sermon browser, a plug-in for Wordpress.