Earlier this football season, myself and many other photographers were caught by surprise when two different website known for their Photo Mechanic code replacement tool either stopped updating or started charging for their services. Code replacement is a major time saver. I’ll take a minute to explain it for people who may never have used it before, but I suspect anyone looking at this is probably already familiar with it.
Anyway, for most newspapers/wire services/websites/magazines you have to caption your photos with very specific information about who appears in your photos. During sporting events, where you are on deadline and rosters can include up to 80 or so people per team, this can really slow down your work flow to constantly type in “Team-name Mascot-name player’s-position Player’s name (#)” for as many as 50-60 photos. Code replacement lets you do all the groundwork once by formatting a roster a specific way and letting you just type something like \g11tpn\ to generate the following code, “Georgia Bulldogs quarterback Aaron Murray (11).” Needless to say, it saves a lot of time over hunting for each player as you get to their photo. If you know their number and they are on an official team roster, you can save quite a bit of time.
As mentioned though, things changed and for me neither of the previous sites were options anymore. After a few weeks of manually creating rosters like I had been for high school football last season for every sport I was shooting, I thought there had to be a better way. Then it hit me and I felt really really stupid. So, in the below steps I outline how to create a fully functional code replacement roster in as quickly as 5 minutes (or possibly faster) using Microsoft Office (Word & Excel) along with a basic text editing program like Notepad.
For the purpose of this how-to, I document how I built my code replacement for the Milwaukee Bucks basketball team in preparation with their game against the Atlanta Hawks.
First things first, you need to find a roster. If possible, I always try to find one that’s online and likely to be up to date. In this case, the official NBA website has every team’s roster and is more than likely already up to date (but you should get an official game roster anyway to double check just in case.)
I copy all the information for the roster table on the website and drop it into a basic text editor, like Notepad since I use Windows, to erase any formatting irregularities. It also does a pretty good job of saving column information which is important to the next step.
Immediately after pasting the information into Notepad, I copy it again and paste it into Microsoft Excel. Even though the columns looked off in Notepad due to the length of certain players’ names, you’ll notice as soon as I posted it in Excel everything lined up in the correct place.
A lot of that information I got from the NBA roster is unnecessary though. I remove it just to simplify things, but you could leave it if you wanted. Also, notice that I left in the “NUM,” “PLAYER,” and “POS” tags on the top of each column. Those are important for later when the mail-merge uses them to name the data in each column.
Also, some rosters abbreviate position names into a single letter or two. You can manually replace them or select the column and do a “Find and Replace” where you should be able to type something like , Find “C” and replace with “center.” This step should take you the longest, which is to say really isn’t terribly long.
Some people may not care about this, but I’m kind of an organizational freak and I like things to be in numerical order. The NBA rosters are sorted by the player’s last name, but I’d rather have it by number so I use the “Sort & Filter” option in Excel after highlighting all the players’ numbers to reorganize the list. It’ll ask you if you want to expand the selection, so make sure you say yes or it will reorganize the numbers without resorting the corresponding player names and that would be bad.
At this point, your work in Excel is done. Save your file to your desktop or somewhere else you can grab it easily.
Next, you will need to open up Word and find the Mail Merge option. Using the wizard is the easiest way I’ve found, but power users may know something easier.
Once you enter the wizard, you’ll see these or similar screens depending on your version of Word. In the first screen, select the Directory option, this will let you format everything exactly the way you want on a normal text document. Then instruct Word to “Use the current document” for the setup, then finally tell it to “Use an existing list.” Click the “Browse…” button that pops up below the options and select your Excel file that you saved earlier.
After selecting your Excel file, it will ask you which sheet you would like to use. You can either do each team in one sheet (in which case you will need to add a column with their team name formatted how you want it, or you could do it in separate sheets or separate files altogether. Also, this is where that first row from the Excel file comes up again, see the checkbox?
After hitting OK, you’ll see this window. Every player’s entry should already be checked, so you can just hit the OK button.
After your Excel roster has been imported, hit the “More Items…” option to pop up the following window. You’ll see those three labels from earlier as options. Go ahead and select each one and insert it into the Word file.
Here is a very basic example of what you’ll need to do. When you insert one of those above column names, it will show up with a << and >> around it. So, using normal text insert the things that you want every variation of that key to have.
Like above, an “m” before the <<Num_>> tag and the tpn after will generate something like m17tpn. I use tpn to signify which variables are being inserted afterwards (like in the Stonepix and O’Watson) code replacements to show that its the code for generating the player’s team, position and number. You can set this part up any way you like in any order you want, just remember it so when it comes time to use it, you know what it is.
You can do multiple variations per run, these are the only three variations I use so after each individual entry, I shift+enter to put in the next one. Also, make sure you shift+enter or hard return after your last entry otherwise the next set will be put immediately after instead of on the next line.
After you are done editing your templates and go to the next screen, it will preview your template with the first data set from your excel file. Look it over to make sure everything is correct.
Notice on the last entry, I forgot to put a space between the player’s position and his name. So if I were to go ahead and render the entire set, this would occur to every player’s name on that third line. If you’re set, move on to the next step of the wizard.
If only one set shows up, DON’T PANIC. You didn’t do anything wrong. I’m not sure what causes the quirk, but to see all the data you need to be the correct viewing mode which is the one all the way on the right that doesn’t show how everything fits on a printed page. Click that and everything should pop up. Copy all that freshly rendered code replacement goodness and drop it back into Notepad.
Everything should look perfectly formatted (except for the above mentioned error I left in to show what happens) at this point and ready to be saved. Hopefully this will save everyone some time. If you know all the steps, you can seriously be out the door with this thing in 3 minutes.