FIGURE 26.1. The Insert Hyperlink dialog box is used to insert a hyperlink in a Microsoft Excel spreadsheet.
| Chapter 26 | HTML PROGRAMMING AND WEB QUERIES |
The dramatic growth of the Internet has made it possible to gain access to enormous knowledge archives scattered all over the world. Thanks to the Internet, we now have at our fingertips databases covering various industries and fields of knowledge, dictionaries and encyclopedias, stock quotes, maps, weather forecasts, and a great deal of other types of information stored on millions of Web servers. Often, the information retrieved from Web pages becomes a subject of further analysis by computer programs. Thanks to its file structure (rows and columns), Microsoft Excel is a preferred tool for working with table data found on the Internet. Using Excel, you can easily create, publish, review, and analyze data. This chapter demonstrates the built-in tools available in Excel 2016 for retrieving data from the Web and publishing Excel spreadsheets on the Web. You will find many Visual Basic statements here that will allow you to obtain and publish data using custom Visual Basic procedures. To maximize your benefit from this chapter, you should have a connection to the Internet.
Excel, like other applications in Microsoft Office, allows you to create hyperlinks in your spreadsheets. After clicking on a cell that contains a hyperlink, you can open a document located on a network server, an intranet, or the Internet. Hyperlinks can be created manually by choosing Insert | Hyperlink (as shown in Figure 26.1) or programmatically using VBA.
FIGURE 26.1. The Insert Hyperlink dialog box is used to insert a hyperlink in a Microsoft Excel spreadsheet.
In VBA, each hyperlink is represented by a Hyperlink object. To create a hyperlink to a Web page, use the Add method of the Hyperlinks collection. This method is shown below:
Expression.Hyperlinks.Add(Anchor, Address, [SubAddress], [ScreenTip], [TextToDisplay])
The arguments in square brackets are optional. Expression denotes a worksheet or range of cells where you want to place the hyperlink. Anchor is an object to be clicked. This can be either a Range or Shape object. Address points to a local network or a Web page. SubAddress is the name of a range in the Excel file. ScreenTip allows the display of a screen label. TextToDisplay is the name that you’d like to display in a spreadsheet cell for a specific hyperlink.
Let’s see how to programmatically place a hyperlink in a worksheet cell. The hyperlink you are going to create should take you to the Yahoo!® site when clicked.
|
Please note files for the “Hands-On” project may be found on the companion CD-ROM. |
|
Hands-On 26.1 Using VBA to Place a Hyperlink in a Worksheet Cell |
1. Open a new workbook and save it as C:\VBAExcel2016_ByExample\Chap26_VBAExcel2016.xlsm.
2. Switch to the Visual Basic Editor screen and insert a new module into VBAProject (Chap26_VBAExcel2016.xlsm).
3. In the Code window, enter the code of the FollowMe procedure shown below:
Sub FollowMe()
Dim myRange As Range
Set myRange = Sheets(1).Range("A1")
myRange.Hyperlinks.Add Anchor:=myRange, Address:="http://search.yahoo.com/", ScreenTip:="Search Yahoo", TextToDisplay:="Click here"
End Sub
4. Run the FollowMe procedure.
When you run the FollowMe procedure, cell A1 in the first worksheet will contain a hyperlink titled “Click here” with the screen tip “Search Yahoo” (see Figure 26.2). If you are now connected to the Internet, clicking on this hyperlink will activate your browser and load the Yahoo! search engine, as shown in Figure 26.3.
FIGURE 26.2. This hyperlink was placed in a worksheet by a VBA procedure.
FIGURE 26.3. The main page of the Yahoo! search engine was activated by clicking on the hyperlink placed in a worksheet cell (see Figure 26.2).
If you’d rather not place hyperlinks in a worksheet but want to make it possible for a user to reach the required Internet pages directly from an Excel worksheet, you can use the FollowHyperlink method. This method allows you to open the required Web page without the need to place a hyperlink object in a worksheet. The FollowHyperlink method looks like this:
Expression.FollowHyperlink(Address, [SubAddress], [NewWindow], [AddHistory], [ExtraInfo], [Method], [HeaderInfo])
Again, the arguments in square brackets are optional. Expression returns a Workbook object. Address is the address of the Web page that you want to activate. SubAddress is a fragment of the object to which the hyperlink address points. This can be a range of cells in an Excel worksheet. NewWindow indicates whether you want to display the document or page in a new window; the default setting is False. The next argument, AddHistory, is not currently used and is reserved for future use. ExtraInfo gives additional information that allows jumping to the specific location in a document or on a Web page. For example, here you can specify the text for which you want to search. Method specifies the method in which the additional information (ExtraInfo) is attached. This can be one of the following constants: msoMethodGet or msoMethodPost. When you use msoMethodGet, ExtraInfo is a string that’s appended to the URL address. When using msoMethodPost, ExtraInfo is posted as a string or byte array. The last optional argument, HeaderInfo, is a string that specifies header information for the HTTP request. The default value is an empty string.
Let’s use the FollowHyperlink method in a VBA procedure. The purpose of this procedure is to use the Bing search engine to find pages containing the text entered in a worksheet cell.
|
Hands-On 26.2. Using a Search Engine to Find Text Entered in a Worksheet Cell |
1. Insert a new sheet into the current workbook.
2. Switch to the Visual Basic Editor window, and double-click the Sheet2 (Sheet2) object in the Microsoft Excel Objects folder located in VBAProject (Chap26_VBAExcel2016.xlsm).
3. In the Sheet2 Code window, enter the Worksheet_BeforeDoubleClick event procedure shown below (you may want to review Chapter 15 on creating and using event procedures in Excel):
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim strSearch As String
strSearch = Sheets(2).Range("C3").Formula
If Target = Range("C3") Then
Cancel = True
ActiveWorkbook.FollowHyperlink Address:="http://www.bing.com/search", ExtraInfo:="q=" & strSearch, Method:=msoMethodGet
End If
End Sub
4. Switch to the Microsoft Excel application window. In cell C3 on Sheet2, enter any word or term about which you want to find information (see Figure 26.4).
FIGURE 26.4. A Microsoft Excel worksheet can be used to send search parameters to any search engine on the Internet.
5. Make sure that you are connected to the Internet. Double-click cell C3. This will cause the text entered in cell C3 to be sent to the Bing search engine. The screen should show the index to found topics with the specified criteria (see Figure 26.5).
FIGURE 26.5. A Web page opened from a Microsoft Excel worksheet lists topics that were found based on the criteria entered in a worksheet cell (see Figure 26.4).
Excel 2016 allows you to save files in the Hypertext Markup Language (HTML) format using .htm or .html file extensions. When you save an Excel file in the HTML format, you can view your worksheets using an Internet browser such as Internet Explorer®, Firefox®, Chrome, or Safari®. When you save a workbook or a portion of a workbook as HTML, the user will be able to view the file either in the browser or inside the Microsoft Excel application window.
You can save your HTML files directly to the Web server, a network server, or a local computer. To do this, click File | Save As. Select the folder for the file location, and in the Save as type drop-down box, choose Web Page. Working with nothing other than the user interface (see Figures 26.6 and 26.7), you can place an entire workbook or selected sheets on the Web page. Detailed instructions on how to go about placing an entire workbook or any worksheet (or its elements, such as charts or PivotTables) on a Web page can be found in the Excel online help. Because this book is about programming, we will focus only on the way these tasks are performed via VBA code.
FIGURE 26.6. The Save As dialog box allows users to save their workbook as a Web page.
FIGURE 26.7. The Publish as Web Page dialog box appears after clicking the Publish button on the Save As dialog box (see Figure 26.6).
The VBA object library offers objects for publishing worksheets on Web pages. To programmatically create and publish Excel files in the HTML format, you should become familiar with the PublishObject object and the PublishObjects collection.
PublishObject represents a worksheet element that was saved on a Web page, while PublishObjects is a collection of all PublishObject objects of a specific workbook. To add a worksheet element to the PublishObjects collection, use its Add method. This method will create an object representing a specific worksheet element that was saved as a Web page. The format of the Add method looks like this:
Expression.Add(SourceType, Filename, Sheet, Source, HtmlType, [DivID], [Title])
The arguments in square brackets are optional. Expression returns an object that belongs to the PublishObjects collection. SourceType specifies the source object using one of the following constants:
Value |
Description |
|
|
3 |
An AutoFilter range |
|
5 |
A chart |
|
6 |
A PivotTable report |
|
2 |
A range of cells selected for printing |
|
7 |
A query table (an external data range) |
|
4 |
A range of cells |
|
1 |
An entire worksheet |
|
0 |
A workbook |
Filename is a string specifying the location where the source object (SourceType) was saved. This can be a Uniform Resource Locator (URL) or the path to a local or network file. Sheet is the name of the worksheet that was saved as a Web page. Source is a unique name that identifies a source object. This argument depends on the SourceType argument. Source is a range of cells or a name applied to a range of cells when the SourceType argument is the xlSourceRange constant. If the SourceType argument is a constant such as xlSourceChart, xlSourcePivotTable, or xlSourceQuery, Source specifies the name of a chart, PivotTable report, or query table. HtmlType specifies whether the selected worksheet element is saved as static (non-interactive) HTML or an interactive Microsoft Office Web Component (this feature was depreciated in Excel 2007 and is used only for backward compatibility. See the side note for more information.). HtmlType constants are listed below:
HtmlType Constants |
Description |
|
Use the Spreadsheet component. This component makes it possible to view, analyze, and calculate spreadsheet data directly in an Internet browser. This component also has options that allow you to change the formatting of fonts, cells, rows, and columns. |
|
Use the Chart component. This component allows you to create interactive charts in the browser. |
|
Use the PivotTable component. This component allows you to rearrange, filter, and summarize information in a browser. This component is also able to display data from a spreadsheet or a database (for instance, Microsoft Access, SQL Server, or OLAP servers). |
|
(default value) Use static (non-interactive) HTML for viewing only. The data published in an HTML document does not change. |
NOTE |
The Office Web Components (OWC) are ActiveX controls that provide four components: Spreadsheet, Chart, PivotTable, and Data Source Control (DSC). In the Office releases prior to 2007 (XP/2003/2000), these components made it possible to use Excel analytical options in an Internet browser. The Office Web Components were discontinued in Office 2007. If you need OWC to support older applications, you will need to reinstall these components or allow users to download and install them on the fly when the document that requires their use is opened in a browser. |
DivID is a unique identifier used in the HTML DIV tag to identify the item on the Web page. Title is the title of the Web page.
Before we look at how you can use the Add method from a VBA procedure, you also need to learn how to use the Publish method of the PublishObject object. This method allows publishing an element or a collection of elements in a particular document on the Web page. This method is quite simple and looks like this:
Expression.Publish([Create])
Expression is an expression that returns a PublishObject object or PublishObjects collection. The optional argument, Create, is used only with a PublishObject object. If the HTML file already exists, setting this argument to True will overwrite the file. Setting this argument to False inserts the item or items at the end of the file. If the file does not yet exist, a new HTML file is created, regardless of the value of the Create argument.
Now that you’ve been introduced to VBA objects and methods used for creating and publishing an Excel workbook in HTML format, let’s get back to programming. In the following Hands-On, you will create an Excel worksheet with an embedded chart and publish it as static HTML.
|
Hands-On 26.3. Creating and Publishing an Excel Worksheet with an Embedded Chart |
1. Create a new workbook and save it as C:\VBAExcel2016_ByExample\PublishExample.xlsm.
2. Right-click Sheet1 and choose Rename. Type Help Desk, and press Enter.
3. In the Help Desk worksheet, enter data as shown in Figure 26.8. To create the chart, select cells A1:B10 and choose the Insert tab. In the Charts group, click the Column drop-down and select 2-D Column. Add Data labels to columns in the plot area and set other chart elements as depicted in Figure 26.8.
FIGURE 26.8. A worksheet like this one with an embedded chart can be placed on a Web page by using Save As and choosing Web Page as the file format or via a VBA procedure.
4. Activate the Visual Basic Editor window and insert a new module into VBAProject (PublishExample.xlsm).
5. In the Code window, enter the two procedures shown below:
' The procedure below will publish a worksheet
' with an embedded chart as static HTML
Sub PublishOnWeb(strSheetName As String, strFileName As String)
Dim objPub As Excel.PublishObject
Set objPub = ThisWorkbook.PublishObjects.Add(SourceType:=xlSourceSheet, Filename:=strFileName, Sheet:=strSheetName, HtmlType:=xlHtmlStatic, Title:="Calls Analysis")
objPub.Publish True
End Sub
Sub CreateHTMLFile()
Call PublishOnWeb("Help Desk", "C:\VBAExcel2016_ByExample\WorksheetWithChart.htm")
End Sub
The first procedure above, PublishOnWeb, publishes a Web page with a worksheet containing an embedded chart as static HTML. The second procedure, CreateHTMLFile, calls the PublishOnWeb procedure and feeds it the two required arguments: the name of the worksheet that you want to publish and the name of the HTML file where the data should be saved.
6. Run the CreateHTMLFile procedure.
When this procedure finishes, a new file called C:\VBAExcel2016_ByExample\ WorksheetWithChart.htm is created on your hard drive. Also, there will be a folder named WorksheetWithChart_files for storing supplemental files.
7. Locate the C:\VBAExcel2016_ByExample\WorksheetWithChart.htm file and open it in your Internet browser (see Figure 26.9).
FIGURE 26.9. An Excel worksheet published as a static (non-interactive) Web page.
Over a decade ago Microsoft introduced in Excel a feature known as Web Query. This feature allowed users to retrieve data from a Web page for use and analysis in Excel. While Web Query is still present in the 2016 version, the Web page layouts have undergone so many changes in recent years that there are fewer and fewer pages that can be accessed using the Web Query. In this section we will take a look at a simple web query so that you are familiar with this concept.
To pull data from the Web via Web queries you must have an active Internet connection. You also need to know the address (URL) of the Web page that contains some data in tabular format. To access the Web Query, choose Data | Get External Data | From Web. Excel will display the New Web Query dialog box. By default, Excel will try to read data from your home page. To load your particular Web page, simply type its URL in the Address bar, as shown in Figure 26.10.
FIGURE 26.10. The New Web Query dialog box provides a convenient user interface for obtaining data from Web pages.
Web queries allow you to retrieve data from the Web directly into Microsoft Excel without having to know anything about programming. After placing data in a worksheet, you can use Excel tools to perform data analysis. A single table, a number of tables, or all the text that a particular Web site contains can be retrieved into a worksheet. Simply click the arrow next to the table you want to select and click Import (see Figure 26.10). You can get the data with or without formatting by choosing the Options button. Figure 26.11 shows the Excel worksheet after the retrieval of data (see the highlighted area in Figure 26.10). Notice that you can save the underlying Web query using the Save button in the New Web Query dialog. Web queries are stored in text files with the .iqy extension. The content of the .iqy file can be viewed by opening the file in any text editor (for example, Windows Notepad), as shown in Figure 26.12. Web queries can be also created manually by entering the required commands in a text file and saving the file with the .iqy extension.
FIGURE 26.11. Web data retrieved into a Microsoft Excel worksheet using the New Web Query dialog box (see Figure 26.10).
FIGURE 26.12. This Web query file was created by clicking the Save button in the New Web Query dialog (see Figure 26.10).
The .iqy files contain the following parts:
Section Name |
Description / Example |
Query Type (Optional section) |
Set to WEB when you use Version Section: WEB |
Query Version (optional section) |
Allows you to set the version number of a Web query. For example: 1 |
URL (required) |
The URL of the Web page where you will get your data. For example: http://www.lsjunction.com/facts/missions.htm You can send parameters by attaching them to the URL address using the question mark, as shown below: http://www.msn.com/en-us/money/quoteslookup?SYMBOL=GOOG SYMBOL is a parameter name. GOOG is a stock symbol (a parameter value) you want to retrieve from the specified URL. If a parameter requires multiple values, separate them with a plus sign (+). |
POST Parameters (optional section) |
You can send parameters to the Web server using the POST or GET method. This section is used for sending parameters with the POST method. The parameters need to be entered on a separate line, as in the following example:
http://www.yourwebsite.com/sales.exe
Category and item are the names of parameters, while the values that follow the equal signs (=) are the parameter settings. Parameters are separated from one another with the ampersand symbol (&). For more information, please see: http://support.microsoft.com/kb/187364 |
Web queries can be static or dynamic. Static queries always return the same data, while dynamic queries allow the user to specify different parameters to narrow down the data returned from the Web page.
In the previous section, you learned that a Web query can be created by using a command on the Ribbon or typing special instructions in a text editor such as Notepad. The third method of creating a Web query is through a VBA statement.
To programmatically create a Web query, use the Add method of the QueryTables collection. This collection belongs to the Worksheet object and contains all the QueryTable objects for a specific worksheet. The Add method returns the QueryTable object that represents a new query. The format of this method is shown below:
Expression.Add(Connection, Destination,[Sql])
Expression is an expression that returns the QueryTable object. Connection specifies the data source for the query table. The data source can be one of the following:
• A string containing the address of the Web page in the form “URL; <url>”. For example:
"URL;http://www.usa.gov/"
See Hands-On 26.6 for a procedure example that uses the URL connection.
• A string indicating the path to the existing Web query file (.iqy) using the form “FINDER; <data finder file path>”. For instance:
"FINDER;C:\VBAExcel2016_ByExample\www.x-rates.iqy"
• A string containing an OLEDB or ODBC connection string. The ODBC connection string has the form “ODBC; <connection string>”. For instance:
"ODBC;DSN=MyNorthwind;UID=;PWD=;" & "Database=Northwind 2007"
Here’s how this could be used to retrieve data from the Northwind 2007.accdb file:
Sub GetAccessData()
Dim strSQL As String
Dim strConn As String
strSQL = "Select * from Shippers"
strConn = "ODBC;DSN=MyNorthwind;UID=;PWD=;" & "Database=Northwind 2007"
Sheets.Add
With ActiveSheet.QueryTables.Add(Connection:=strConn, Destination:=Range("B1"), Sql:=strSQL)
.Refresh
End With
End Sub
To try out this procedure, start by creating the MyNorthwind data source name. You can do this via the ODBC Data Sources link (Control Panel\System and Security \Administrative Tools). In the ODBC Data Source Administrator dialog (the User DSN tab), click the Add button. Next, select the Microsoft Access Driver (*.mdb, *.accdb) and click the Finish button. Enter MyNorthwind in the Data Source Name text box, then click the Select button, and choose the C:\VBAExcel2016_ByExample\Northwind 2007.accdb file. Click OK to confirm your selection. Next, click OK to exit the ODBC Microsoft Access setup dialog. MyNorthwind should appear in the list of the User Data Sources. Click OK to close the ODBC Data Source Administrator dialog. Enter the procedure in a standard module of any workbook and then run it.
• An ADO or DAO Recordset object. Microsoft Excel retains the recordset until the query table is deleted or the connection is changed. The resulting query table cannot be edited.
• A string indicating the path to a text file in the form “TEXT; <text file path and name>”. For instance:
"TEXT;C:\VBAExcel2016_ByExample\NorthEmployees.csv"
The following example procedure opens the comma-separated file in Excel:
Sub GetDelimitedText()
Dim qtblOutput As QueryTable
Sheets.Add
Set qtblOutput = ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\VBAExcel2016_ByExample\
NorthEmployees.csv", Destination:=ActiveSheet.Cells(1, 1))
With qtblOutput
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
'.TextFileOtherDelimiter = "Tab"
.Refresh
End With
End Sub
Destination is the cell in the upper-left corner of the query table destination range (this is where the resulting query table will be placed). This cell must be located in the worksheet containing the QueryTable object used in the expression. The optional argument, Sql, is not used when a QueryTable object is used as the data source.
NOTE |
Be careful not to include spaces in the connection strings as this will cause runtime error 1004 during the execution of a VBA procedure. |
The following example procedure creates a new Web query in the active workbook. The data retrieved from a Web page is placed in a worksheet as static text.
|
1. Open a new workbook and save it as C:\VBAExcel2016_ByExample\MyWebQueries.xlsm.
2. Switch to the Visual Basic Editor window and insert a new module in VBAProject (MyWebQueries.xlsm).
3. In the Code window, enter the GetIndexForEnergy procedure, which retrieves Index for Energy from Yahoo investing site.
Sub GetIndexForEnergy()
' create a web query in the current worksheet
' connect to the web, retrieve data, and paste it
' in the worksheet as static text
Sheets.Add
With ActiveSheet.QueryTables.Add(Connection:="URL;http://finance.yahoo.com/currency-investing", Destination:=Range("A2"))
.Name = "IndexForEnergy"
.BackgroundQuery = True
.WebSelectionType = xlSpecifiedTables
.WebTables = "yfi-commodities-energy"
.WebFormatting = xlWebFormattingNone
.Refresh BackgroundQuery:=False
.SaveData = True
End With
End Sub
4. Switch to the Microsoft Excel application window and choose Developer | Macros.
5. In the Macros dialog box, highlight the GetIndexForEnergy procedure and click Run.
While the procedure executes, the following tasks occur: (a) a connection is established with the specified Web page, (b) data from a Web page is retrieved, and (c) data is placed in a worksheet. When the procedure finishes executing, the active worksheet displays data as shown in Figure 26.13.
FIGURE 26.13. This data was retrieved from a Web page using the Web query in a VBA procedure.
Notice that this worksheet does not contain any hyperlinks because we set the WebFormatting property of the query table to xlWebFormattingNone in the procedure code. This property determines how much formatting from a Web page, if any, is applied when you import the page into a query table. You can use one of the following constants: xlWebFormattingAll, xlWebFormattingNone (this is the default setting), or xlWebFormattingRTF.
Setting the BackgroundQuery property of the QueryTable object to True allows you to perform other operations in the worksheet while the data is being retrieved from the Web page. The WebSelectionType property determines whether an entire Web page, all tables on the Web page, or only specific tables are imported into a query table. The WebSelectionType property can be one of the following constants: xlAllTables, xlEntirePage, or xlSpecifiedTables. The WebTables property specifies a comma-delimited list of table names or table index numbers when you import a Web page into a query table. After retrieving data from the Web page, you must use the Refresh method of the QueryTable object in order to display the data in a worksheet. If you omit this method in your procedure code, the data retrieved from the Web page will be invisible. By setting the SaveData property to True, the table retrieved from the Web page will be saved with the workbook.
6. Right-click anywhere within the data placed by the Web query in the worksheet. Choose Edit Query from the shortcut menu.
You will see the Edit Web Query window (Figure 26.14). You may need to scroll down to find the highlighted Energy table.
FIGURE 26.14. The Web Query Options dialog box.
7. Click the Options button located on the Edit Web Query window's toolbar to access the Web Query Options dialog box, as shown in Figure 26.14.
Notice that the option button None is selected in the Formatting area. This option button represents the xlWebFormattingNone setting of the WebFormatting property in the procedure code.
8. Press Cancel to close the Web Query Options dialog box and then click Cancel to exit the Edit Web Query window.
You often need to specify parameters in order to retrieve data from a Web page. To send parameters to the Web server in your Web query, use the POST or GET method after checking to see which of these methods the particular Web server uses. You can find this information as instructed below.
|
Hands-On 26.5. Checking for POST and GET Methods in Web Pages |
1. Activate your browser and enter the address of a Web page from which you want to retrieve information. For example, enter: http://www.xe.com/currencyconverter/. This will bring up the Web page shown in Figure 26.15.
FIGURE 26.15. This Web page allows you to convert one type of currency into another type.
2. Right-click anywhere in the text area of the Web page and choose View Source from the shortcut menu. The underlying code for this Web page appears in Developer Tools, as shown in Figure 26.16.
FIGURE 26.16. You can view the source of the underlying data for a particular Web page by selecting View Source from your browser’s shortcut menu or by selecting View Source from the Page drop-down in Internet Explorer.
3. While the source code is shown, press Ctrl+F to activate the Find box and type method=”get” as the text to search for.
If the parameters are being sent to the Web server using the GET method, the search string should appear highlighted, as shown in Figure 26.16. The same line includes an action property that specifies a URL of the Web server that supplies the data.
Now that you know which method is used to send the parameters, you need to find out how the parameters are called.
NOTE |
A Web page can contain more than one form; therefore, you will find more than one occurrence of the |
4. While the source code remains open, press Ctrl+F and type the following search string:
<input type="text" name=
When you click the Next button in the Find dialog box, the program will highlight the first match for the search string. Click the Next button until you find the name of the first parameter “Amount”. After the word value=, you should see the current value of this parameter. For example:
<input type="text" name="Amount" id="amount" class="ucc-amount inptStyle inputStyle-ucc" onfocus="if(this.value == '1') this.value='';" onblur="if(this.value == '') this.value='1' ;" value="1" maxlength="50" tabindex="1"/>
In the above HTML statement, the word “Amount” is the name of a parameter and “1” is the current value of this parameter. A value of the parameter can also be one of the options located in the HTML <option> tag. For example:
<select class="ccDbx" name="From" id="from" size="1" tabindex="2">
<option value='AUD'>AUD - Australian Dollar</option>
<option value='CAD'>CAD - Canadian Dollar</option>
<option value='CHF'>CHF - Swiss Franc</option>
<option value='CNY'>CNY - Chinese Yuan Renminbi</option>
<option value='DKK'>DKK - Danish Krone</option>
<option value='EUR'>EUR - Euro</option>
…
…
</select>
In the above HTML code segment, the word “From” is the name of a parameter. This parameter can have one of the following values: AUD, CAD, CHF, CNY, DKK, EUR, and so on.
If a Web server receives parameters using the GET method, you can see the parameter names and their values in your browser’s address bar:
http://www.xe.com/currencyconverter/convert/?Amount=1&From=USD&To=CAD
Notice that the first parameter (Amount) is preceded by a question mark. Parameters are separated from one another with the ampersand symbol (&).
5. Close the Developer Tools window.
Web query parameters can be static or dynamic. When you use static parameters, you do not need to enter any values when running a Web query (see the code of the Portfolio procedure in Hands-On 26.6). A static Web query always returns the same data. If you use dynamic parameters, you can specify one or more values as criteria for your data retrieval while executing your Web query (see the Portfolio2 procedure in Hands-On 26.7). A dynamic Web query returns data based on the supplied parameters.
In the following example you will learn how to create a static Web query programmatically. This Web query will send parameters to the Web server using the GET method. These parameters are static because you are not prompted to supply their values when the Web query is run. The values of the parameters that the Web server expects to receive are coded inside the VBA procedure.
|
Hands-On 26.6. Using the GET Method to Send Parameters to the Web Server |
1. In the Visual Basic Editor window, insert a new module into VBAProject (MyWebQueries.xlsm).
2. In the Code window, enter the code of the Portfolio procedure as shown below:
Sub Portfolio()
Dim sht As Worksheet
Dim qryTbl As QueryTable
' insert a new worksheet in the current workbook
Set sht = ThisWorkbook.Worksheets.Add
' create a new web query in a worksheet
Set qryTbl = sht.QueryTables.Add(Connection:="URL;http://moneycentral." & "msn.com/investor/external/excel/quotes.asp?" & "SYMBOL=GOOG&SYMBOL=YHOO", Destination:=sht.Range("A1"))
' retrieve data from web page and specify formatting
' paste data in a worksheet
With qryTbl
.BackgroundQuery = True
.WebSelectionType = xlSpecifiedTables
.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.Refresh BackgroundQuery:=False
.SaveData = True
End With
' delete unwanted rows/columns
With sht
.Rows("2").Delete
.Columns("B:C").Delete
.Rows("5:16").Delete
End With
End Sub
3. Switch to the Microsoft Excel application window and choose Developer | Macros.
4. In the Macro dialog box, highlight the Portfolio procedure and click Run.
The result of this procedure is shown in Figure 26.17. Notice that the previous Portfolio procedure imports data contained in the first table of the specified Web site (see the WebTables property setting). To retrieve data from two or more tables, separate their indices or names with commas. When setting the WebTables property, you must surround the table names or indices with quotation marks. To view the explanations of other properties used in this procedure, see the GetIndexForEnergy procedure in Hands-On 26.4.
FIGURE 26.17. A Web query can retrieve data from a specific table on a Web page.
When sending several values using the same parameter name, instead of repeating the parameter name (as shown in the Portfolio procedure), you can separate parameter values with the plus sign (+), like this:
http://www.msn.com/en-us/money/quoteslookup?SYMBOL=GOOG+YHOO
You can also use a comma as a separator:
http://www.msn.com/en-us/money/quoteslookup?SYMBOL=GOOG+YHOO
|
Which Table Should You Import? |
Web pages may contain many tables. Tables allow you to organize the content of the page. When viewing the HTML source code, you can easily recognize tables by the following tags: <table> (beginning of table) and </table> (end of table). The <td> tag indicates table data. This data will be placed in a worksheet cell when retrieved by Excel. Every new table row begins with the <tr> tag and ends with the </tr> tag. Many times, one table is placed inside another table (referred to as table nesting). Because tables are usually numbered in the HTML code, finding the correct table number containing the data you want to place in the worksheet usually requires experimentation. The New Web Query dialog box provides a visual clue to which tables a particular Web page contains (see Figure 26.10 earlier in this chapter). By clicking on the arrow pointing to the table, you can mark a particular table for selection. You can then click the Save Query button to save the query to a file. When you open the prepared .iqy file in Notepad, you will see the number or a name assigned to the selected table (as shown below) which you can use in your VBA procedure. |
|
|
IMPORTANT NOTE |
Web pages undergo frequent modifications. It’s not uncommon to find out that a Web query you prepared a while ago suddenly stops working because the Web page address or parameters required to process the data have changed. If you plan on using Web queries in your applications, you must watch for any changes introduced on Web sites that supply you with critical information. Particularly watch for table references. A reference to table “13” from a while ago could now be a totally different number or name, causing your Web query to retrieve data that you don’t care about or no data at all. |
Instead of hardcoding the parameter values in the code of your VBA procedures, you can create a dynamic query that will prompt the user for the parameter settings when the query is run. The Portfolio2 procedure shown below uses the GET method for sending dynamic parameters. This procedure displays a dialog box prompting the user to enter stock symbols separated by spaces.
|
Hands-On 26.7. Using the GET Method for Sending Dynamic Parameters |
1. In the Code window of VBAProject (MyWebQueries.xlsm), enter the following Portfolio2 procedure below the code of the Portfolio procedure:
Sub Portfolio2()
Dim sht As Worksheet
Dim qryTbl As QueryTable
' insert a new worksheet in the current workbook
Set sht = ThisWorkbook.Worksheets.Add
' create a new web query in a worksheet
Set qryTbl = sht.QueryTables.Add(Connection:="URL;http://moneycentral." & "msn.com/investor/external/excel/quotes.asp?" & "SYMBOL=[""Enter " & "symbols separated by spaces""]", Destination:=sht.Range("A1"))
' retrieve data from web page and specify formatting
' paste data in a worksheet
With qryTbl
.BackgroundQuery = True
.WebSelectionType = xlSpecifiedTables
.WebTables = "1"
.WebFormatting = xlWebFormattingAll
.Refresh BackgroundQuery:=False
.SaveData = True
End With
' delete unwanted rows/columns
With sht
.Rows("2").Delete
.Rows("6:18").Delete
.Columns("B:C").Delete
End With
End Sub
2. Switch to the Microsoft Excel application window and choose Developer | Macros.
3. In the Macro dialog box, highlight the Portfolio2 procedure and click Run.
When the Web query is activated, the Enter Parameter Value dialog box appears.
4. Enter the stock symbols in the dialog box as shown in Figure 26.18 and click OK.
FIGURE 26.18. Dynamic Web queries request parameter values from the user.
The data is retrieved from the specified Web page and placed in a worksheet.
You can refresh data retrieved from a Web page by using the Refresh option on the shortcut menu. To access this menu, right-click any cell in the range where the data is located.
The Refresh option is also available from the Ribbon's Data tab. Data can be refreshed in the background while you are performing other tasks in the worksheet, when the file is being opened, or at specified time intervals. You can specify when the data should be refreshed in the External Data Range Properties dialog box; right-click anywhere in the data range and choose Data Range Properties from the shortcut menu to bring up the dialog box shown in Figure 26.19.
FIGURE 26.19. After retrieving data from a particular Web page using the Web query, you can use the External Data Range Properties dialog box to control when data is refreshed and how it is formatted.
In this chapter, you were introduced to using Excel with the Internet. Let’s quickly summarize the information we’ve covered here:
• Hyperlinks allow you to activate a specified Web page from a worksheet cell.
• HTML files can be created and published from Excel by selecting the Web Page (*.htm; *.html) format type in the Save As dialog box or via VBA procedures.
• Web queries allow you to retrieve “live” data from a Web page into a worksheet. These queries can be created using built-in Ribbon commands or programmatically with VBA. Web queries let you retrieve an entire Web page or specific tables that a particular Web page contains. The retrieved data can be refreshed as often as required. There are two types of Web queries: static and dynamic.
The next chapter demonstrates how you can retrieve or send information to Excel via Active Server Pages.
