Creating dynamic link to web page in Excel

stevesansan

New Member
Joined
Apr 7, 2010
Messages
5
I am using Excel 2007 on Windows 7 Pro
I have a spreadsheet which is updated from a website. Presently this works from a permanent URL.
I am looking to create a dynamic URL whereby Excel will have two cell which represent the start date and end date, which are to form part of the websire URL. (Presently I have to manually change the URL to change the date ranges).
How do I create the macro and iqy to make the URL dynamic?
Thanks
steve
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hi Andrew
The end part of URL is as follows:-

....\ManagementReport.aspx?startdate=2010/03/01&enddate=2010/03/31

What I want to achieve is two cells in Excel that have the dates for the "startdate" and "enddate" parameters such that the URL used in the "Get External Data - from Web" reflects this.

Hope this makes sense

steve
 
Upvote 0
You can build it up with concatenation like this:

"....\ManagementReport.aspx?startdate=" & Format(Range("A1").Value, "yyyy/mm/dd") & "&enddate=" & Format(Range("A2").Value, "yyyy/mm/dd")
 
Upvote 0
Thanks Andrew for the help
I now have the URL in a cell in Excel (through the concatenation).

How do I now automatically get this text into the "Address" box of the "New Web Query" to create access to the External Data?

Thanks

steve
 
Upvote 0
You could copy it and paste it into the dialog. But a macro is probably the way to go. Record one and you will get some code you can edit.
 
Upvote 0
Hi Andrew

Thanks for your perserverance with me!

I have created the macro and tried it with a static URL - it works:-

Sub Macro5()
'
' Macro5 Macro
'
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;<TABLE style="WIDTH: 67pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=89><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 67pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=89>http://........../managementreport.aspx?StartDate=2010/03/01&enddate=2010/03/31</TD></TR></TBODY></TABLE>", _
Destination:=Range("a1"))

.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SaveData = True
End With
'
End Sub


Question - how do I change the URL to be the cell reference which contains my URL?

Thanks for all your help

steve
 
Upvote 0
It would be something like:

Code:
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" & Range("A1").Value, _
        Destination:=Range("a1"))

or:

Code:
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://........../managementreport.aspx?StartDate=" & Format(Range("A1").Value, "yyyy/mm/dd") & "&enddate=" & Format(Range("A2").Value, "yyyy/mm/dd"), _
        Destination:=Range("a1"))

Change the range references to suit.
 
Last edited:
Upvote 0
Hi Andrew

It all works!

Thanks for all your help and support - I would not have got the solution without your expertise

kind regards
steve
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top