Info

The macro code opens a text file called directory.html for output. First, all the HTML code from the Top worksheet is written to the file.

Then the macro loops through each row in the membership directory, writing data to the file.

After completing this loop, the macro writes out the HTML code from the Bottom worksheet to finish the file:

Sub WriteMembershipHTML() ' Write Web Pages Dim WST As Worksheet Dim WSB As Worksheet Dim WSM As Worksheet Set WSB = Worksheets("Bottom") Set WST = Worksheets("Top") Set WSM = Worksheets("Membership")

1 Figure out the path MyPath = ThisWorkbook.Path

LineCtr = 0

FinalT = WST.Cells(65536, 1).End(xlUp).Row FinalB = WSB.Cells(65536, 1).End(xlUp).Row FinalM = WSM.Cells(65536, 1).End(xlUp).Row

MyFile = "sampledirectory.html"

ThisFile = MyPath & Application.PathSeparator & MyFile ThisHostFile = MyFile

' Delete the old HTML page On Error Resume Next Kill (ThisFile) On Error GoTo 0

' Build the title

ThisTitle = "<Title>LTCC Membership Directory</Title>" WST.Cells(3, 2).Value = ThisTitle

' Open the file for output Open ThisFile For Output As #1

' Write out the top part of the HTML For j = 2 To FinalT

' For each row in Membership, write out lines of data to HTML file For j = 2 To FinalM

' Surround Member name with bold tags

Print #1, "<b>" & WSM.Cells(j, 1).Value & "</b><br>"

' Member Address

Print #1, WSM.Cells(j, 2).Value & "<br>" ' City, State, Zip code

Addr = WSM.Cells(j, 3) & " " & WSM.Cells(j, 4) & " " & WSM.Cells(j, 5)

' Telephone number with 2 line breaks after it Print #1, WSM.Cells(j, 6).Value & "<br><br>" Next j

' Close old file

' Write date updated, but make sure there are 20 rows first Print #1, "<br>"

Print #1, "This page current as of " & Format(Date, "mmmm dd, yyyy") & _ " " & Format(Time, "h:mm AM/PM")

' Write out HTML code from Bottom worksheet For j = 2 To FinalB

Application.StatusBar = False Application.CutCopyMode = False MsgBox "Web pages updated"

End Sub

The finished Web page is shown in Figure 14.15. This Web page looks a lot better than the generic page created by Excel's Save As Web Page option. It can maintain the look and feel of the rest of the site.

Figure 14.15

A simple content management system in Excel was used to generate this Web page.The look and feel matches the rest of the Web site. Excel achieved it without any expensive Web database coding.

Figure 14.15

A simple content management system in Excel was used to generate this Web page.The look and feel matches the rest of the Web site. Excel achieved it without any expensive Web database coding.

There are many advantages to this system. The person who maintains the membership directory is comfortable working in Excel. She has already been maintaining the data in Excel on a regular basis. Now, after updating some records, she presses a button to produce a new version of the Web page.

Of course, the Web designer is clueless about Excel. However, if he ever wants to change the Web design, it is a simple matter to open his new sample.html file in Notepad and copy the new code to the Top and Bottom worksheet.

The resulting Web page has a small file size—about one-sixth the size of the equivalent page created by Excel's Save As Web Page.

In real life, the content management system in this example was extended to allow easy maintenance of the organization's calendar, board members, and so on. The resulting workbook made it possible to maintain 41 Web pages at the click of a button.

0 0

Post a comment