So I have a customer that needed a registration page for their E-University. This consisted of about 500 courses in various categories.
I got the ~great idea to implement an AJAX Accordion to contain the categories. Within each accordion pane, I needed separate tables to house the various sub-categories. So how do I get a list of courses, with course codes and hyperlinks into a properly formatted accordion control?
I thought about doing this manually (for about 1 second), then I decided to try writing a macro. I don’t have a ton of macro or VB experience, but this was the only thing I could think of.
Enter the reason for this post…
I searched all over the Internet for an easy way to export Excel to HTML. There were a few examples, but I found them really hard to follow, and most were too involved for my needs. (I thought)
Note: The material presented here was learned through trial and error. I am not a macro, VB, or VBA expert. I know there is a better way to go about this. Unfortunately, I couldn’t easily find it online, so I hacked this together. If you are an expert, I would love for you to comment so we can have an optimized version of the code we should be using. In the end, this code worked great for me. You might not have the same results, so use at your own risk.
Please download the code for the macro here: http://www.businesshut.com/Excel_To_HTML_Macros.txt
Download the sample spreadsheet here: http://www.businesshut.com/E-University_Macros.xls
The output text file will be similar to this: http://www.businesshut.com/ExcelToHTML.txt
The resulting .aspx file will be made available soon.
I recommend following along with the example files.
Now, for a tour of the sub-routines…
First, you’ll see “getHyperlinks”
In column C, the Course#’s are all hyperlinks. However, I needed the hyperlink separated from the Course# in my html.
getHyperlinks starts in cell C4. (you’ll change this per your situation.)
We have a “Do Until” loop that will iterate for the number of rows you enter here. If you enter more rows than you need, the loop will simply write blank values to the cells in column E.
If a hyperlink is NOT found in the cell, we use a blank string.
If a hyperlink IS found, we use Selection.Hyperlinks(1).Address to pull out the URL.
We then use Selection.Offset(0,2).Select to move 0 cells down and 2 cells right, and we write the URL there.
Add 1 to i and repeat until we get to 400.
Great, now I have all of the URLs as text in their own column.
On to exporting this information as HTML…
Unfortunately, much of this code was pulled from random sources on the web, so I can’t even explain how every line works. However, between this post, and the inline comments, you should be able to figure out how to modify it for your own uses.
Line 36: The “MyFile” variable dictates where the resulting text file will be saved.
We create “fnum” as a FreeFile(), and we’ll use
Print #fnum, “string text”
to write each line to the file specified in the MyFile variable
Line 60: myRow was hard coded to 2 because that’s where my data began. You may need to change this.
Now we start creating the accordion panes.
***Note: This macro only creates the accordion PANES. Create an accordion first, (another topic), and just use the code generated by the macro as your accordion panes. ***
Line 63: We get the “courseGroup” by pulling the value from the row specified by “myRow” and column 1 of the current sheet.
*One last time I’ll mention that when I specify cells, that you may need to change them per the format of your spreadsheet.
Line 66: Now we start writing to the file.
This section creates the header information for the AccordionPane.
Everything in quotes is added literally, and the variables are inserted where necessary.
Line 74: Add 1 to myRow to move down to the Course Category and pull that value into our code.
This section labels this part within the AccordionPane as a new category and starts the table that will contain the course information.
Line 91: This is the main loop. (1000 is an arbitrary number. However, it is referenced later in the loop.)
Now that we have the AccordionPane and the category information, we’ll start looping through the courses.
Back up on line 48, we set cl = 1
This was done to help me group various controls and reference them easily. I was having trouble using “FindControl” dynamically with a MasterPage and an AJAX Accordion control, so this was my solution. I might have been able to use a Checkbox Array, but I couldn’t get the formatting I wanted. Each group of controls is simply named with the same number on the end. So if someone clicks “chk1″, which is the checkbox to select the course, I know that “lblchk1″ will contain the corresponding information for that course. Yes, crude but effective.
Line 125: (By “blank row” I mean that column B is blank.)
If we have 1 blank row, it indicates a new category.
If we have 2 in a row, it indicates a new Accordion Pane.
And if we have 3 blank rows, we’re all done.
:: 1 blank row::
Line 129 skips down another row and checks to see if there’s a second blank row, but we’ll assume it’s not.
Jump to the Else on line 182.
We step back a row to get the information that we skipped while checking the next row.
Line 184 ends the table for the previous category.
Line 187 gets the new category info.
Lines 188-199 set up the title and table for the new category.
Line 207 : Loop again from Line 91 until we hit 1000 iterations.
::If we find a second blank row in the IF statement on line 129::
Add another row to myRow and check to see if we have 3 blank rows in a row.
*We’re assuming we only have 2.
Jump to the Else on line 144.
Now we go back 2 rows to get the heading information that we skipped.
Then we end the table for the previous section, and end the Content section of the AccordionPane. We also end the AccordionPane and start the header information of our new one.
Line 157 begins the new AccordionPane, and Line 167 begins the heading and table information for the first category within the new AccordionPane.
EndIf sends us to Line 207 : Loop again from Line 91 until we hit 1000 iterations.
:: 3 blank* rows:: (*Column B is blank.)
Line 135 : End the table for the current category.
Line 138 : End the Content section of the Accordion Pane.
Line 140 : End the Accordion Pane.
Line 143 : Set i to 1000 so we don’t loop again.
Line 213 : Close the file for writing.
End Sub
~~~
Remember when I said I thought the other examples were “too involved” for my needs? I was wrong.
I realize this example is a bit involved, and it’s very specific to my project. If you want to see a simplified example, just send me a comment, and I’d be happy to set one up.
I look forward to everyone’s thoughts on how I went about this solution, and I’m really interested to see how you would have done it.
- Jason Green
