Ok, what we need here is a macro rather than a cell formula. The code
snippet below presumes you want that same link modified by a date that will
be in cell A1. The macro will need to be run when that sheet is the selected
sheet. It may need to be 'fine tuned' later to delete any prior information
in it, but that could be done manually for the time being.
You may want/need to do this in a new workbook. Type in a valid date into
cell A1 on a sheet. Then Record a macro to do what you want to do. Then
stop recording.
Choose Tools | Macro | Macros and click the [Edit] button. The VB Editor
will open up and show you the code created. Somewhere in it you are going to
seem a line that starts out like this:
With ActiveSheet.QueryTables.Add(Connection:=
with the url you entered following that := in the line.
That is what we have to get modified for you. We also need to kill of the
previously defined instance of this web query. So put this code ahead of
that line of code:
Dim qtEntry As QueryTable
Dim qryConnect As String
On Error Resume Next
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.QueryTable.Delete
Selection.ClearContents
Range("A2").Select
On Error GoTo 0
qryConnect = "URL;
http://www.hkex.com.hk/futures/futuresdayrpt/hsio" _
& Right(Year(Range("A1")), 2)
If Month(Range("A1")) < 10 Then
qryConnect = qryConnect & "0" & Month(Range("A1"))
Else
qryConnect = qryConnect & Month(Range("A1"))
End If
If Day(Range("A1")) < 10 Then
qryConnect = qryConnect & "0" & Day(Range("A1"))
Else
qryConnect = qryConnect & Day(Range("A1"))
End If
qryConnect = qryConnect & ".htm"
And then change that first line of code to use qryConnect instead of the
literal that it started out with:
With ActiveSheet.QueryTables.Add(Connection:=qryConnect, Destination _
:=Range("$A$2"))
The rest of the macro you should be able to leave alone. If you do happen
to delete the previously retrieved data, you'll get a prompt about deleting
just the data or the data and the query. Go ahead and respond [Yes] to kill
off the query along with it. The code is killing it and rebuilding it anyhow.
This appears to work for me, and I ran it multiple times, in Excel 2007 and
I've done similar thing in the past in Excel 2003, so I think it will work
for you. To get it to do its work: Tools | Macro | Macros and highlight the
name in the list (you can rename it while you're in there editing or when you
start recording it) and click the [Run] button.
Good luck.
Eric said:
Thank you for your suggestion
I am trying to retrieve external source through Data>External Source>insert
the link in wizard, then it will retrieve all web content into excel
spreadsheet. Once I insert this link into this wizard, next time, I only need
to click the update button for getting the updated the content. However, I
get the problem with the link n wizard, since the parameter of the link is
based on date format, and I don't want to update this link everytime I update
the content, therefore setting variable parameter into the link is necessary.
Do you have any suggestion on this issue?
Thank you very much for your reply
Eric
:
Eric,
That formula, when placed into a cell, works for me to get connected to
them. I presumed you were trying to connect from a link in a cell.
If the value in A1 is actually a date and not text, then just change the
references to NOW() to A1 in the formula:
=HYPERLINK("
http://www.hkex.com.hk/futures/futuresdayrpt/hsio" &
RIGHT(YEAR(A1),2) & IF(MONTH(A1)<10,"0" & MONTH(A1),MONTH(A1)) &
IF(DAY(A1)<10,"0" & DAY(A1),DAY(A1)) & ".htm","View Todays Updates")
remember, that's all one line - not actually broken up like this forum
tends to do to long formulas.
:
If the date is given in cell A1 [2-Jan-07], do you have any suggestion on how
to link the date from cell A1 in 070102 format?
Thank you very much
Eric
:
Try this - it's all one formula:
=HYPERLINK("
http://www.hkex.com.hk/futures/futuresdayrpt/hsio" &
RIGHT(YEAR(NOW()),2) & IF(MONTH(NOW())<10,"0" & MONTH(NOW()),MONTH(NOW())) &
IF(DAY(NOW())<10,"0" & DAY(NOW()),DAY(NOW())) & ".htm","View Todays Updates")
:
Does anyone know how to modify the web link for retrieving data from external
source? such as
http://www.hkex.com.hk/futures/futuresdayrpt/hsio070102.htm
This parameter "
http://www.hkex.com.hk/futures/futuresdayrpt/hsio" does not
change, but this part "070102.htm" will be changed based on the current date.
I cannot use indirect function under external linkage, does anyone have any
idea on solving this problem?
Thank you in advance
Eric
