The Internet has become pervasive and has changed our lives. From your desktop, millions of answers are available at your fingertips. In addition, publishing a report on the Web enables millions of others to instantly access your information.
This chapter discusses automated ways to pull data from the Web into spreadsheets, using web queries. You’ll find out how to use VBA to call a website repeatedly to gather information for many data points. It also shows how to save data from your spreadsheet directly to the Web.
There is an endless variety of data on the Internet. You can gather stock quotes from Quotes.com. You can download historical temperatures from Weather Underground. You can get fantasy football stats from NFL.com. Whatever your interest, there is probably a website somewhere with that information online.
Sometimes the websites make it difficult by putting the information on many different pages. You can use VBA to automate the process of visiting all those pages and collecting the data.
Instead of manually downloading data from a website every day and then importing it into Excel, you can use the Web Query feature in Excel to allow it to automatically retrieve the data from a web page.
You can set up web queries to refresh the data from the Web every day or even every minute. Although they were originally fairly hard to define, the Excel user interface now includes a web browser you can use to build the web query.
As Web 2.0 evolves, there are some sites that are not suitable for web queries. You want to look for web pages where the URL tells you about the selections that you made while getting to that page.
For example, I searched for NFL stats. In the process of getting to an interesting page, I had asked for 2011 regular-season data. I had asked for passing stats and then the complete list. I ended up at a page with a very long URL: http://www.nfl.com/stats/categorystats?tabSeq=0&statisticCategory=PASSING&conference=null&season=2011&seasonType=REG&d-447263-s=PASSING_YARDS&d-447263-o=2&d-447263-n=1
.
This looks like an excellent candidate for web queries because all of my choices are embedded in that URL. I can see 2011, REG, PASSING, and YARDS in the URL.
Go to the address bar, change 2011 to 2010, and press Enter. If the correct page comes up with 2010 passing yards, you know that you have a winner.
Another example: Suppose you want currency exchange rates from XE.com. On the XE.com page, you specify 100, CAD for Canadian dollars and USD for U.S. dollars. Click Go. The URL of the returned page is http://www.xe.com/ucc/convert.cgi?Amount=100&From=CAD&To=USD
. You can see how you can alter this URL by changing USD to GBP to get British pounds.
In contrast, take a look at http://www.Easy-XL.com
. There are several videos you can watch there. As you navigate to each video, the URL stays exactly the same: http://www.easy-xl.com/iaplayer.cgi?v=Query&x=play&p=ez%2Fvideos&i=ezVideos.csv
.
There is nothing in that URL that tells you which video you chose. The site is using some Web 2.0 magic via Java to serve up the correct video. A site built like this is not ideal for web queries.
The easiest way to get started with web queries is to create your first one manually while the macro recorder is running.
Excel 2013 includes the PowerPivot add-in that allows you to mash up disparate datasets. One of the favorite demo applications mashes up daily sales data from a store with daily weather for that city. You probably already have daily sales data for your stores. The hard part is finding daily weather data.
The Weather Underground website has a historical weather query. After browsing to find the data for the Akron Canton airport (code = CAK) for February 17, 2010, you will have this URL: http://www.wunderground.com/history/airport/KCAK/2010/2/17/DailyHistory.html
.
You can see all the variables in the URL—the airport code of CAK and the date from which you need the weather, albeit in a bizarre format of YYYY/M/D.
Open Excel. Go to a blank worksheet. Rather than leave the cell pointer in A1, move down to about Cell A9 to leave room for some work variables later.
Turn on the macro recorder. Record a new macro called WeatherQuery. From the Data tab of the ribbon, select Get External Data, From Web. Excel shows the New Web Query dialog with your Internet Explorer home page displayed.
Using the browser, go to your desired website. Make the selections necessary to get the data. In the case of Weather Underground, select history, the city, and the date, and click Go. In a moment, the desired web page displays in the dialog box.
Note that in addition to the web page there are a number of yellow squares with a black arrow. These squares are in the upper-left corner of various tables on the web page. Click the square that contains the data you want to import to Excel. If there is no square for your table, import the entire page, as shown in Figure 18.1. While you are clicking, a blue border confirms the table that will be imported. After you click, the yellow arrow changes to a green check mark.
Click the Import button on the New Web Query dialog. Click OK on the Import Data dialog. In a few seconds, you see the live data imported into a range on your spreadsheet. Because you import the entire section of the web page, there will be the data that you want as well as extraneous data. In Figure 18.2, you see that I’ve manually highlighted the statistics that I think would be relevant in northeastern Ohio. If you live in Maui or Trinidad, you might not care about snowfall. Figure 18.2 shows the returned web query.
Here’s the recorded macro:
Sub WeatherQuery()
'
With ActiveSheet.QueryTables.Add(Connection:= "URL;http://www" & _
".wunderground.com/history/airport/KCAK/2010/2/17/DailyHistory.html" _
, Destination:=Range("$A$10"))
.Name = "DailyHistory"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
The important parts of this macro are the connect string, the location of the data returned from the web query, the web table, and the Refresh BackgroundQuery:=False
.
The connect string is the URL that you found in the address bar of Internet Explorer (preceded by URL:).
The output location for the web query is specified in the destination property.
Setting BackgroundQuery
to False
means that the macro does not proceed until the data comes back from the web page. This is the appropriate setting. Your macro might go on to pull certain pieces of data from the results. If you allowed the query to run in the background, the macro would be pulling from a blank web page.
This example loaded the entire web page. If you had specified one table, you would see a line identifying which .WebTable
to import. The best way to figure out this table number is to record a macro and have the macro recorder tell you the table number that corresponds to the check box that you selected.
Note
If web query macros are going to break over time, it will be because of a website redesign. If the web owner decides to add a new advertising box at the top of the website, it might move the good data from table #11 to table #12. If you are designing a web query that will be run once a day for the next five years, you should add some code to make sure that you are actually getting the correct data.
In this example, if the word Actual does not appear in column B, stop the macro and alert someone:
Dim FoundCell As Range
Set FoundCell = Columns("B:B").Find(What:="Actual", _
After:=Range("B1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)
If FoundCell Is Nothing Then
MsgBox "It looks like the website changed. Actual not found in column B."
Exit Sub
End If
End Sub
To update all web queries on the current sheet, use this code:
Sub RefreshAllWebQueries()
Dim QT As QueryTable
For Each QT In ActiveSheet.QueryTables
Application.StatusBar = "Refreshing " & QT.Connection
QT.Refresh
Next QT
Application.StatusBar = False
End Sub
You can assign this macro to a hotkey or to a macro button and refresh all queries on demand.
To gather weather data for 24 months, you have to repeat the web query process more than 700 times. This would be tedious to do manually.
Instead, you can use VBA to build and execute the web queries. It is fairly simple to build a web query on the fly. The connect string to get weather for any airport for any day can be broken down into four parts.
The first part can be hard-coded because it never changes:
"URL;http://www.wunderground.com/history/airport/K"
The next part is the three-letter airport code. If you are retrieving data for many cities, this part will change:
CAK
The third part is a slash, the date in YYYY/M/D format and a slash:
/2010/2/17/
The final part can be hard-coded:
"DailyHistory.html"
Insert a new worksheet and build an output table. In Cell A2, enter the first date for which you have sales history. Use the fill handle to drag the dates down to the current date.
The formula in B2 is ="/"&Text(A2,"YYYY/M/D")&"/"
.
Add friendly headings across Row 1 for the statistics you will collect.
The data worksheet is shown in Figure 18.3.
Next, you have a decision to make. It looks as though the weather underground website is fairly static. The snow statistic even shows up if I ask for JHM airport in Maui. If you are positive that rainfall is always going to appear in Cell B28 of your results sheet, you could write the macro to get data from there.
However, to be safe, you can build some lookup formulas at the top of the worksheet to look for certain row labels and to pull that data. In Figure 18.4, eight INDEX and MATCH formulas find the statistics for High, Low, Rain, and Snow from the web query.
Note
The variable web location of the web data happens more often than you might think. If you are pulling name and address information, some addresses have three lines and some have four lines. Anything that appears after that address might be off by a row. Some stock-quote sites show a different version of the data depending on whether the market is open or closed. If you kick off a series of web queries at 3:45 p.m., the macro might work until 4:00 p.m. and then stop working. For these reasons, it is often safer to take the extra steps of retrieving the correct data from the web query using VLOOKUP
statements.
To build the macro, you add some code before the recorded code:
Dim WSD as worksheet
Dim WSW as worksheet
Set WSD = Worksheets("Data")
Set WSW = Worksheets("Web")
FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
Then add a loop to go through all the dates in the data worksheet:
For I = 2 to FinalRow
ThisDate = WSD.Cells(I, 2).value
' Build the ConnectString
CS = "URL: URL;http://www.wunderground.com/history/airport/KCAK"
CS = CS & ThisDate & "DailyHistory.html"
If a web query is about to overwrite existing data on the worksheet, it moves that data to the right. You want to clear the previous web query and all the contents:
For Each qt In WSD.QueryTables
qt.Delete
Next qt
WSD.Range("A10:A300").EntireRow.Clear
You can now go into the recorded code. Change the QueryTables.Add
line to the following:
With WSD.QueryTables.Add(Connection:= CS, Destination:=WSW.Range("A10"))
After the recorded code, add some lines to calculate the VLOOKUP
s, copy the results, and finish the loop:
WSW.Calculate
WSD.Cells(i, 3).Resize(1, 4).Value = WSW.Range("B4:E4").Value
Next i
Step through the code as it goes through the first loop to make sure that everything is working. You should notice that the actual .Refresh
line takes about 5 to 10 seconds. To gather two or three years’ worth of web pages, it requires more than an hour of processing time. Run the macro, head to lunch, and then come back to a good dataset.
In the final macro here, I turned off screen updating and showed the row number that the macro is processing in the status bar. I also deleted some unnecessary properties from the recorded code:
Sub GetData()
Dim WSD As Worksheet
Dim WSW As Worksheet
Set WSD = Worksheets("Data")
Set WSW = Worksheets("Web")
FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To FinalRow
ThisDate = WSD.Cells(i, 2).Value
' Build the ConnectString
CS = "URL;http://www.wunderground.com/history/airport/KCAK/"
CS = CS & ThisDate
CS = CS & "DailyHistory.html"
' Clear results of last web query
For Each qt In WSW.QueryTables
qt.Delete
Next qt
WSD.Range("A10:A300").EntireRow.Clear
With WSW.QueryTables.Add(Connection:=CS, Destination:=Range("$A$10"))
.Name = "DailyHistory"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
WSW.Calculate
WSD.Cells(i, 3).Resize(1, 4).Value = WSW.Range("B4:E4").Value
Next i
End Sub
After an hour, you have data retrieved from hundreds of web pages (see Figure 18.5).
Over the years, I have used the web query trick many times. Examples include the following:
• Names and company address for all Fortune 1000 CFOs so that I could pitch my Power Excel seminars to them.
• The complete membership roster for a publishing association of which I am a member. (I already had the printed roster, but with an electronic database, I could filter to find publishers in certain cities.)
• A mailing address for every public library in the United States.
• The complete list of Chipotle restaurants (which later ended up in my GPS, but that is a story for the [yet unwritten] Microsoft MapPoint book).
VBA offers the OnTime
method for running any VBA procedure at a specific time of day or after a specific amount of time has passed.
You can write a macro that would capture data every hour throughout the day. This macro would have times hard-coded. The following code will, theoretically, capture data from a website every hour throughout the day:
Sub ScheduleTheDay()
Application.OnTime EarliestTime:=TimeValue("8:00 AM"), _
Procedure:=CaptureData
Application.OnTime EarliestTime:=TimeValue("9:00 AM"), _
Procedure:=CaptureData
Application.OnTime EarliestTime:=TimeValue("10:00 AM"), _
Procedure:=CaptureData
Application.OnTime EarliestTime:=TimeValue("11:00 AM"), _
Procedure:=CaptureData
Application.OnTime EarliestTime:=TimeValue("12:00 AM"), _
Procedure:=CaptureData
Application.OnTime EarliestTime:=TimeValue("1:00 PM"), _
Procedure:=CaptureData
Application.OnTime EarliestTime:=TimeValue("2:00 PM"), _
Procedure:=CaptureData
Application.OnTime EarliestTime:=TimeValue("3:00 PM"), _
Procedure:=CaptureData
Application.OnTime EarliestTime:=TimeValue("4:00 PM"), _
Procedure:=CaptureData
Application.OnTime EarliestTime:=TimeValue("5:00 PM"), _
Procedure:=CaptureData
End Sub
Sub CaptureData()
Dim WSQ As Worksheet
Dim NextRow As Long
Set WSQ = Worksheets("MyQuery")
' Refresh the web query
WSQ.Range("A2").QueryTable.Refresh BackgroundQuery:=False
' Make sure the data is updated
Application.Wait (Now + TimeValue("0:00:10"))
' Copy the web query results to a new row
NextRow = WSQ.Cells(Rows.Count, 1).End(xlUp).Row + 1
WSQ.Range("A2:B2").Copy WSQ.Cells(NextRow, 1)
End Sub
The OnTime
method will run provided only that Excel is in Ready, Copy, Cut, or Find mode at the prescribed time. If you start to edit a cell at 7:59:55 a.m. and keep that cell in Edit mode, Excel cannot run the CaptureData macro at 8:00 a.m. as directed.
In the preceding code example, I specified only the start time for the procedure to run. Excel waits anxiously until the spreadsheet is returned to Ready mode and then runs the scheduled program as soon as it can.
The classic example is that you start to edit a cell at 7:59 a.m., and then your manager walks in and asks you to attend a surprise staff meeting down the hall. If you leave your spreadsheet in Edit mode and attend the staff meeting until 10:30 a.m., the program cannot run the first three scheduled hours of updates. As soon as you return to your desk and press Enter to exit Edit mode, the program runs all previously scheduled tasks. In the preceding code, you find that the first three scheduled updates of the program all happen between 10:30 and 10:31 a.m.
One alternative is to provide Excel with a window of time within which to make the update. The following code tells Excel to run the update at any time between 8:00 a.m. and 8:05 a.m. If the Excel session remains in Edit mode for the entire five minutes, the scheduled task is skipped.
Application.OnTime EarliestTime:=TimeValue("8:00 AM"), Procedure:=CaptureData,
LatestTime:=TimeValue("8:05 AM")
It is fairly difficult to cancel a previously scheduled macro. You must know the exact time that the macro is scheduled to run. To cancel a pending operation, call the OnTime
method again, using the Schedule:=False
parameter to unschedule the event. The following code cancels the 11:00 a.m. run of CaptureData
:
Sub CancelEleven()
Application.OnTime EarliestTime:=TimeValue("11:00 AM"), _
Procedure:=CaptureData, Schedule:=False
End Sub
It is interesting to note that the OnTime
schedules are remembered by a running instance of Excel. If you keep Excel open but close the workbook with the scheduled procedure, it still runs. Consider this hypothetical series of events:
1. Open Excel at 7:30 a.m.
2. Open Schedule.xls
and run a macro to schedule a procedure at 8:00 a.m.
3. Close Schedule.xls
but keep Excel open.
4. Open a new workbook and begin entering data.
At 8:00 a.m., Excel reopens Schedule.xls
and runs the scheduled macro. Excel doesn’t close Schedule.xls
. As you can imagine, this is fairly annoying and alarming if you are not expecting it. If you are going to make extensive use of Application.Ontime
, you might want to have it running in one instance of Excel while you work in a second instance of Excel.
Note
If you are using a macro to schedule a macro a certain amount of time later, you could remember the time in an out-of-the way cell to be able to cancel the update. See an example in the “Scheduling a Macro to Run x Minutes in the Future” section of this chapter.
If you close Excel with File, Exit, all future scheduled macros are automatically canceled. When you have a macro that has scheduled a bunch of macros at indeterminate times, closing Excel is the only way to prevent the macros from running.
You can schedule a macro to run at a certain time in the future. The macro uses the TIME
function to return the current time and adds 2 minutes and 30 seconds to the time. The following macro runs something 2 minutes and 30 seconds from now:
Sub ScheduleAnything()
' This macro can be used to schedule anything
WaitHours = 0
WaitMin = 2
WaitSec = 30
NameOfScheduledProc = "CaptureData"
' --- End of Input Section -------
' Determine the next time this should run
NextTime = Time + TimeSerial(WaitHours, WaitMin, WaitSec)
' Schedule ThisProcedure to run then
Application.OnTime EarliestTime:=NextTime, Procedure:=NameOfScheduledProc
End Sub
Later, if you need to cancel this scheduled event, it would be nearly impossible. You won’t know the exact time that the macro grabbed the TIME
function. You might try to save this value in an out-of-the-way cell:
Sub ScheduleWithCancelOption
NameOfScheduledProc = "CaptureData"
' Determine the next time this should run
NextTime = Time + TimeSerial(0,2,30)
Range("ZZ1").Value = NextTime
' Schedule ThisProcedure to run then
Application.OnTime EarliestTime:=NextTime, Procedure:=NameOfScheduledProc
End Sub
Sub CancelLater()
NextTime = Range("ZZ1").value
Application.OnTime EarliestTime:=NextTime, _
Procedure:=CaptureData, Schedule:=False
End Sub
The text-to-speech tools in Excel can be fun. The following macro sets up a schedule that reminds you when it is time to go to the staff meeting:
Sub ScheduleSpeak()
Application.OnTime EarliestTime:=TimeValue("9:14 AM"), _
Procedure:="RemindMe"
End Sub
Sub RemindMe()
Application.Speech.Speak _
Text:="Bill. It is time for the staff meeting."
End Sub
If you want to pull a prank on your manager, you can schedule Excel to automatically turn on the Speak on Enter feature. Follow this scenario:
1. Tell your manager that you are taking him out to lunch to celebrate April 1.
2. At some point in the morning, while your manager is getting coffee, run the ScheduleSpeech macro. Design the macro to run 15 minutes after your lunch starts.
3. Take your manager to lunch.
4. While the manager is away, the scheduled macro will run.
5. When the manager returns and starts typing data in Excel, the computer will repeat the cells as they are entered. This is slightly reminiscent of the computer on Star Trek that repeated everything Lieutenant Uhura said.
After this starts happening, you can pretend to be innocent; after all, you have a firm alibi for when the prank began to happen:
Sub ScheduleSpeech()
Application.OnTime EarliestTime:=TimeValue("12:15 PM"), _
Procedure:="SetUpSpeech"
End Sub
Sub SetupSpeech())
Application.Speech.SpeakCellOnEnter = True
End Sub
Note
To turn off Speak on Enter, you can either dig out the button from the QAT Customization panel (look in the category called Commands Not on the Ribbon) or, if you can run some VBA, change the SetupSpeech macro to change the True to False.
My favorite method is to ask Excel to run a certain macro every two minutes. However, I realize that if a macro gets delayed because I accidentally left the workbook in Edit mode while going to the staff meeting, I don’t want dozens of updates to happen in a matter of seconds.
The easy solution is to have the ScheduleAnything
procedure recursively schedule itself to run again in two minutes. The following code schedules a run in two minutes and then performs CaptureData
:
Sub ScheduleAnything()
' This macro can be used to schedule anything
' Enter how often you want to run the macro in hours and minutes
WaitHours = 0
WaitMin = 2
WaitSec = 0
NameOfThisProcedure = "ScheduleAnything"
NameOfScheduledProc = "CaptureData"
' --- End of Input Section -------
' Determine the next time this should run
NextTime = Time + TimeSerial(WaitHours, WaitMin, WaitSec)
' Schedule ThisProcedure to run then
Application.OnTime EarliestTime:=NextTime, Procedure:=NameOfThisProcedure
' Get the Data
Application.Run NameOfScheduledProc
End Sub
This method has some advantages. I have not scheduled a million updates in the future. I have only one future update scheduled at any given time. Therefore, if I decide that I am tired of seeing the national debt every 15 seconds, I only need to comment out the Application.OnTime
line of code and wait 15 seconds for the last update to happen.
This chapter highlights many ways to capture data from the Web. It is also useful for publishing Excel data back to the Web.
The RunReportForEachCustomer
macro shown in Chapter 11, “Data Mining with Advanced Filter,” produces reports for each customer in a company. Instead of printing and faxing the report, it would be cool to save the Excel file as HTML and post the results on a company intranet so that the customer service reps could instantly access the latest version of the report.
Consider a report like the one shown in Figure 18.6. With the Excel user interface, it is easy to save the report as a web page to create an HTML view of the data.
In Excel 2013, use File, Save As. Select Web Page (*.htm, *html) in the Save as Type drop-down (see Figure 18.7).
You have control over the title that appears in the window title bar. This title also gets written to the top center of your web page.
Click the Change Title button to change the <Title>
tag for the web page. Type a name that ends in either .htm or .html and click Publish.
The result is a file that can be viewed in any web browser. The web page accurately shows our number formats and font sizes (see Figure 18.8).
Whereas the macro from Chapter 11 did WBN.SaveAs
, the new macro uses this code to write out each web page:
HTMLFN = "C:Intranet" & ThisCust & ".html"
On Error Resume Next
Kill HTMLFN
On Error GoTo 0
With WBN.PublishObjects.Add( _
SourceType:=xlSourceSheet, _
Filename:=HTMLFN, _
Sheet:="Sheet1", _
Source:="", _
HtmlType:=xlHtmlStatic, _
DivID:="A", _
Title:="Sales to " & ThisCust)
.Publish True
.AutoRepublish = False
End With
Although the data is accurately presented in Figure 18.8, it is not extremely fancy. We don’t have a company logo or navigation bar to examine other reports.
Long before Microsoft introduced the Save as Web Page functionality, people had been using VBA to take Excel data and publish it as HTML. The advantage of this method is that you can write out specific HTML statements to display company logos and navigation bars.
Consider a typical web page template:
• There is code to display a logo and navigation bar at the top/side.
• There is content for the page.
• There is some HTML code to finish the page.
This macro reads the code behind a web page and writes it to Excel:
Sub ImportHTML()
ThisFile = "C:Intranetschedule.html"
Open ThisFile For Input As #1
Ctr = 2
Do
Line Input #1, Data
Worksheets("HTML").Cells(Ctr, 2).Value = Data
Ctr = Ctr + 1
Loop While EOF(1) = False
Close #1
End Sub
If you import the text of a web page into Excel, even if you don’t understand the HTML involved, you can probably find the first lines that contain your page content.
Examine the HTML code in Excel. Copy the lines needed to draw the top part of the web page to a worksheet called Top. Copy the lines of code needed to close the web page to a worksheet called Bottom.
You can use VBA to write out the top, generate content from your worksheet, and then write out the bottom.
Five hundred million people are proficient in Excel. Companies everywhere have data in Excel, and many staffers who are comfortable in maintaining that data. Rather than force these people to learn how to create HTML pages, why not build a content management system to take their Excel data and write out custom web pages?
You probably already have data for the web page in Excel. Using the ImportHTML
routine to read the HTML into Excel, you know the top and bottom portions of the HTML needed to render the web page.
Building a content management system with these tools is simple. To the existing Excel data, I added two worksheets. In the worksheet called Top, I copied the HTML needed to generate the navigation bar of the website. To the worksheet called Bottom, I copied the HTML needed to generate the end of the HTML page. Figure 18.9 shows the simple Bottom worksheet.
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")
' Figure out the path
MyPath = ThisWorkbook.Path
LineCtr = 0
FinalT = WST.Cells(Rows.Count, 1).End(xlUp).Row
FinalB = WSB.Cells(Rows.Count, 1).End(xlUp).Row
FinalM = WSM.Cells(Rows.Count, 1).End(xlUp).Row
MyFile = "sampleschedule.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
Print #1, WST.Cells(j, 2).Value
Next j
' 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, "<li>" & WSM.Cells(j, 1).Value
Next j
' Close old file
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
Print #1, WSB.Cells(j, 2).Value
Next j
Close #1
Application.StatusBar = False
Application.CutCopyMode = False
MsgBox "web pages updated"
End Sub
Figure 18.10 shows the finished web page. This web page looks a lot better than the generic page created by Excel’s Save as Web Page option. It maintains the look and feel of the rest of the site.
This system has many advantages. The person who maintains the schedule data 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.
Note
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.
After you are able to update web pages from Excel, you still have the hassle of using an FTP program to upload the pages from your hard drive to the Internet. Again, we have lots of people proficient in Excel, but not so many comfortable with using an FTP client.
Ken Anderson has written a cool command-line FTP freeware utility. Download WCL_FTP from http://www.softlookup.com/display.asp?id=20483
. Save WCL_FTP.exe
to the root directory of your hard drive and then use this code to automatically upload your recently created HTML files to your web server:
Sub DoFTP(fname, pathfname)
' To have this work, copy wcl_ftp.exe to the C: root directory
' Download from http://www.softlookup.com/display.asp?id=20483
' Build a string to FTP. The syntax is
' WCL_FTP.exe "Caption" hostname username password host-directory _
' host-filename local-filename get-or-put 0Ascii1Binanry 0NoLog _
' 0Background 1CloseWhenDone 1PassiveMode 1ErrorsText
If Not Worksheets("Menu").Range("I1").Value = True Then Exit Sub
s = """c:wcl_ftp.exe "" " _
& """Upload File to website"" " _
& "ftp.MySite.com FTPUser FTPPassword www " _
& fname & " " _
& """" & pathfname & """ " _
& "put " _
& "0 0 0 1 1 1"
Shell s, vbMinimizedNoFocus
End Sub
Chapter 19, “Text File Processing,” covers importing from a text file and writing to a text file. Being able to write to a text file is useful when you need to write out data for another system to read.