Article ID: 274787 - Last Review: October 6, 2011 - Revision: 5.0

How to create Web query files for use with Excel for Mac

View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
This article was previously published under Q274787

On This Page

Expand all | Collapse all

SUMMARY

This article explains how to create Web query files for use with Microsoft Excel for Mac.
Back to the top

MORE INFORMATION

Web queries allow you to query data from a specific World Wide Web, Internet, or intranet site and retrieve the information directly into a Microsoft Excel worksheet. Microsoft Excel includes some sample Web queries.
Back to the top

Definition of a Web Query File

A Web query file is a text file that contains from one to four lines of text. You can create Web query files in any text editor, such as SimpleText or TextEdit (with preferences set to text).

Note You must save query files as text files with no formatting. Rich Text Format (RTF) files are not recognized.

Web query files are saved in the following folder on your computer's hard disk:
Microsoft Office 2001:Office:Queries

-or-

Microsoft Office X/Office/Queries (in Mac OS X)
Back to the top

How to Create a Web Query File

To create a Web query file, follow these steps:
  1. Start a text editor, such as SimpleText or TextEdit (with preferences set to text).
  2. Type the four lines of text in the text editor; use the following information:

    First Line: Type of Query

    The first line in the Web query file tells Microsoft Excel what type of query the file contains. At this time, the following are the only valid query types:
    WEB
    <line omitted>
    If you omit the line, WEB is the assumed value.

    Second Line: Version of Query

    The second line of a Web query file tells Microsoft Excel what version of the query is being executed. At this time, the following are the only valid versions:
    1
    <line omitted>
    Note If you specify the type of query in the first line, you must specify a version in the second line. If you omit the type, you must also omit the version.

    Third Line: Uniform Resource Locator (URL)

    The third line of a Web query file determines the Web document on which the query acts. Unless the Web document is a POST type (see the "Fourth Line: POST Parameters" section), this information is the only required value in the Web query file.

    Type the URL in one of the following formats. 
          File location                   URL format
      -------------------------------------------------------
      Web document                    http://<server>/<file>
      File stored locally             <drive>:<folder>:<file>
      File stored on a network        <drive>:<folder>:<file>
    where file is the name of the document, drive is the drive that contains the file, and folder is the folder name that contains the file.

    Fourth Line: POST Parameters

    The fourth line of a Web query file contains POST parameters. Note that this line is optional and must be included only if the third line (the URL) exceeds 200 characters in length as a result of adding parameters.

    When you query a Web document for information, the parameters sent to the Web document can be sent in one of two ways: GET or POST.

    When you use the GET method, data values are included in the same line as the URL. The following example illustrates how to type the line
    http://server/file?parameters
    where server is the name of the server that contains the Web document, and file is the name of the document.

    When you use the POST method, data values are sent in a separate line. The following example illustrates how to type the line
    http://server/file parameters
    where server is the name of the server that contains the Web document, and file is the name of the document.
  3. After you type all the required lines, save the new file as a text file in the following folder:
    Microsoft Office 2001:Office:Queries

    -or-

    Microsoft Office X/Office/Queries (in Mac OS X)
  4. Quit the text editor.
  5. Use Finder to navigate to the saved text file.
  6. Click the file name to highlight the name of the file in the a box.

    Note Do not open the file.
  7. Change the file extension from .txt to .iqy, and then click in an area outside the box to complete the change. When you are prompted to, select Keep .iqy.
Note If you do not change the file extension to .igy the Web query will appear in the list when you click Data, click Get External Data, and then click Run saved query. However, the query will be dimmed and you will not be able to select it.
Back to the top

How to Use Static and Dynamic Parameters in a Web Query

In Web queries, you can use both static and dynamic parameters. Static parameters send query data without prompting you for any values. Dynamic parameters prompt you to type one or more values when the Web query run.

The syntax for parameters is as follows
parameter=value string
where parameter is the name of a parameter (for example, stock) and value string is a value.

The value string can be one of the following values: 
   Parameter type   Value string
   -----------------------------------------------------------
   Static           value1
   Dynamic          ["value1","Please enter the first value:"]
In the dynamic value string, the first argument (value1) is the name of the value to be entered. The second argument is the message that appears when the Web query is executed.

If multiple parameters are required, separate them with an ampersand character (&). The following example illustrates the syntax:
quote1=stock1&quote2=stock2
When multiple values are sent for a single parameter, separate them with a plus sign (+). The following example illustrates the syntax:
quote1=stock1+stock2&quote2=stock3+stock4
You can combine static and dynamic parameters within a single parameter string. The following example illustrates the syntax:
quote1=stock1&quote2=["stock2","Please enter the second value:"]
Back to the top

Examples of Web Query Files

The following examples demonstrate the syntax used by Web query files.

Note The following examples are not functional Web queries.

Example 1:

This example sends the value "wyut" to the Client.exe program and retrieves a value. You are not prompted to type any values. This example sends the parameter by using the GET method.
WEB
1
http://www.weylandyutani.com/cgi-bin/client.exe?quote=wyut
If you like, you can omit the first two lines and use the following line:
http://www.weylandyutani.com/cgi-bin/client.exe?quote=wyut
Note It is acceptable to omit the first two lines when you create the Web query file. You can also omit these lines for all of the examples shown in this article.

Example Two:

This example sends the values "wyut" and "hypd" to the Client2.exe program, and then retrieves values. You are not prompted to type any values. This example sends the parameters by using the POST method.
WEB
1
http://www.weylandyutani.com/cgi-bin/client2.exe quote1=wyut&quote2=hypd
Example Three:

This example prompts you to type a stock symbol, sends that symbol to the Client.exe program, and retrieves a value. This examples sends the parameters by using the GET method.

Note Type the third and fourth lines of this example on a single line in your text file.
WEB
1
http://www.weylandyutani.com/cgi-bin/client.exe?quote1=
["stock1","Please enter a stock symbol:"]
Example Four:

This example sends the value "wyut" and a value that you type to the Client2.exe program, and then retrieves the values. This example sends the parameters by using the POST method.
WEB
1
http://www.weylandyutani.com/cgi-bin/client2.exe
quote1=wyut&quote2=["stock2","Please enter a stock symbol:"]
Back to the top
APPLIES TO
  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
Back to the top
Keywords: 
dftsdahomeportal kbhowto kbweb KB274787
Back to the top