xl2003, winXP

I have a range of 10 URLs. A10-A19 Each day I need to update them to the
current date. The URL looks like:-

URL;http://www.tabonline.com.au/2006/02/23/NR05.html


So tomorrow I will want to be able to push a button and /2006/02/23 becomes
/2006/02/24 in all 10.
I need to do this programatically as it will be part of a series of
operations.
Any help would be greatly appreciated.
Ta,
Martin

Try the macro below. (I broken one of the very long statement into
multi-lines.)

Sub change_url()
Dim cell As Object
Dim tmp$, tmp2$
With ActiveSheet.Range("A10:A19")
For Each cell In .Cells
tmp = cell.Value
If InStr(tmp, "http") < 0 Then
If InStr(12, tmp, "/") < 0 Then
cell.Value = Left(tmp, InStr(12, tmp, "/")) & _
Format(Date, "yyyy") & "/" & Format(Date, "mm") & _
"/" & Format(Date, "dd") & "/" & _
Right(tmp, Len(tmp) - Len(Left(tmp, InStr(12, tmp,
"/"))) - 11)
End If
End If
Next
End With
End Sub


Regards,
Edwin Tam

http://www.vonixx.com


"Martin Wheeler" wrote:

xl2003, winXP

I have a range of 10 URLs. A10-A19 Each day I need to update them to the
current date. The URL looks like:-

URL;http://www.tabonline.com.au/2006/02/23/NR05.html


So tomorrow I will want to be able to push a button and /2006/02/23 becomes
/2006/02/24 in all 10.
I need to do this programatically as it will be part of a series of
operations.
Any help would be greatly appreciated.
Ta,
Martin

Sub ChangeURL()

Dim iRow As Integer
Dim iDate As Integer

Dim dDate As Date

Dim sTxt() As String
Dim sCol As String

sCol = "E"
dDate = DateValue(Mid(Range("e1"), 33, 10)) + 1
For iRow = 1 To 10 Step 1
ReDim sTxt(1)
sTxt(0) = Cells(iRow, sCol)
For iDate = 16 To Len(sTxt(0)) Step 1
If IsDate(Mid(sTxt(0), iDate, 10)) Then
dDate = DateValue(Mid(sTxt(0), iDate, 10)) + 1
sTxt(1) = Left(sTxt(0), iDate - 1) _
& Format(dDate, "yyyy/mm/dd") _
& Mid(sTxt(0), iDate + 10)
Exit For
End If
Next iDate

Cells(iRow, sCol).Value = sTxt(1)
Next


--
mudraker
------------------------------------------------------------------------
mudraker's Profile: http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=515580

This isn't VBA, but maybe you could just use =hyperlink().

Like:

=HYPERLINK("http://www.tabonline.com.au/"
&TEXT(TODAY(),"yyyy/mm/dd")&"/NR05.html", "Click Me")

(all one cell)



Martin Wheeler wrote:

xl2003, winXP

I have a range of 10 URLs. A10-A19 Each day I need to update them to the
current date. The URL looks like:-

URL;http://www.tabonline.com.au/2006/02/23/NR05.html

So tomorrow I will want to be able to push a button and /2006/02/23 becomes
/2006/02/24 in all 10.
I need to do this programatically as it will be part of a series of
operations.
Any help would be greatly appreciated.
Ta,
Martin

--

Dave Peterson

Martin,
Perhaps...

="URL;http://www.tabonline.com.au/"&TEXT(TODAY(),"yyyy/mm/dd")&"/NR05.html"

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Martin Wheeler" wrote in message...
xl2003, winXP
I have a range of 10 URLs. A10-A19 Each day I need to update them to the
current date. The URL looks like:-

URL;http://www.tabonline.com.au/2006/02/23/NR05.html

So tomorrow I will want to be able to push a button and /2006/02/23 becomes
/2006/02/24 in all 10.
I need to do this programatically as it will be part of a series of
operations.
Any help would be greatly appreciated.
Ta,
Martin

I tried to use today() in spread sheet it comes as serial no. of date in vba
it comes as 2/23/2006

