Creating and Running Web Queries with VBA

In the previous section you learned that a web query can be created by using a menu option or typing special instructions in a text editor such as Notepad. The third method of creating a web query is through VBA statements.

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.nycenet.edu/distschweb/searchresult.asp"

■ A string indicating the path to the existing web query file (.iqy) using the form " FINDER;<data finder file path>." For instance:

"FINDER;C:\Program Files\Microsoft Office\Office\Queries\ _ MSN MoneyCentral Investor Currency Rates.iqy"

■ A string containing an OLE DB or ODBC connection string. The ODBC connection string has the form "ODBC;<connection string>." For instance:

"ODBC;DSN=MyNorthwind;UID=NorthUser;PWD=UserPass;Database=Northwind"

■ 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:\myTextFile.txt"

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.

The example procedure shown below 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 MyWebQueries.xls.

2. Switch to the Visual Basic Editor window, and insert a new module in the current VBA Project (MyWebQueries.xls).

3. In the Code window, enter the Manhattan_Schools procedure, which retrieves a list of NYC schools located in Manhattan from the web.

Sub Manhattan_Schools()

' create a Web query in the current worksheet, connect to the Web, ' retrieve data and paste it in the worksheet as static text

With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://www.nycenet.edu/dist_sch/sch/" & _

"searchresult.asp?boro=Manhattan&flag=schoolInfo2", _ Destination:=Range("a1")) .BackgroundQuery = True .WebSelectionType = xlSpecifiedTables .WebTables = "Table3" .WebFormatting = xlWebFormattingNone .Refresh BackgroundQuery:=False .SaveData = True End With End Sub

4. Run the Manhattan_Schools procedure. 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 you activate the above procedure, the active worksheet will display names and addresses of Manhattan schools (Figure 16-14). Notice that this worksheet does not contain any hyperlinks because we set the WebFormatting property of the QueryTable 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: xlWebFor-mattingAll, xlWebFormattingNone (this is the default setting), or xlWebFormattingRTF. The BackgroundQuery property of the Query-Table object when set 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 on the web page 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, in order to display this data in a worksheet, you must use the Refresh method of the QueryTable object. 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.

j MyWebQueries.xls

HEQl

A

B

c

4

Roberto Clemente (M015)

AsherLevy (M019)

5

333 East 4 Brest

185 First Avenue

_

New York NY 10009

New York NY 10000

7

Phone: 212-22B-8T30

Phone:212-533-5340

3

Fa*: 212-477-0931

Fax: 212-073-1477

9

to

Anna Silver (M020)

Franklin D. Roosevelt (M034)

11

166 Essex areat

730 East 12 Street

New York NY 10002

New York NY 1QQK

13

Phone- 212-354-9577

Phone:

14

Fa*: 212-254-3526

Fax:

IS

IE 17

William Mckinley (M053J 121 East 3 areet

The Mangin School (M097) 525 East Houston Street

13

Wow York NY 10009

New York NY 10002

"Ï9

Phone' 212-674-31S0

Phone: 212-477-4140

20

Fax: 212-420-9013

Fax: 212-777-4702

22

Florence Nightingale P.S. #110 (M110)

Henrietta Sioid (M134)

23

285 Dalancey SlrBet

293 East Broadway

"24

New York NY 10002

NewYork NY 1000(2

25

Phone- 212-674-2690

Phone:212-673-4470

14 A

> HKSheet! / SheetZ / 5heet3 /

111 1

This data was retrieved from a web page using the web query in a VBA procedure.

5. Right-click anywhere within the data placed by the web query in the worksheet and choose Edit Query from the shortcut menu. You will see the Edit Web Query dialog box. Click the Options button located on this dialog box's toolbar to access the Web Query Options dialog box, as shown in Figure 16-15. Notice that in the Formatting area the option button None is selected. This option button represents the setting of xlWebFor-mattingNone of the WebFormatting property in the procedure code.

0 0

Post a comment