Change date in URL

[Martin Wheeler]

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

[Edwin Tam]

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
support@vonixx.com
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
>
>
>

[mudraker]

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

[Dave Peterson]

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

[Jim Cone]

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" <madw@ake.quik.co.nz> 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

[R..VENKATARAMAN]

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" <madw@ake.quik.co.nz> wrote in message
news:u7foazBOGHA.2828@TK2MSFTNGP12.phx.gbl...
> 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 Wheeler]

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" <petersod@verizonXSPAM.net> wrote in message
news:43FD2685.82752D2D@verizonXSPAM.net...
> 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 Wheeler]

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" <jim.coneXXX@rcn.comXXX> wrote in message
news:O4KmVdCOGHA.720@TK2MSFTNGP14.phx.gbl...
> 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" <madw@ake.quik.co.nz> 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 Wheeler]

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" <EdwinTam@discussions.microsoft.com> wrote in message
news:4530428A-32D6-4109-BC6C-F324F0304198@microsoft.com...
> 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
> support@vonixx.com
> 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
>>
>>
>>

[Martin Wheeler]

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" <mudraker.23nwim_1140664204.3899@excelforum-nospam.com> wrote in
message news:mudraker.23nwim_1140664204.3899@excelforum-nospam.com...
>
> 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
>

[Martin Wheeler]

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" <venkat1926@touchtelindia.net> wrote in message
news:uDx4dhCOGHA.3988@TK2MSFTNGP09.phx.gbl...
>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" <madw@ake.quik.co.nz> wrote in message
> news:u7foazBOGHA.2828@TK2MSFTNGP12.phx.gbl...
>> 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
>>
>
>

[Edwin Tam]

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" <venkat1926@touchtelindia.net> wrote in message
> news:uDx4dhCOGHA.3988@TK2MSFTNGP09.phx.gbl...
> >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" <madw@ake.quik.co.nz> wrote in message
> > news:u7foazBOGHA.2828@TK2MSFTNGP12.phx.gbl...
> >> 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]

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" <petersod@verizonXSPAM.net> wrote in message
> news:43FD2685.82752D2D@verizonXSPAM.net...
> > 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

[Martin Wheeler]

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" <petersod@verizonXSPAM.net> wrote in message
news:43FDCEFE.71780D8C@verizonXSPAM.net...
> 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" <petersod@verizonXSPAM.net> wrote in message
>> news:43FD2685.82752D2D@verizonXSPAM.net...
>> > 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]

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" <petersod@verizonXSPAM.net> wrote in message
> news:43FDCEFE.71780D8C@verizonXSPAM.net...
> > 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" <petersod@verizonXSPAM.net> wrote in message
> >> news:43FD2685.82752D2D@verizonXSPAM.net...
> >> > 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