Exporting a range to HTML format

The example in this section demonstrates how to export a range of cells to an HTML file. An HTML file, as you might know, is simply a text file that contains special formatting tags that describe how the information will be presented in a browser.

Why not use Excel's File ^ Save as Web Page command? The procedure listed here has a distinct advantage: It does not produce bloated HTML code. For example, I used the ExportToHTML procedure to export a range of 70 cells. The file size was 2.6KB. Then I used Excel's File ^ Save as Web Page command to export the sheet. The result was 15.8KB — more than six times larger. But, on the other hand, the ExportToHTML procedure does not maintain all the cell formatting. In fact, the only formatting information that it produces is bold and italic. You'll find that this procedure has another serious limitation: It cannot handle merged cells.

You might want to use the ExportToHTML procedure, here in Listing 27-6, as the basis for additional customizations.

Listing 27-6: Exporting a Range of Cells to an HTML File

Sub ExportToHTML() ' Dim ws As Worksheet Dim Filename As Variant

Dim TDOpenTag As String, TDCloseTag As String

Dim CellContents As String

Dim Rng As Range

Dim r As Long, c As Integer

' Use the selected range of cells

Set Rng = Application.Intersect(ActiveSheet.UsedRange, Selection)

' Get a file name

Filename = Application.GetSaveAsFilename( _ InitialFileName:="myrange.htm", _ fileFilter:="HTML Files(*.htm), *.htm") If Filename = False Then Exit Sub

' Open the text file

Open Filename For Output As #1

Print #1, "<TABLE BORDER=1 CELLPADDING=3>"

' Loop through the cells

For r = 1 To Rng.Rows.Count Print #1, "<TR>" For c = 1 To Rng.Columns.Count

TDOpenTag = "<TD ALIGN=RIGHT>" TDCloseTag = "</TD>" If Rng.Cells(r, c).Font.Bold Then TDOpenTag = TDOpenTag & "<B>" TDCloseTag = "</B>" & TDCloseTag End If

If Rng.Cells(r, c).Font.Italic Then TDOpenTag = TDOpenTag & "<I>" TDCloseTag = "</I>" & TDCloseTag End If

CellContents = Rng.Cells(r, c).Text Print #1, TDOpenTag & CellContents & TDCloseTag Next c

Next r Close the Print #1,

Close the Close #1

file

' Tell the user

MsgBox Rng.Count & " cells exported to " & Filename End Sub

This example is available on the companion CD-ROM.

The procedure starts by determining the range to export. This is based on the intersection of the selected range and the used area of the worksheet. This ensures that entire rows or columns are not processed. Next, the user is prompted for a filename, and the text file is opened. The bulk of the work is done within two For-Next loops. The code generates the appropriate HTML tags and writes the information to the text file. Finally, the file is closed, and the user sees a summary message.

Figure 27-4 shows a range in a worksheet, and Figure 27-5 shows how it looks in a browser after being converted to HTML.

FE

X

A B

C

D

E

F

G

1

2

New Yol k

Los Angeles

Chicago

Tot.il

3

January

$3,813.00

(0,674 00

(15,174 00

(27661 00

4

February

J21.161.00

(611 00

(7,766 00

(29550 00

5

March

J21.097.00

(963 00

(10,357 00

(32417 00

G

April

J15.392.00

(19,794 00

(9,656 00

(45630 00

7

May

J12.858.00

(22,693.00

(8,696.00

(44,249.00

8

Jut ]e

J14,336.00

(1,619.00

(111.00

(16,266.00

g

July

J19.842.00

(354.00

(11,558.00

(31,754.00

10

August

J15,716.00

(2,663.00

(20,739.00

(38,458.00

11

September

»4,219.00

(2,976.00

(8,686.00

(15,275.00

12

October

J12,338.00

(20,496.00

(15,769.00

(48,597.00

13

November

J18,693.00

(5,389.00

(14,664.00

(38,746.00

14

December

J17,141.00

(13,662.00

(12,926.00

(43,663.00

15

Total

J176,606.00

(99,568.00

J135,766.00

(411,880.00

16

17

jjr

► MlVsh,,»/

-I

I

Figure 27-4: A worksheet range, ready to be converted to HTML.

Figure 27-4: A worksheet range, ready to be converted to HTML.

3 C:\Documents and Settings\John\Desktop\myrange.htm Microsoft Internet E...

File Edit View Favorites Tools Help s . O a aE

Back Forward 5top Refresh Home Favorites Address C:\Documents and 5ettingsyohn\Desktop\myrange.htm

New York

Los Angeles

Chicago

Total

January

$3,813.00

$8,874.00

$15,174.00

$27,861.00

February

$21,161.00

$611.00

$7,786.00

$29,558.00

March

$21,097.00

$963.00

$10,357.00

$32,417.00

April

$15,392.00

$19,794.00

$9,850.00

$45,036.00

May

$12,858.00

$22,693.00

$8,698.00

$44,249.00

June

$14,336.00

$1,819.00

$111.00

$16,266.00

July

$19,842.00

$354.00

$11,558.00

$31,754.00

August

$15,716.00

$2,003.00

$20,739.00

$38,458.00

September

$4,219.00

$2,976.00

$8,080.00

$15,275.00

October

$12,338.00

$20,490.00

$15,769.00

$48,597.00

November

$18,693.00

$5,389.00

$14,664.00

$38,746.00

December

$17,141.00

$13,602.00

$12,920.00

$43,663.00

Total

$176,606.00

$99,568.00

$135,706.00

I J My Computer d

Figure 27-5: The worksheet data after being converted to HTML.

Was this article helpful?

0 0

Post a comment