Excel: Getting data from a dynamic URL
Hi,
I have a pricing spreadsheet which enables you to choose a Month/Year on which to base the pricing (e.g. April 2015/June 2015 etc.).
I have multiple HTML pages on a web server (e.g. Pricing-Apr15.html, Pricing-June.html).
I want the user to pull down a month (e.g. April/May/June etc.) and then for the pricing sheet to pull the pricing data from the relevent HTML file on the webserver.
I don't see any way to make the Connection String dynamic in the Connection Properties (see screenshot, attached).
Help!
Thanks :)
I have a pricing spreadsheet which enables you to choose a Month/Year on which to base the pricing (e.g. April 2015/June 2015 etc.).
I have multiple HTML pages on a web server (e.g. Pricing-Apr15.html, Pricing-June.html).
I want the user to pull down a month (e.g. April/May/June etc.) and then for the pricing sheet to pull the pricing data from the relevent HTML file on the webserver.
I don't see any way to make the Connection String dynamic in the Connection Properties (see screenshot, attached).
Help!
Thanks :)
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Quick question if i understand correctly what you are trying to say that in your web sever the entire link remains constant the only change part is the months..Like in the below example of links...
http://www.moneycontrol.com/india/stockpricequote/banks-public-sector/statebankindia/SBI
http://www.moneycontrol.com/india/stockpricequote/banks-public-sector/bankofbaroda/BOB
The only changing part is after banks-public-sector..rest of the link is constant..let me know is my assumptions correct in this case??
Saurabh...
http://www.moneycontrol.com/india/stockpricequote/banks-public-sector/statebankindia/SBI
http://www.moneycontrol.com/india/stockpricequote/banks-public-sector/bankofbaroda/BOB
The only changing part is after banks-public-sector..rest of the link is constant..let me know is my assumptions correct in this case??
Saurabh...
Codestone..
Okay follow these steps...
1. Open Notepad
2. Type Web in first line and then enter and 1 and again enter
3. In third line copy and paste your url so like in my example the URL will be..
http://www.moneycontrol.com/india/stockpricequote/banks-public-sector/["Paramter1"]
Parameter 1 is basically the dynamic part in the URL which will keeps on changing...
4. Now save this file and rather then saving it as text file..select the option all files and save it with extension as .iqy
Make sure you save it by that extension of IQY
5. Now go to excel Data-->Existing Connections and browse for that path where you saved the above notepad file
6. Now as soon you open this it will ask for where you want to paste your data which is which cell of the worksheet. Choose your destination and Hit ok.
7. Now it will ask for which cell you will have the value of parameter1 which is basically dynamic part of your URL. Now you can select that cell where you will keep on changing this dynamic part and in additional you can check the box of use this value for reference for future response and refresh automatically when value changes.
8. Hit OK and now you will notice as soon the dynamic part of the url changes your data in excel changed.
Hope this helps..
Saurabh...
Okay follow these steps...
1. Open Notepad
2. Type Web in first line and then enter and 1 and again enter
3. In third line copy and paste your url so like in my example the URL will be..
http://www.moneycontrol.com/india/stockpricequote/banks-public-sector/["Paramter1"]
Parameter 1 is basically the dynamic part in the URL which will keeps on changing...
4. Now save this file and rather then saving it as text file..select the option all files and save it with extension as .iqy
Make sure you save it by that extension of IQY
5. Now go to excel Data-->Existing Connections and browse for that path where you saved the above notepad file
6. Now as soon you open this it will ask for where you want to paste your data which is which cell of the worksheet. Choose your destination and Hit ok.
7. Now it will ask for which cell you will have the value of parameter1 which is basically dynamic part of your URL. Now you can select that cell where you will keep on changing this dynamic part and in additional you can check the box of use this value for reference for future response and refresh automatically when value changes.
8. Hit OK and now you will notice as soon the dynamic part of the url changes your data in excel changed.
Hope this helps..
Saurabh...
Premium Content
You need an Expert Office subscription to comment.Start Free Trial