The easiest way to get started with web queries is to create your first one manually. Using any web browser, navigate to a website and enter the settings needed to display the information of interest to you. In the case of Figure 16.1, the URL to display that portfolio is as follows:

http://finance.yahoo.com/q/cq?d=v1&s=KO%2c+MSFT%2c+GOOG%2c+WGO%2c+HOG%2c+F

Open Excel. Find a blank area of the worksheet. From the Data tab of the Ribbon, choose Get External Data, from Web. Excel shows the New Web Query dialog with your Internet Explorer home page displayed. Copy the preceding URL to the Address text box and click Go. In a moment, the desired web page will display 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 that you want to import to Excel. In this case, you want the portfolio information. As shown in Figure 16.2, click the square by the table of quotes. 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.

Figure 16.2. Use the New Web Query dialog to browse to a web page. Highlight the table that you want to import to Excel by clicking on a yellow arrow adjacent to the table.

Click the Import button on the New Web Query dialog. Click OK on the Import Data dialog. In a few seconds, you will see the live data imported into a range on your spreadsheet, as shown in Figure 16.3.

Figure 16.3. Data from the web page is automatically copied to your worksheet. You can now use VBA to automatically refresh this data at your command or periodically.

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 could assign this macro to a hot key or to a macro button and refresh all queries on demand.

The problem with the previous examples is that the web query URL is hard-coded into the VBA. Someone would be required to edit the VBA code every time that the portfolio changes.

It is fairly simple to build a web query on-the-fly. The key is to build a connect string. The connect string for the Yahoo! Finance stock quote is this:

URL: http://finance.yahoo.com/q/cq?d=v1&s=PSO,+SJM,+KO,+MSFT,+CSCO,+INTC

To build a flexible application, you need to use the concatenation character (&) to join the first part of the connect string with the various stock symbols.

Figure 16.4 shows a simple front end for a web query engine. Anyone can enter stock symbols of interest in the shaded area of Column A. The Get Quotes button is set up to call the CreateNewQuery macro.

Figure 16.4. This worksheet serves as a front end to allow different stock symbols to be entered in Column A. Click the Get Quotes button to run a macro that builds a web query on-the-fly on a second worksheet and then copies the data to this sheet.

When the button is clicked, the macro builds a connect string by concatenating the first part of the URL with the first stock symbol from cell A2:

ConnectString = "URL;http://finance.Yahoo.com/q/cq?d=v1&s=" & _
    WSD.Cells(i, 1).Value

As the program loops through additional stock symbols, it takes the existing connect string and adds a comma and a plus sign, and then the next stock symbol:

ConnectString = ConnectString & ",+" & WSD.Cells(i, 1).Value

After the connect string has been built, the macro uses a worksheet called Workspace to build the web query. This hides the unformatted web query results from view. Because it is possible that the list of stock symbols has changed since the last web query, the program deletes any old query and then builds the new query.

It is important to set the query up with BackgroundRefresh set to False. This ensures that the macro pauses to give the query time to refresh before continuing.

After the results have been received, the program assigns a range name to the results and uses VLOOKUP formulas to retrieve the desired columns from the web query:

Sub CreateNewQuery()
    Dim WSD As Worksheet
    Dim WSW As Worksheet
    Dim QT As QueryTable
    Dim FinalRow As Long
    Dim i As Integer
    Dim ConnectString As String
    Dim FinalResultRow As Long
    Dim RowCount As Long

    Set WSD = Worksheets("Portfolio")
    Set WSW = Worksheets("Workspace")

    ' Read column A of Portfolio to find all stock symbols
    FinalRow = WSD.Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To FinalRow
        Select Case i
            Case 2
                ConnectString = "URL;http://finance.Yahoo.com/q/cq?d=v1&s=" & _
                    WSD.Cells(i, 1).Value
            Case Else
                ConnectString = ConnectString & "%2c+" & WSD.Cells(i, 1).Value
        End Select
    Next i

    ' On the Workspace worksheet, clear all existing query tables
    For Each QT In WSW.QueryTables
        QT.Delete
    Next QT

    ' Define a new web Query
    Set QT = WSW.QueryTables.Add(Connection:=ConnectString, _
        Destination:=WSW.Range("A1"))
    With QT
        .Name = "portfolio"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "11"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
    End With

    ' Refresh the query
    QT.Refresh BackgroundQuery:=False

    ' Define a named range for the results
    FinalResultRow = WSW.Cells(Rows,Count, 1).End(xlUp).Row
    WSW.Cells(1, 1).Resize(FinalResultRow, 7).Name = "WebInfo"

    ' Build a VLOOKUP to get quotes from WSW to WSD
    RowCount = FinalRow - 1
    WSD.Cells(2, 2).Resize(RowCount, 1).FormulaR1C1 = _
        "=VLOOKUP(RC1,WebInfo,3,False)"
    WSD.Cells(2, 3).Resize(RowCount, 1).FormulaR1C1 = _
        "=VLOOKUP(RC1,WebInfo,4,False)"
    WSD.Cells(2, 4).Resize(RowCount, 1).FormulaR1C1 = _
        "=VLOOKUP(RC1,WebInfo,5,False)"
    WSD.Cells(2, 5).Resize(RowCount, 1).FormulaR1C1 = _
        "=VLOOKUP(RC1,WebInfo,6,False)"
    WSD.Cells(2, 6).Resize(RowCount, 1).FormulaR1C1 = _
        "=VLOOKUP(RC1,WebInfo,2,False)"

    MsgBox "Data Updated"
End Sub

When the program runs, you will get nicely formatted results, as shown in Figure 16.5.

Figure 16.5. After you run the macro, only the relevant columns are shown on the report. The back worksheet contains the unformatted web query from the website.

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 will 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 anytime 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:

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.

If you are using a macro to schedule a macro a certain amount of time in the future from the current time, you could remember the time in an out-of-the way cell to be able to cancel the update. See an example in the section “Scheduling a Macro to Run x Minutes in the Future.”

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 time at a certain point 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

If you need to later 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 will remind 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:

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

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.

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 data specific to the page, and then there is data to close the HTML file. Build an HTML template with the words PUT DATA HERE and examine the resulting HTML in Notepad, as shown in Figure 16.10.

Figure 16.10. Examining the sample.html file in Notepad and locating the words PUT DATA HERE allows you to separate the HTML file into three pieces: the top portion of the code used to write the navigation bar, the data, and the bottom portion used to close the HTML page.

After you examine the code in Notepad, the task of creating a custom web page is fairly simple. First, write out the HTML needed to draw the navigation bars. Then, write the data from Excel. Then, write the few lines of HTML to close the table and finish the web page.

Two hundred and fifty 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?

Figure 16.11 shows a typical membership database. We had a web designer offer to build an expensive PHP (Hypertext Preprocessor) database on the web to display the membership directory and allow maintenance of members.

Figure 16.11. 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 organization is already maintaining this data in Excel. Using the code from Figure 16.10, you pretty much 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 membership directory Excel file, 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 16.12 shows the simple Bottom worksheet.

Figure 16.12. Two worksheets are added to the membership directory workbook. One lists the HTML code necessary to draw the navigation bar. The second lists the HTML code necessary to finish the web page after displaying the data.

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 = "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
        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, "<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)
        Print #1, Addr & "<br>"
        ' 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
        Print #1, WSB.Cells(j, 2).Value
    Next j
    Close #1

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

End Sub

Figure 16.13 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 can maintain the look and feel of the rest of the site.

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

This system has many advantages. 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.

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 www.pacific.net/~ken/software/. 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.pacific.net/~ken/software/
' 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