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).
excelscreenshot.png
Help!

Thanks :)
LVL 1
CodestoneAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
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 AdvisorCommented:
This usually requires VBA code. The connection string URL does not allow for variable parts.
CodestoneAuthor Commented:
Hi Saurabh, yes your assumption is correct
Saurabh Singh TeotiaCommented:
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...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CodestoneAuthor Commented:
Perfect - thanks :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.