In this section we will go through How to Create Dynamic Web Query in Excel.
Before staring this you should have knowledge about Excel web Query. You can refer to my previous post Excel Web Query.
There are 2 way to create dynamic web query:
Before staring this you should have knowledge about Excel web Query. You can refer to my previous post Excel Web Query.
There are 2 way to create dynamic web query:
- By Macro: VBA program contains the logic to create dynamic web query and fetch from the internet. For more detail please refer to MicroSoft site Or ExcelForum.
- By Parameters: Macro is not preferable for small task. If website permits simple query with parameter, It is always better to use this method.
I'm going to explain about How to Create Dynamic Web Query by Parameters means by Using values from cells.First we learn about how to Create Web Query (iqy) Files. You can also refer to Microsoft site for this. Or Follow following steps:
Select location. After selecting location another dialogue will be sown for parameter address. Point cell where are you like to enter you parameter value.
Now web query will fetch data as per this cell value.
In Excel 2003 these initial steps are little different. Iqy file can be selected from Data-->Import External Data--> Import Data
And select iqy file and rest steps are same as Excel 2007.
You can edit web query parameter as below
Parameters dialogue box will open here you can change text, parameter location.
I think I've provided steps to create dynamic web query with 1 parameter, similarly multiple parameter query can be created. You can download Excel Example from http://searchadres.com and Stock.iqy from Here
- Open any text editor, such as notepad
- Type "WEB" in first line
- Type Version. example "1"
- Type Website address without parameter. If full website address is 'http://finance.yahoo.com/q?s=MARUTI.NS' here parameter value 'MARUTI.NS'. so we type "'http://finance.yahoo.com/q?" in 3rd line
- On last line we give name of parameter and text for popup dialogue as I've give 'stock1' so 4th line will be "s=["stock1","Please enter a stock symbol:"]"
- Save this file with extension .iqy
I save as Stock.iqy and my file look like
WEB
1
http://finance.yahoo.com/q?
s=["stock1","Please enter a stock symbol:"]
Above stock.iqy file will download given company quotes that can be used to create watch list or portfolio.
How to use this dynamic web query (Iqy) File:
For Excel 2007:
Open Data--> Connections
Workbook Connections will be Open. Click on 'Add' button and select your *.iqy file.
Set connection properties by clicking on 'Properties' button.
Close this form.
Select Empty cell in Excel and Open Data--> Existing Connections.
Existing Connections form will be open as below. Select your iqy file and click on 'Open' button. Here you can select *.iqy file and open as well.
Open Data--> Connections
Workbook Connections will be Open. Click on 'Add' button and select your *.iqy file.
Set connection properties by clicking on 'Properties' button.
Close this form.
Select Empty cell in Excel and Open Data--> Existing Connections.
Existing Connections form will be open as below. Select your iqy file and click on 'Open' button. Here you can select *.iqy file and open as well.
Select location. After selecting location another dialogue will be sown for parameter address. Point cell where are you like to enter you parameter value.
In Excel 2003 these initial steps are little different. Iqy file can be selected from Data-->Import External Data--> Import Data
You can edit web query parameter as below
welding titanium for welding titanium - TITanium Art
ReplyDeleteGet ready 4x8 sheet metal prices near me for welding titanium. We've put together titan metal the best oakley titanium glasses and most reliable titanium models from the titanium body armor world's best titanium tubing manufacturers.