Does anyone know how to modify the web link for retrieving data from external
source? such as
http://www.hkex.com.hk/futures/futur...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

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")

"Eric" wrote:

Does anyone know how to modify the web link for retrieving data from external
source? such as
http://www.hkex.com.hk/futures/futur...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

Thank you for your suggestion

It seems to me when I put your given code into the link, the page cannot be
able to loaded, because some communication setting are required, could you
please give me any suggestion on how to setup this setting?
Thank you very much
Eric

"JLatham" wrote:

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")

"Eric" wrote:

Does anyone know how to modify the web link for retrieving data from external
source? such as
http://www.hkex.com.hk/futures/futur...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

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

"JLatham" wrote:

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")

"Eric" wrote:

Does anyone know how to modify the web link for retrieving data from external
source? such as
http://www.hkex.com.hk/futures/futur...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

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.

"Eric" wrote:

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

"JLatham" wrote:

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")

"Eric" wrote:

Does anyone know how to modify the web link for retrieving data from external
source? such as
http://www.hkex.com.hk/futures/futur...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

Thank you for your suggestion
I am trying to retrieve external source through DataExternal Sourceinsert
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


"JLatham" wrote:

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.

"Eric" wrote:

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

"JLatham" wrote:

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")

"Eric" wrote:

Does anyone know how to modify the web link for retrieving data from external
source? such as
http://www.hkex.com.hk/futures/futur...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

Or maybe a modification of JL's formula:

=HYPERLINK("http://www.hkex.com.hk/futures/futuresdayrpt/hsio" &
text(today(),"yymmdd") & ".htm","View Todays Updates")

Eric wrote:

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

"JLatham" wrote:

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")

"Eric" wrote:

Does anyone know how to modify the web link for retrieving data from external
source? such as
http://www.hkex.com.hk/futures/futur...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

--

Dave Peterson

Thank you for your suggestion

Hyperlink does work for cell, but it does not work through the function to
retrieve data from external source, which I intend to do. Under the tool
bars Data look for retrieve from External source function new web link
[I am using chinese office, and try to translate those function into english,
so you may not the translation accurately match in office english], if I
insert hyperlink function, then it does not work here.
Does you have any suggestion?
Thank you
Eric

"Dave Peterson" wrote:

Or maybe a modification of JL's formula:

=HYPERLINK("http://www.hkex.com.hk/futures/futuresdayrpt/hsio" &
text(today(),"yymmdd") & ".htm","View Todays Updates")

Eric wrote:

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

"JLatham" wrote:

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")

"Eric" wrote:

Does anyone know how to modify the web link for retrieving data from external
source? such as
http://www.hkex.com.hk/futures/futur...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

--

Dave Peterson

No, I don't have any suggestions.

But maybe someone else will.

Good luck.

Eric wrote:

Thank you for your suggestion

Hyperlink does work for cell, but it does not work through the function to
retrieve data from external source, which I intend to do. Under the tool
bars Data look for retrieve from External source function new web link
[I am using chinese office, and try to translate those function into english,
so you may not the translation accurately match in office english], if I
insert hyperlink function, then it does not work here.
Does you have any suggestion?
Thank you
Eric

"Dave Peterson" wrote:

Or maybe a modification of JL's formula:

=HYPERLINK("http://www.hkex.com.hk/futures/futuresdayrpt/hsio" &
text(today(),"yymmdd") & ".htm","View Todays Updates")

Eric wrote:

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

"JLatham" wrote:

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")

"Eric" wrote:

Does anyone know how to modify the web link for retrieving data from external
source? such as
http://www.hkex.com.hk/futures/futur...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

--

Dave Peterson


--

Dave Peterson

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" wrote:

Thank you for your suggestion
I am trying to retrieve external source through DataExternal Sourceinsert
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


"JLatham" wrote:

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.

"Eric" wrote:

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

"JLatham" wrote:

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")

"Eric" wrote:

Does anyone know how to modify the web link for retrieving data from external
source? such as
http://www.hkex.com.hk/futures/futur...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

Thank everyone for suggestion
If I would like to refer specific sheet, do you have any suggestion on how
to modify the parameter "ActiveSheet"? such as I would like to refer to HCT
spreadsheet in this case. Do you have any suggestion on modify following
code?
With ActiveSheet.QueryTables.Add(Connection:=qryConnect , Destination _
:=Range("$A$2"))
Thank you for your suggestion, this approach is simple, easy and powerful.
Eric

"JLatham" wrote:

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" wrote:

Thank you for your suggestion
I am trying to retrieve external source through DataExternal Sourceinsert
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


"JLatham" wrote:

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.

"Eric" wrote:

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

"JLatham" wrote:

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")

"Eric" wrote:

Does anyone know how to modify the web link for retrieving data from external
source? such as
http://www.hkex.com.hk/futures/futur...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

Remember that ActiveSheet refers to the sheet you currently have chosen. So
it's name is unimportant, and can be changed. If you change the code to use
a specific sheet, then you can't change that sheet's name without changing
the code also.

To be able to call this and get it to work from anywhere in the workbook,
then the code needs to look like this:

Dim qtEntry As QueryTable
Dim qryConnect As String
Dim anyRange As Range
Dim anySheet As Worksheet

Set anyRange = Sheets("HCT").Range("A2:" & _
Sheets("HCT").Range("A2").SpecialCells(xlLastCell) .Address)
On Error Resume Next ' errors if no querytable entry
anyRange.QueryTable.Delete
On Error GoTo 0
anyRange.ClearContents

