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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to the Board.

What's the URL and which parts do you want to make dynamic using which cells?
 

stevesansan

New Member
Joined
Apr 7, 2010
Messages
5
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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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")
 

stevesansan

New Member
Joined
Apr 7, 2010
Messages
5

ADVERTISEMENT

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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

stevesansan

New Member
Joined
Apr 7, 2010
Messages
5

ADVERTISEMENT

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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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:

stevesansan

New Member
Joined
Apr 7, 2010
Messages
5
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
 

Forum statistics

Threads
1,136,336
Messages
5,675,184
Members
419,553
Latest member
hanahass

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
Top