I build eLearning courses at work. We use various formats and technologies, but some of our recent course-ware is HTML based with LOTS of embedded video and flash .swf files.  I work on a several new courses at a time so building them out can get tedious. We host our videos outside of the root location of the HTML course and authenticate the user for access, so it's not as simple as just using "Insert" flash presentations or video objects in Dreamweaver. I thought - how could I make this a bit easier to create these code blocks? Excel!

Using the CONCATENATE function in Excel, we can build lengthy code blocks that repeat with some dynamic content that we can change easily, and just copy the code into our HTML pages.

Here's the project we're working on - Excel_Code_Automation for download.

First, you need to look at your required code and divide things out by what is dynamic and what is static. For some of my uses (flash embeds in particular) I ended up with 8-12 columns and 4 dynamic fields.

For this example, we are just going to create a simple anchor link that we can rebuild by just changing the URL, title, and Text. So our Excel sheet will create multiple links that look like this:

<p><a href="http://siteURL" title="Site Title" target="_blank">Link Text</a></p>

And all we will have to do is type the URL, Title, and Text into Excel columns, and output the appropriate link.  Obviously this is easier to type than to configure anything (or to use the WYSIWYG editor in your HTML tool), but this is for example purposes only.  This works well when you have lengthy embed codes and other more complex code creation that repeats throughout a project.

Let's begin!

Step 1: Divide out your code

So we start by breaking this code into chunks.  We are going to concatenate (combine) our columns into one text string in the end, attaching static code blocks with dynamic code blocks.  Our dynamic code blocks in this case are the site URL, the link title, and the link text.

IMPORTANT: First, highlight the row, right click, "Format Cells". Change the format to "Text". This will ensure that none of our code is interpreted by Excel as anything other than text.

We'll take each piece and build the columns out:

Column A - STATIC:

<p><a href="

Column B - DYNAMIC:

http://siteURL

Column C - STATIC:

" title="

Column D - DYNAMIC

Site Title

Column E - STATIC

" target="_blank">

Column F - DYNAMIC

Link Text

Column G - STATIC

</a></p>

If you have duplicate dynamic code blocks, for example a URL or file name in a flash embed, you don't have to type this out.  Just use the "=(A1)" code to repeat the text from the data column.  If you placed "=(A1)" in box D1, this will just duplicate whatever was entered in A1.  Keep in mind you will need to format the cells for "general" instead of "text" in order to use the function.

Step 2: Concatenate Columns

What you end up with is a series of columns that when strung together make up the entire code block.  In our Column H, we are going to use the CONCATENATE function of Excel to bring these all together as one piece of code.  First, change the cell format back from "Text" to "General" just for this column by highlighting the "H" and selecting the column.  Right click, "Format Cells".  This will allow function use in this column.

Under the insert function bar (looks like "fx" at the top of Excel), type in the following:

=CONCATENATE(A1,B1,C1,D1,E1,F1,G1)

This is just taking the contents from cells A1 through G1, combining them into a single string, and then putting that text output into column H.  We now have our completed anchor creation string!

Step 3: Clean it up!  

At this time I do a little cleanup.  I add some headers in, and hide all of the static code columns since we won't be using those.

Step 4: Duplicate your work across rows

Highlight Row and Drag down to duplicate row and coding.

We can now highlight that entire row and drag the pull box down to duplicate that code across multiple rows.  This will recreate our code blocks and automatically adjust our CONCATENATE code to associate with the row it's currently in.

Step 5: Modify the dynamic code parts to create new code.

We can now edit our dynamic fields to automatically create the URL code in column H by entering in new dynamic content in columns B, D, and F.

Our updated dynamic content and populated code in column H.

Step 6: Copy and paste into your HTML pages.

After all that work, we now have code blocks in column H that we can copy and paste directly into our HTML Pages!

Have a better way to do something like this?  I'd love to know!