qryConnect = "URL;http://www.hkex.com.hk/futures/futuresdayrpt/hsio" _
& Right(Year(Sheets("HCT").Range("A1")), 2)
If Month(Sheets("HCT").Range("A1")) < 10 Then
qryConnect = qryConnect & "0" & Month(Sheets("HCT").Range("A1"))
Else
qryConnect = qryConnect & Month(Sheets("HCT").Range("A1"))
End If
If Day(Sheets("HCT").Range("A1")) < 10 Then
qryConnect = qryConnect & "0" & Day(Sheets("HCT").Range("A1"))
Else
qryConnect = qryConnect & Day(Sheets("HCT").Range("A1"))
End If
qryConnect = qryConnect & ".htm"

Set anySheet = Sheets("HCT")
With anySheet.QueryTables.Add(Connection:=qryConnect, Destination _
:=anySheet.Range("$A$2"))

....rest of recorded macro code follows as before

"Eric" wrote:

Thank everyone for suggestion
If I would like to refer specific sheet, do you have any suggestion on how
to modify the parameter "ActiveSheet"? such as I would like to refer to HCT
spreadsheet in this case. Do you have any suggestion on modify following
code?
With ActiveSheet.QueryTables.Add(Connection:=qryConnect , Destination _
:=Range("$A$2"))
Thank you for your suggestion, this approach is simple, easy and powerful.
Eric

"JLatham" wrote:

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" wrote:

Thank you for your suggestion
I am trying to retrieve external source through DataExternal Sourceinsert
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


"JLatham" wrote:

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.

"Eric" wrote:

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

"JLatham" wrote:

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")

"Eric" wrote:

Does anyone know how to modify the web link for retrieving data from external
source? such as
http://www.hkex.com.hk/futures/futur...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

Eric,
If I've misunderstood and the date is to be in cell A1 of some sheet other
than the one where you want the query results to be shown, then just change
the sheet name accordingly above in the section where the qryConnect string
is built up.

"JLatham" wrote:

Remember that ActiveSheet refers to the sheet you currently have chosen. So
it's name is unimportant, and can be changed. If you change the code to use
a specific sheet, then you can't change that sheet's name without changing
the code also.

To be able to call this and get it to work from anywhere in the workbook,
then the code needs to look like this:

Dim qtEntry As QueryTable
Dim qryConnect As String
Dim anyRange As Range
Dim anySheet As Worksheet

Set anyRange = Sheets("HCT").Range("A2:" & _
Sheets("HCT").Range("A2").SpecialCells(xlLastCell) .Address)
On Error Resume Next ' errors if no querytable entry
anyRange.QueryTable.Delete
On Error GoTo 0
anyRange.ClearContents

qryConnect = "URL;http://www.hkex.com.hk/futures/futuresdayrpt/hsio" _
& Right(Year(Sheets("HCT").Range("A1")), 2)
If Month(Sheets("HCT").Range("A1")) < 10 Then
qryConnect = qryConnect & "0" & Month(Sheets("HCT").Range("A1"))
Else
qryConnect = qryConnect & Month(Sheets("HCT").Range("A1"))
End If
If Day(Sheets("HCT").Range("A1")) < 10 Then
qryConnect = qryConnect & "0" & Day(Sheets("HCT").Range("A1"))
Else
qryConnect = qryConnect & Day(Sheets("HCT").Range("A1"))
End If
qryConnect = qryConnect & ".htm"

Set anySheet = Sheets("HCT")
With anySheet.QueryTables.Add(Connection:=qryConnect, Destination _
:=anySheet.Range("$A$2"))

...rest of recorded macro code follows as before

"Eric" wrote:

Thank everyone for suggestion
If I would like to refer specific sheet, do you have any suggestion on how
to modify the parameter "ActiveSheet"? such as I would like to refer to HCT
spreadsheet in this case. Do you have any suggestion on modify following
code?
With ActiveSheet.QueryTables.Add(Connection:=qryConnect , Destination _
:=Range("$A$2"))
Thank you for your suggestion, this approach is simple, easy and powerful.
Eric

"JLatham" wrote:

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" wrote:

Thank you for your suggestion
I am trying to retrieve external source through DataExternal Sourceinsert
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


"JLatham" wrote:

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.

"Eric" wrote:

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

"JLatham" wrote:

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")

"Eric" wrote:

Does anyone know how to modify the web link for retrieving data from external
source? such as
http://www.hkex.com.hk/futures/futur...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

Thank you very much, I solve this problem
Eric :

My question is similar to the above.

I want to have a link to a cell in a new file, the new file is in the same
folder as the result workbook. The filename of the new files is the number in
the A-column followed by -BOM.xls

The formula that I'm trying to use is like bellow.

="'\\se-ka-sr028\roxtec-se\Global
Technology\TPO\DO-BOM\["&A2&"-BOM.xls]Sheet1'!$G$5"

Best regards Johan

My question is similar

I want to have a link to a cell in a different workbook. The cell is always
the same but it's different files, the title is the information in the A
column followed by -BOM.xls.

All files are placed in the same folder.

My formula is like bellow
="'\\se-ka-sr028\roxtec-se\Global
Technology\TPO\DO-BOM\["&A2&"-BOM.xls]Sheet1'!$G$5"
but I cant get it to work.

Best regards Johan

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

Johan Myrén wrote:

My question is similar to the above.

I want to have a link to a cell in a new file, the new file is in the same
folder as the result workbook. The filename of the new files is the number in
the A-column followed by -BOM.xls

The formula that I'm trying to use is like bellow.

="'\\se-ka-sr028\roxtec-se\Global
Technology\TPO\DO-BOM\["&A2&"-BOM.xls]Sheet1'!$G$5"

Best regards Johan

--

Dave Peterson