Excel: Getting data from a dynamic URL

Codestone
Codestone used Ask the Experts™
on
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).
excelscreenshot.png
Help!

Thanks :)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015

Commented:
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...
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
This usually requires VBA code. The connection string URL does not allow for variable parts.

Author

Commented:
Hi Saurabh, yes your assumption is correct
Top Expert 2015
Commented:
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...

Author

Commented:
Perfect - thanks :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial