CHAPTER 18
Reading from and writing to the web
In this chapter, you will:
Get data from the web
Use
Application.OnTimeto periodically analyze dataPublish data to a web page
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 new features from the former Power Query add-in. You’ll find out how to use VBA to call a website repeatedly to gather information for many data points. This chapter also shows how to save data from a spreadsheet directly to the web.
Getting data from the web
There is an endless variety of data on the Internet. You have two options when it comes to getting data from the web: You can use the Excel interface to build a query and then use VBA to refresh the query, or you can attempt to write the query in the M language. The Power Query add-in that Microsoft introduced for Excel 2010/2013 is built in to Excel 2019. When you use New Query in the Get & Transform group on the Data tab, you are using the former Power Query add-in to build your query in the M language.
The code for the query you would need to write to get data from the web is lengthy and difficult:
Sub CreatePowerQuery()
ActiveWorkbook.Queries.Add Name:="Table 1", _
Formula:="let" & Chr(13) & "" & Chr(10) & _
" Source = Web.Page(Web.Contents(" & _
"""http://www.flightstats.com/go/FlightStatus/" & _
"flightStatusByFlightPositionDetails.do?id=" & _
"562694389&airlineCode=AA&flightNumber=5370""))," _
& Chr(13) & "" & Chr(10) & " Data1 = Source{1}[Data]," _
& Chr(13) & "" & Chr(10) & " #""Changed Type"" = " & _
"Table.TransformColumnTypes(Data1,{{""UTC Time""," & _
"type text}, {""Time At Departure"", type text}, " & _
"{""Time At Arrival"", type text}, {""Spee" & _
"d"", type text}, {""Altitude"", type text}, " & _
"{""Latitude"", type number}, {""Longitude"", " & _
"type number}})," & Chr(13) & "" & Chr(10) & " " & _
"#""Removed Columns"" = Table.RemoveColumns" & _
"(#""Changed Type"",{""UTC Time"", ""Time At " & _
"Departure""})," & Chr(13) & "" & Chr(10) & _
" #""Split Column by Position"" = Table.Split" & _
"Column(#""Removed Columns"",""Time At Arrival""," & _
"Splitter.SplitTextByPositions({0, 6}, false),"
Formula = Formula & _
"{""Time At Arrival.1"", ""Time At Arrival.2""})," & Chr(13) & _
"" & Chr(10) & " #""Changed Type1"" = " & _
"Table.TransformColumnTypes(#""Split Column by " & _
"Position"",{{""Time At Arrival.1"", type date}," & _
"{""Time At Arrival.2"", type time}})," & Chr(13) & _
"" & Chr(10) & " #""Removed Columns1"" = " & _
"Table.RemoveColumns(#""Changed Type1"",{""Time At Arrival.1" _
"})," & _
Chr(13) & "" & Chr(10) & " #""Split Column by Delimiter"" = " & _
"Table.SplitColumn(#""Removed Columns1"",""Spe" & _
"ed"",Splitter.SplitTextByEachDelimiter({"" ""}, " & _
"null, false),{""Speed.1"", ""Speed.2""})," & Chr(13) & _
"" & Chr(10) & " #""Changed Type2"" = " & _
"Table.TransformColumnTypes(#""Split Column by Delimiter""," & _
"{{""Speed.1"", Int64.Type}, {""Speed.2"", type text}})," & _
Chr(13) & "" & Chr(10) & " #""Removed Columns2"" = " & _
"Table.RemoveColumns(#""Changed Type2"",{""Speed.2""})," & _
Chr(13) & "" & Chr(10) & " #""Split Column by Delimiter1"" " & _
"= Table.SplitColumn(#""Removed Columns2""," & _
"""Altitude"",Splitter.SplitTextByEachDelimiter({"" ""}, " & _
"null, false),{""Altitude.1"", ""Altitude.2""})," & _
Chr(13) & "" & Chr(10) & " #""Changed Type3"" = "
Formula = Formula & "Table.TransformColumnTypes(#""Split " & _
"Column by Delimiter1""," & _
"{{""Altitude.1"", Int64.Type}, {""Altitude.2"", type text}})," & _
Chr(13) & "" & Chr(10) & " #""Removed Columns3"" = " & _
"Table.RemoveColumns(#""Changed Type3"",{""Altitude.2""})" & _
Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " " & _
" #""Removed Columns3"""
Sheets.Add After:=ActiveSheet
With ActiveSheet.ListObjects.Add(SourceType:=0, _
Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;" & _
"Data Source=$Workbook$;Location=Table1", _
Destination:=Range("$A$1")).QueryTable .CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [Table 1]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = False
.ListObject.DisplayName = "Table_1"
.Refresh BackgroundQuery:=False
End With
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub
The easier solution is to build the query in the Power Query interface and then refresh the query with this code:
Sub RefreshPowerQuery()
ActiveWorkbook.RefreshAll
End Sub
Building multiple queries with VBA
Say that you want to collect data from a website, such as historical weather statistics. Hourly weather statistics are available from http://www.wunderground.com/history/airport/KCAK/2018/6/17/DailyHistory.html. In this URL, KCAK is the location code for the Akron Canton airport (CAK). The 2018/6/17 refers to June 17, 2018. You can imagine how you can iterate through multiple cities or multiple dates.
The strategy would be to build the Power Query from scratch, refresh, copy the data to a new sheet, and then delete the Power Query and move on to the next city or date.
To gather weather data for 24 months, you have to repeat the web query process more than 700 times. Doing this manually would be tedious.
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:
/2018/6/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.
Finding results from retrieved data
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-1, eight VLOOKUP formulas find the statistics for high, low, rain, and snow from the web query.
FIGURE 18-1 VLOOKUPs at the top of the web worksheet find and pull the relevant data from a web page.
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 by 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 and 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 VLOOKUPs, 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. Gathering two or three years’ worth of web pages requires more than an hour of processing time. Run the macro, head to lunch, and then come back to a good data set.
Putting it all together
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
WSD.Range("K3:N3").FormulaR1C1 = _
"=VLOOKUP(R[-1]C,Web!C1:C2,2,FALSE)"
WSD.Cells(i, 3).Resize(1, 4).Value = _
WSD.Range("K3:N3").Value
Next i
End Sub
After an hour, you have data retrieved from hundreds of web pages (see Figure 18-2).
FIGURE 18-2 Here are the results of running the web query hundreds of times.
Examples of scraping websites using web queries
Over the years, I have used the web query trick many times. Examples include the following:
I used a web query to get names and company addresses for all Fortune 1000 CFOs so that I could pitch my Power Excel seminars to them.
I used a web query to find 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.)
I used a web query to get a mailing address for every public library in the United States.
I used a web query to get a complete list of Chipotle restaurants (which later ended up in my GPS, but that is a story for the [yet unwritten] Microsoft MapPoint book).
Using Application.OnTime to periodically analyze data
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 to 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
Using ready mode for scheduled procedures
The OnTime method runs only when 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.
Specifying a window of time for an update
You can provide Excel with a window of time within which to make an update. The following code tells Excel to run an update at any time between 8:00 a.m. and 8:05 a.m.:
Application.OnTime EarliestTime:=TimeValue("8:00 AM"), _
Procedure:= "CaptureData ", _
LatestTime:=TimeValue("8:05 AM")
If the Excel session remains in Edit mode for the entire five minutes, the scheduled task is skipped.
Canceling a previously scheduled macro
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 and use 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:
Open Excel at 7:30 a.m.
Open Schedule.xlsm and run a macro to schedule a procedure at 8:00 a.m.
Close Schedule.xlsm but keep Excel open.
Open a new workbook and begin entering data.
At 8:00 a.m., Excel reopens Schedule.xlsm and runs the scheduled macro. Excel doesn’t close Schedule.xlsm. 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.
Closing Excel cancels all pending scheduled macros
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.
Scheduling a macro to run x minutes in the future
You can schedule a macro to run at a certain time in the future. The following 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, canceling this scheduled event 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
Scheduling a verbal reminder
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 a 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:
Tell your manager that you are taking him out to lunch to celebrate April 1.
At some point in the morning, while your manager is getting coffee, run the
ScheduleSpeechmacro. Design the macro to run 15 minutes after your lunch starts.Take your manager to lunch.
While the manager is away, the scheduled macro runs.
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 strong alibi for when the prank began to happen. Here’s the code you use to do it:
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.
Scheduling a macro to run every two minutes
Say that you want to ask Excel to run a certain macro every two minutes. However, you realize that if a macro gets delayed because you accidentally left the workbook in Edit mode while going to the staff meeting, you 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. It doesn’t schedule a million updates in the future. You have only one future update scheduled at any given time. Therefore, if you decide that you are tired of seeing the national debt every 15 seconds, you only need to comment out the Application.OnTime line of code and wait 15 seconds for the last update to happen.
Publishing data to a web page
This chapter has highlighted many ways to capture data from the web. But you can also publish Excel data back to the web. That’s what this section is about.
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 a 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 can instantly access the latest version of the report.
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 2019, use File, Save As. Select Web Page (*.htm, *html) in the Save as Type drop-down menu. 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 the number formats and font sizes (see Figure 18-3).
FIGURE 18-3 The formatting is close to that of the original worksheet.
Whereas the macro from Chapter 11 did WBN.SaveAs, the current 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-3, it is not extremely fancy. For example, you don’t have a company logo or navigation bar to examine other reports.
Using VBA to create custom web pages
Long before Microsoft introduced the Save As Web Page functionality, people had been using VBA to publish Excel data as HTML. The advantage of using VBA for this 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.
The following 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 the 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.
Using Excel as a content management system
Half a billion people are proficient in Excel. Companies everywhere have data in Excel and many staffers who are comfortable 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 a 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, and I’ll show you an example. 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-4 shows the simple Bottom worksheet.
FIGURE 18-4 Companies everywhere are maintaining all sorts of data in Excel and are comfortable updating the data in Excel. Why not marry Excel with a simple bit of VBA so that custom HTML can be produced from Excel?
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 following 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 the HTML file
For j = 2 To FinalM
' Surround Member name with bold tags
Print #1, "<li>" & WSM.Cells(j, 1).Value
Next j
' Close the 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 the 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-5 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, and it maintains the look and feel of the rest of the site.
Using this approach 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 clicks 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 of opening his new sample.html file in Notepad and copying the new code to the Top and Bottom worksheets.
FIGURE 18-5 A simple content management system in Excel was used to generate this web page. The look and feel match the look and feel of the rest of the website. Excel achieved it without any expensive web database coding.
The resulting web page has a small file size—about one-sixth the size of an 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.
Bonus: FTP from Excel
Even when 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, many people are proficient in Excel, but not so many are 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
Next steps
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.