Help with a Macro - Excel 2003

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bob Smith - 19 Jun 2006 17:26 GMT

Hi folks,
I don't know much about coding macros and was hoping someone might provide
some advice. I have a workbook, with 50 or so worksheets querying 50
different URLs from one company. The company has changed their URLs from
http://www.ntn.com/hospitality_tech/players_corner/site.asp?id= to
http://players.buzztime.com/bt_site.php?s= .(Following each = is a different
number, like 1234 or 15923 ect.)

Is there a way to change those URLs on a global basis, and maintain the same
number following the = sign?

Thanks for any help you may be able to provide.

Regards, Bob

Reply to this Message

stacrafty@yahoo.com - 19 Jun 2006 17:34 GMT

An easy way would be to highlight all your data and hit ctrl H to bring
up find and replace, place the first bit
(http://www.ntn.com/hospitality_tech/players_corner/site.asp?id=) in
find and then put http://players.buzztime.com/bt_site.php?s=, in
replace. This should update it.
Alternatively you could use a SS formula to find the "=" and replace
everything to the left of that with your new text
(http://players.buzztime.com/bt_site.php?s=)

Both would require a little bit of manual work still, are you looking
for an entirely automated solution?

> Hi folks,
> I don't know much about coding macros and was hoping someone might provide
[quoted text clipped - 10 lines]
>
> Regards, Bob

Reply to this Message

Bob Smith - 19 Jun 2006 17:46 GMT

> An easy way would be to highlight all your data and hit ctrl H to bring
> up find and replace, place the first bit
[quoted text clipped - 7 lines]
> Both would require a little bit of manual work still, are you looking
> for an entirely automated solution?

I tried your first solution, and it finds no text to replace. These are
queries which are buried in cell A1 in each sheet. As for your SS formula
solution, I'm sorry but I don't know what you are talking about.

And yes, I'm looking for an automated solution :).

Regards,

Bob

Reply to this Message

Die_Another_Day - 19 Jun 2006 18:29 GMT

As far as having a global address you can use the "Hyperlink" function
in Excel to reference the main page which allows for future changes. it
does not however help you know.

For example:
A1 =
http://groups.google.com/group/microsoft.public.excel.worksheet.functions/browse
_thread/thread/807c0d5c030d9755/

B1 = d1b59fe11c0f62d1#d1b59fe11c0f62d1

C1 = =HYPERLINK(A1&B1,"Link to here")

HTH

Die_Another_Day

Reply to this Message