so I formatted a series of cells e.g. A6 to a15 as TEXT
in A6 as 2006/2/23
I filled serially down to A15(it is easy to do this in excel 2002-you click
right bottom of A6 and drag it down and click smart tag and click fill
series

now in B6 you type
=$A$1&A6&$A$2
take care of dollar signs
now copy B6 down to B15
will this be useful to you.

may be more elegant solutions are there.
..

"Martin Wheeler" wrote in message
...
xl2003, winXP

I have a range of 10 URLs. A10-A19 Each day I need to update them to the
current date. The URL looks like:-

URL;http://www.tabonline.com.au/2006/02/23/NR05.html


So tomorrow I will want to be able to push a button and /2006/02/23
becomes /2006/02/24 in all 10.
I need to do this programatically as it will be part of a series of
operations.
Any help would be greatly appreciated.
Ta,
Martin

Hi Dave,
I just tried this and all I got was the main page.
It did not add the extra bits.
Ta,
Martin


"Dave Peterson" wrote in message
...
This isn't VBA, but maybe you could just use =hyperlink().

Like:

=HYPERLINK("http://www.tabonline.com.au/"
&TEXT(TODAY(),"yyyy/mm/dd")&"/NR05.html", "Click Me")

(all one cell)



Martin Wheeler wrote:

xl2003, winXP

I have a range of 10 URLs. A10-A19 Each day I need to update them to the
current date. The URL looks like:-

URL;http://www.tabonline.com.au/2006/02/23/NR05.html

So tomorrow I will want to be able to push a button and /2006/02/23
becomes
/2006/02/24 in all 10.
I need to do this programatically as it will be part of a series of
operations.
Any help would be greatly appreciated.
Ta,
Martin

--

Dave Peterson

Hi Jim,
It works! I'm impressed. I also need to change the NR05 each day but will do
that later.
Thanks for your help
Ta,
Martin

"Jim Cone" wrote in message
...
Martin,
Perhaps...

="URL;http://www.tabonline.com.au/"&TEXT(TODAY(),"yyyy/mm/dd")&"/NR05.html"

Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Martin Wheeler" wrote in message...
xl2003, winXP
I have a range of 10 URLs. A10-A19 Each day I need to update them to the
current date. The URL looks like:-

URL;http://www.tabonline.com.au/2006/02/23/NR05.html

So tomorrow I will want to be able to push a button and /2006/02/23
becomes
/2006/02/24 in all 10.
I need to do this programatically as it will be part of a series of
operations.
Any help would be greatly appreciated.
Ta,
Martin

Hi Edwin,
Thanks for the code. I cannot test it right now. Only 1 computer and it is
doing critical stuff.
Will try in a few hours.
Ta,
Martin

"Edwin Tam" wrote in message
...
Try the macro below. (I broken one of the very long statement into
multi-lines.)

Sub change_url()
Dim cell As Object
Dim tmp$, tmp2$
With ActiveSheet.Range("A10:A19")
For Each cell In .Cells
tmp = cell.Value
If InStr(tmp, "http") < 0 Then
If InStr(12, tmp, "/") < 0 Then
cell.Value = Left(tmp, InStr(12, tmp, "/")) & _
Format(Date, "yyyy") & "/" & Format(Date, "mm") & _
"/" & Format(Date, "dd") & "/" & _
Right(tmp, Len(tmp) - Len(Left(tmp, InStr(12, tmp,
"/"))) - 11)
End If
End If
Next
End With
End Sub


Regards,
Edwin Tam

http://www.vonixx.com


"Martin Wheeler" wrote:

xl2003, winXP

I have a range of 10 URLs. A10-A19 Each day I need to update them to the
current date. The URL looks like:-

URL;http://www.tabonline.com.au/2006/02/23/NR05.html


So tomorrow I will want to be able to push a button and /2006/02/23
becomes
/2006/02/24 in all 10.
I need to do this programatically as it will be part of a series of
operations.
Any help would be greatly appreciated.
Ta,
Martin

Hi Mud,
Thanks for the code. Cannot test it right now as my only computer is doing
critical stuff. Will do so in a few hours
Ta,
Martin


"mudraker" wrote in
message ...

Sub ChangeURL()

Dim iRow As Integer
Dim iDate As Integer

Dim dDate As Date

Dim sTxt() As String
Dim sCol As String

sCol = "E"
dDate = DateValue(Mid(Range("e1"), 33, 10)) + 1
For iRow = 1 To 10 Step 1
ReDim sTxt(1)
sTxt(0) = Cells(iRow, sCol)
For iDate = 16 To Len(sTxt(0)) Step 1
If IsDate(Mid(sTxt(0), iDate, 10)) Then
dDate = DateValue(Mid(sTxt(0), iDate, 10)) + 1
sTxt(1) = Left(sTxt(0), iDate - 1) _
& Format(dDate, "yyyy/mm/dd") _
& Mid(sTxt(0), iDate + 10)
Exit For
End If
Next iDate

Cells(iRow, sCol).Value = sTxt(1)
Next


--
mudraker
------------------------------------------------------------------------
mudraker's Profile:
http://www.excelforum.com/member.php...fo&userid=2473
View this thread: http://www.excelforum.com/showthread...hreadid=515580

Hi R,

Jim's function works very well so I will use it for now But might go to
Edwin's or Mudraker's code when I develop it further

Thanks for your help.
Ta,
Martin



"R..VENKATARAMAN" wrote in message
...
I tried to use today() in spread sheet it comes as serial no. of date in
vba it comes as 2/23/2006

so I formatted a series of cells e.g. A6 to a15 as TEXT
in A6 as 2006/2/23
I filled serially down to A15(it is easy to do this in excel 2002-you
click right bottom of A6 and drag it down and click smart tag and click
fill series

now in B6 you type
=$A$1&A6&$A$2
take care of dollar signs
now copy B6 down to B15
will this be useful to you.

may be more elegant solutions are there.
.

"Martin Wheeler" wrote in message
...
xl2003, winXP

I have a range of 10 URLs. A10-A19 Each day I need to update them to the
current date. The URL looks like:-

URL;http://www.tabonline.com.au/2006/02/23/NR05.html


So tomorrow I will want to be able to push a button and /2006/02/23
becomes /2006/02/24 in all 10.
I need to do this programatically as it will be part of a series of
operations.
Any help would be greatly appreciated.
Ta,
Martin

Martin,

An additional note. The code I posted will work even when the domain name
changed at a later date, or even when you go for https instead of http later.
Because it actually "hunt" for the position of the end of the domain name and
pluf the date into it and then plug the rest of the URL.

Edwin


"Martin Wheeler" wrote:

Hi R,

Jim's function works very well so I will use it for now But might go to
Edwin's or Mudraker's code when I develop it further

Thanks for your help.
Ta,
Martin



"R..VENKATARAMAN" wrote in message
...
I tried to use today() in spread sheet it comes as serial no. of date in
vba it comes as 2/23/2006

so I formatted a series of cells e.g. A6 to a15 as TEXT
in A6 as 2006/2/23
I filled serially down to A15(it is easy to do this in excel 2002-you
click right bottom of A6 and drag it down and click smart tag and click
fill series

now in B6 you type
=$A$1&A6&$A$2
take care of dollar signs
now copy B6 down to B15
will this be useful to you.

may be more elegant solutions are there.
.

"Martin Wheeler" wrote in message
...
xl2003, winXP

I have a range of 10 URLs. A10-A19 Each day I need to update them to the
current date. The URL looks like:-

URL;http://www.tabonline.com.au/2006/02/23/NR05.html


So tomorrow I will want to be able to push a button and /2006/02/23
becomes /2006/02/24 in all 10.
I need to do this programatically as it will be part of a series of
operations.
Any help would be greatly appreciated.
Ta,
Martin

What are the extra bits?

When I put that formula in a worksheet cell and clicked on it, I went to:

http://www.tabonline.com.au/2006/02/23/NR05.html

Which seems to match what you asked for.

Martin Wheeler wrote:

Hi Dave,
I just tried this and all I got was the main page.
It did not add the extra bits.
Ta,
Martin

"Dave Peterson" wrote in message
...
This isn't VBA, but maybe you could just use =hyperlink().

Like:

=HYPERLINK("http://www.tabonline.com.au/"
&TEXT(TODAY(),"yyyy/mm/dd")&"/NR05.html", "Click Me")

(all one cell)



Martin Wheeler wrote:

xl2003, winXP

I have a range of 10 URLs. A10-A19 Each day I need to update them to the
current date. The URL looks like:-

URL;http://www.tabonline.com.au/2006/02/23/NR05.html

So tomorrow I will want to be able to push a button and /2006/02/23
becomes
/2006/02/24 in all 10.
I need to do this programatically as it will be part of a series of
operations.
Any help would be greatly appreciated.
Ta,
Martin

--

Dave Peterson

--

Dave Peterson

Hi Dave,

I think we have "our wires" crossed. You are right it does bring up the web
page in ie but I get the home page not the race NR05.

But what I actually want is simply the url in the range with no hyperlink.
I use this to refresh the web queries in each of 10 worksheets, as per Jim
Cones.

Sorry about the confusion.
Ta,
Martin

"Dave Peterson" wrote in message
...
What are the extra bits?

When I put that formula in a worksheet cell and clicked on it, I went to:

http://www.tabonline.com.au/2006/02/23/NR05.html

Which seems to match what you asked for.

Martin Wheeler wrote:

Hi Dave,
I just tried this and all I got was the main page.
It did not add the extra bits.
Ta,
Martin

"Dave Peterson" wrote in message
...
This isn't VBA, but maybe you could just use =hyperlink().

Like:

=HYPERLINK("http://www.tabonline.com.au/"
&TEXT(TODAY(),"yyyy/mm/dd")&"/NR05.html", "Click Me")

(all one cell)



Martin Wheeler wrote:

xl2003, winXP

I have a range of 10 URLs. A10-A19 Each day I need to update them to
the
current date. The URL looks like:-

URL;http://www.tabonline.com.au/2006/02/23/NR05.html

So tomorrow I will want to be able to push a button and /2006/02/23
becomes
/2006/02/24 in all 10.
I need to do this programatically as it will be part of a series of
operations.
Any help would be greatly appreciated.
Ta,
Martin

--

Dave Peterson

--

Dave Peterson

I did get the correct page (that formula was one cell, right).

But, it still wasn't what you really wanted anyway.

Glad that Jim helped you get what you want.

Martin Wheeler wrote:

Hi Dave,

I think we have "our wires" crossed. You are right it does bring up the web
page in ie but I get the home page not the race NR05.

But what I actually want is simply the url in the range with no hyperlink.
I use this to refresh the web queries in each of 10 worksheets, as per Jim
Cones.

Sorry about the confusion.
Ta,
Martin

"Dave Peterson" wrote in message
...
What are the extra bits?

When I put that formula in a worksheet cell and clicked on it, I went to:

http://www.tabonline.com.au/2006/02/23/NR05.html

Which seems to match what you asked for.

Martin Wheeler wrote:

Hi Dave,
I just tried this and all I got was the main page.
It did not add the extra bits.
Ta,
Martin

"Dave Peterson" wrote in message
...
This isn't VBA, but maybe you could just use =hyperlink().

Like:

=HYPERLINK("http://www.tabonline.com.au/"
&TEXT(TODAY(),"yyyy/mm/dd")&"/NR05.html", "Click Me")

(all one cell)



Martin Wheeler wrote:

xl2003, winXP

I have a range of 10 URLs. A10-A19 Each day I need to update them to
the
current date. The URL looks like:-

URL;http://www.tabonline.com.au/2006/02/23/NR05.html

So tomorrow I will want to be able to push a button and /2006/02/23
becomes
/2006/02/24 in all 10.
I need to do this programatically as it will be part of a series of
operations.
Any help would be greatly appreciated.
Ta,
Martin

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson