Change date in URL

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

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