I’ve seen a lot of articles on the Internet explaining how to export a page to Excel using VBScript. Unfortunately, they all seem to have problems in their methodology.
Many of the articles are helpful, but they don’t seem to include all of the information you need in order to export HTML content successfully from a VBScript page into an Excel document.
I’ve finally found a method to successfully export my data to Excel, and I hope it will help some of you, too.
First, we need to set the header of our document as an Excel document. We do that with the following code:
<% Response.ContentType = "application/vnd.ms-excel" Response.AddHeader "Content-Disposition", "attachment; filename=Enquiry_List.xls" %>
The first line of that code tells your browser that the file is an Excel document. The second line of the code prints the header of the Excel document, and assigns a filename. You can use VBScript to automatically generate that filename if you want.
Now, we need to format our Excel document using some simple HTML and CSS tricks:
<html xmlns:x="urn:schemas-microsoft-com:office:excel">
<head>
<style>
<!--table
br {mso-data-placement:same-cell;}
tr {vertical-align:top;}
-->
</style>
</head>
<body>
The code above should be printed exactly as it is written above. Do not place that code inside VBScript tags.
Next, we need to print our data into an HTML table. We can do so by using code similar to:
<table>
<thead>
<tr>
<th>Test 1</th>
<th>Test 2</th>
<th>Test 3</th>
</tr>
</thead>
<tbody>
<tr>
<td><%="This is a single-line test"%></td>
<td><%="This is a test with breaks.<br />This is after the break"%></td>
<td><%=p2br("<p>This is a test with paragraphs.</p><p>This is the second paragraph.</p>")%></td>
</tr>
</tbody>
</table>
Notice that we can use VBScript to print whatever we want within our table cells. You can even use a loop to print sets of information into the table.
Within the code above, you may also notice that I am using a function called p2br to format my information. This is extremely important. The code for that function is:
Function p2br(strOutput) Set objRegExp = New Regexp objRegExp.IgnoreCase = True objRegExp.Global = True objRegExp.Pattern = "<p.*?>(.*?)</p>" strOutput = objRegExp.Replace(strOutput, "$1<br /><br />") p2br = strOutput Set objRegExp = Nothing End Function
That function converts all HTML paragraphs within your output into HTML line breaks instead (<br />).
A major issue you may encounter when exporting HTML into Excel is that, whenever Excel encounters an HTML line-break, it automatically starts a new cell below the beginning of your output, and then merges those cells together. This makes it impossible to sort your information, because your merged cells throughout the spreadsheet will not be “identically sized”.
Whenever Excel encounters a paragraph tag, it automatically creates a new blank cell below the first portion of your output, then creates another new cell below that and outputs the rest of your information. Again, this makes it impossible to sort the information in your new spreadsheet.
However, in the code we printed at the top of the page, we told Excel not to create a new cell when it encounters an HTML line-break. I have not been successful trying to tell Excel to do the same with paragraph tags. Therefore, I wrote the p2br function to convert all of my paragraph tags into two HTML line-breaks.
Once you have done that, Excel will see the HTML line-breaks in your output, and will simply start a new line within the cell it’s already working on, rather than creating a new cell.I tried a lot of different functions before I discovered the style definition mentioned above. I tried converting my line-breaks to CHR(10), CHAR(10), VbCrLf, VbLf, VbCr, etc. and nothing translated properly once it was pushed into Excel.
By using this method, you can successfully sort your information after exporting it to Excel.
Finally, you need to close your HTML/Excel document. You do so by simply adding the closing tags:
</body> </html>
Voila! You can now export your HTML table to Excel, even if you have <br /> and <p> tags inside your table cells. I hope that helps.
5 Responses
Excellent. Simple, clear and it works. Just what I needed, many thanks! :)
What do you do with the VB script?? I dont know any VB so I dont know where to place it and with what tabs…
I agree with Steve sort of. It would be really handy to have the code all in 1 block, but I’ll take what I can get. HUGE shortcut, thanks. It didn’t work the first try but I’m sure that’s related to the absent tags.
The following is part of a table of historical data that I would like to convert to an Excel Spreadsheet.
I have the Formatting, headers and three rows as examples of the data I’m looking at. The problem is there are 20 of these rows per page and upwards of 40-50 pages. Is there an easier way to have VB gather the information and send it to Excel or do I need to manually copy/paste all the data over and over again. Thanks for the help and being so well thought out on your explanations on this page!
NameMovingStatusTimeLaneStationaryAP
Timothy
Multis
Success
2010-07-10 04:25:22
1
Rooks
-133,749
One
MALAYA
Success
2010-07-10 04:25:22
1
-NONE-
style
Rogers
MALAYA
Success
2010-07-10 04:25:20
1
Thomps
-259,187
I was unable to get new lines in cells until I changed your function as follows