edit hyperlink code cont from 12/8/04

I tried running this without success. Can anybody tell me why?

Sub Fix192Hyperlinks()
    '
    Dim OldStr As String, NewStr As String
    OldStr = "c:\My Templates\Profile Database\"
    NewStr = "\\Pinoak\Data\Mfr\Grinding Room\"
    Dim hyp As Hyperlink
    For Each hyp In ActiveSheet.Hyperlinks
         hyp.Address = Replace(hyp.Address, OldStr, NewStr)
    Next hyp
End Sub

Thank you
> 
> 
>> > "Ron de Bruin" wrote:
> >
> >> I copy this from David McRitchie his site
> >> http://www.mvps.org/dmcritchie/excel/buildtoc.htm#hyp
> >>
> >> Sub Fix192Hyperlinks()
> >>     Dim OldStr As String, NewStr As String
> >>     OldStr = "http://192.168.15.5/"
> >>     NewStr = "http://hank.home.on.ca/"
> >>     Dim hyp As Hyperlink
> >>     For Each hyp In ActiveSheet.Hyperlinks
> >>          hyp.Address = Replace(hyp.Address, OldStr, NewStr)
> >>     Next hyp
> >> End Sub
> >>
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >> >I am moving an Excel spreadsheet with about 3000 hyperlinks from C: drive to
> >> > U: drive (servor) and need to revise the hyperlinks accordingly. While I'm at
> >> > it, I can take an unneccessary folder   (....../folder/....) out of the
> >> > address. I tried writing VBA to do this without success. Suggestions?  Thank
> >> > you in advance. Doug
> 
 

0
Doug4515 (774)
12/17/2004 2:03:03 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
374 Views

Similar Articles

[PageSpeed] 46

Do you use Excel 97 Doug ?

Try
hyp.Address = Application.WorksheetFunction.Substitute(hyp.Address, OldStr, NewStr)

-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"doug" <doug@discussions.microsoft.com> wrote in message news:9B856CAE-156D-47DD-BF42-B3D4208B9DF6@microsoft.com...
>I tried running this without success. Can anybody tell me why?
>
> Sub Fix192Hyperlinks()
>    '
>    Dim OldStr As String, NewStr As String
>    OldStr = "c:\My Templates\Profile Database\"
>    NewStr = "\\Pinoak\Data\Mfr\Grinding Room\"
>    Dim hyp As Hyperlink
>    For Each hyp In ActiveSheet.Hyperlinks
>         hyp.Address = Replace(hyp.Address, OldStr, NewStr)
>    Next hyp
> End Sub
>
> Thank you
>>
>>
>>> > "Ron de Bruin" wrote:
>> >
>> >> I copy this from David McRitchie his site
>> >> http://www.mvps.org/dmcritchie/excel/buildtoc.htm#hyp
>> >>
>> >> Sub Fix192Hyperlinks()
>> >>     Dim OldStr As String, NewStr As String
>> >>     OldStr = "http://192.168.15.5/"
>> >>     NewStr = "http://hank.home.on.ca/"
>> >>     Dim hyp As Hyperlink
>> >>     For Each hyp In ActiveSheet.Hyperlinks
>> >>          hyp.Address = Replace(hyp.Address, OldStr, NewStr)
>> >>     Next hyp
>> >> End Sub
>> >>
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl
>> >>
>> >> >I am moving an Excel spreadsheet with about 3000 hyperlinks from C: drive to
>> >> > U: drive (servor) and need to revise the hyperlinks accordingly. While I'm at
>> >> > it, I can take an unneccessary folder   (....../folder/....) out of the
>> >> > address. I tried writing VBA to do this without success. Suggestions?  Thank
>> >> > you in advance. Doug
>>
>
> 


0
rondebruin (3790)
12/17/2004 2:54:21 PM
excel 2003

"Ron de Bruin" wrote:

> Do you use Excel 97 Doug ?
> 
> Try
> hyp.Address = Application.WorksheetFunction.Substitute(hyp.Address, OldStr, NewStr)
> 
> -- 
> Regards Ron de Bruin
> http://www.rondebruin.nl
> 
> 
> "doug" <doug@discussions.microsoft.com> wrote in message news:9B856CAE-156D-47DD-BF42-B3D4208B9DF6@microsoft.com...
> >I tried running this without success. Can anybody tell me why?
> >
> > Sub Fix192Hyperlinks()
> >    '
> >    Dim OldStr As String, NewStr As String
> >    OldStr = "c:\My Templates\Profile Database\"
> >    NewStr = "\\Pinoak\Data\Mfr\Grinding Room\"
> >    Dim hyp As Hyperlink
> >    For Each hyp In ActiveSheet.Hyperlinks
> >         hyp.Address = Replace(hyp.Address, OldStr, NewStr)
> >    Next hyp
> > End Sub
> >
> > Thank you
> >>
> >>
> >>> > "Ron de Bruin" wrote:
> >> >
> >> >> I copy this from David McRitchie his site
> >> >> http://www.mvps.org/dmcritchie/excel/buildtoc.htm#hyp
> >> >>
> >> >> Sub Fix192Hyperlinks()
> >> >>     Dim OldStr As String, NewStr As String
> >> >>     OldStr = "http://192.168.15.5/"
> >> >>     NewStr = "http://hank.home.on.ca/"
> >> >>     Dim hyp As Hyperlink
> >> >>     For Each hyp In ActiveSheet.Hyperlinks
> >> >>          hyp.Address = Replace(hyp.Address, OldStr, NewStr)
> >> >>     Next hyp
> >> >> End Sub
> >> >>
> >> >> Regards Ron de Bruin
> >> >> http://www.rondebruin.nl
> >> >>
> >> >> >I am moving an Excel spreadsheet with about 3000 hyperlinks from C: drive to
> >> >> > U: drive (servor) and need to revise the hyperlinks accordingly. While I'm at
> >> >> > it, I can take an unneccessary folder   (....../folder/....) out of the
> >> >> > address. I tried writing VBA to do this without success. Suggestions?  Thank
> >> >> > you in advance. Doug
> >>
> >
> > 
> 
> 
> 
0
Doug4515 (774)
12/17/2004 3:17:06 PM
Hi Doug

Send me a workbook with a few links (private)and i take a look at it


-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"doug" <doug@discussions.microsoft.com> wrote in message news:3DA22DBE-62A8-4445-8FF5-67189034D198@microsoft.com...
> excel 2003
>
> "Ron de Bruin" wrote:
>
>> Do you use Excel 97 Doug ?
>>
>> Try
>> hyp.Address = Application.WorksheetFunction.Substitute(hyp.Address, OldStr, NewStr)
>>
>> -- 
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "doug" <doug@discussions.microsoft.com> wrote in message news:9B856CAE-156D-47DD-BF42-B3D4208B9DF6@microsoft.com...
>> >I tried running this without success. Can anybody tell me why?
>> >
>> > Sub Fix192Hyperlinks()
>> >    '
>> >    Dim OldStr As String, NewStr As String
>> >    OldStr = "c:\My Templates\Profile Database\"
>> >    NewStr = "\\Pinoak\Data\Mfr\Grinding Room\"
>> >    Dim hyp As Hyperlink
>> >    For Each hyp In ActiveSheet.Hyperlinks
>> >         hyp.Address = Replace(hyp.Address, OldStr, NewStr)
>> >    Next hyp
>> > End Sub
>> >
>> > Thank you
>> >>
>> >>
>> >>> > "Ron de Bruin" wrote:
>> >> >
>> >> >> I copy this from David McRitchie his site
>> >> >> http://www.mvps.org/dmcritchie/excel/buildtoc.htm#hyp
>> >> >>
>> >> >> Sub Fix192Hyperlinks()
>> >> >>     Dim OldStr As String, NewStr As String
>> >> >>     OldStr = "http://192.168.15.5/"
>> >> >>     NewStr = "http://hank.home.on.ca/"
>> >> >>     Dim hyp As Hyperlink
>> >> >>     For Each hyp In ActiveSheet.Hyperlinks
>> >> >>          hyp.Address = Replace(hyp.Address, OldStr, NewStr)
>> >> >>     Next hyp
>> >> >> End Sub
>> >> >>
>> >> >> Regards Ron de Bruin
>> >> >> http://www.rondebruin.nl
>> >> >>
>> >> >> >I am moving an Excel spreadsheet with about 3000 hyperlinks from C: drive to
>> >> >> > U: drive (servor) and need to revise the hyperlinks accordingly. While I'm at
>> >> >> > it, I can take an unneccessary folder   (....../folder/....) out of the
>> >> >> > address. I tried writing VBA to do this without success. Suggestions?  Thank
>> >> >> > you in advance. Doug
>> >>
>> >
>> >
>>
>>
>> 


0
rondebruin (3790)
12/17/2004 3:25:13 PM
Are you sure your activesheet had hyperlinks that matched your OldStr?

Maybe replacing this:
hyp.Address = Replace(hyp.Address, OldStr, NewStr)
with:
hyp.Address = Replace(hyp.Address, OldStr, NewStr, 1, -1, vbTextCompare)

will help.

If you don't specify it (like vbTextCompare), replace is case sensitive.

Check VBA's help for Replace for more info.




doug wrote:
> 
> I tried running this without success. Can anybody tell me why?
> 
> Sub Fix192Hyperlinks()
>     '
>     Dim OldStr As String, NewStr As String
>     OldStr = "c:\My Templates\Profile Database\"
>     NewStr = "\\Pinoak\Data\Mfr\Grinding Room\"
>     Dim hyp As Hyperlink
>     For Each hyp In ActiveSheet.Hyperlinks
>          hyp.Address = Replace(hyp.Address, OldStr, NewStr)
>     Next hyp
> End Sub
> 
> Thank you
> >
> >
> >> > "Ron de Bruin" wrote:
> > >
> > >> I copy this from David McRitchie his site
> > >> http://www.mvps.org/dmcritchie/excel/buildtoc.htm#hyp
> > >>
> > >> Sub Fix192Hyperlinks()
> > >>     Dim OldStr As String, NewStr As String
> > >>     OldStr = "http://192.168.15.5/"
> > >>     NewStr = "http://hank.home.on.ca/"
> > >>     Dim hyp As Hyperlink
> > >>     For Each hyp In ActiveSheet.Hyperlinks
> > >>          hyp.Address = Replace(hyp.Address, OldStr, NewStr)
> > >>     Next hyp
> > >> End Sub
> > >>
> > >> Regards Ron de Bruin
> > >> http://www.rondebruin.nl
> > >>
> > >> >I am moving an Excel spreadsheet with about 3000 hyperlinks from C: drive to
> > >> > U: drive (servor) and need to revise the hyperlinks accordingly. While I'm at
> > >> > it, I can take an unneccessary folder   (....../folder/....) out of the
> > >> > address. I tried writing VBA to do this without success. Suggestions?  Thank
> > >> > you in advance. Doug
> >
> 

-- 

Dave Peterson
0
ec357201 (5290)
12/17/2004 11:51:41 PM
Reply:

Similar Artilces:

Can i edit layout of the "Navigation Pane"
I would like to specifically remove the "favourites folder" section as well as the "Buttons" by the bottom and just simply have the folders structure... Can this be done? Cheers, ...

Slightly OT: Anybody programmatically burn CDs/DVDs from Win32 code...?
Does XP offer the ability to do so? Are there 3rd party controls/APIs/tools for doing this? I'd like to add the ability to burn data to disc to our product and I was wondering what people on this list have done successfully. Thanks, WTH XP has some support for this (ICDBurn interface). XP has a directory (staging area) for each user where you place the files to be burned. The default path to the staging area is "%userprofile%\Local Settings\Application Data\Microsoft\CD Burning". You can get the path to the staging area by calling SHGetFolderLocation ...

Exchange backup #12
Hello At the mo, i am using MS backup to backup my exchange server, each night. But the problems i have i need to remember to goto the backup to rename it, so the next night backup does not overwrite it. Any idea how i can stop msbackup doing this, or a dos script that i can use to rename the file and add the date to the file? Thanks Andy I create two separate scheduled backup jobs that use two separate .BKF files. One runs on M-W-F, and the other runs on T-R-S. You could write a script to run at some point after the backup that renames the .BKF file. How many days worth of bac...

Charting Beginning Revenue and Changes Over a 12 year period
Hi I would like to create a chart that shows revenue for 1996 and cumulative year over year changes in revenue that when added to 1996 (beginning balance) will equal 2008. Does a particular chart come to mind? Thanks in advance. Al Charbonneau Waterfall? http://peltiertech.com/Excel/Charts/ChartIndex.html#Waterfall - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ On 2/26/2010 10:59 AM, Al Charbonneau wrote: > Hi > > I would like to create a chart that shows revenue for 1996 and cumulative > year over year chan...

Change Needed
I have a neat VB code that I use in Excel. This code imports data from a text file into columns in an excel spreadsheet. The excel file has two worksheets, one is to input the .txt filename with a button for the VB code to do it's magic and convert everything I have in the text file, which usually has the same layout (in each txt file) in terms of what data I want columnized in excel. The code goes as follows.... Sub ConvertFile() Dim LCStart As Long Dim IQStart As Long Dim Lat1Start As Long Dim Lon1Start As Long Dim Lat2Start As Long Dim Lon2Start As Long Dim NbmesStart As Long Dim...

NDR Status Code 6.4.1
Hi...I'm new to this group, but we are running up against a problem with a front-end exchange server that has just been implemented. Since this server has been installed, the error log is filling up with NDR 6.4.1 error messages and those select email are NOT getting delivered properly even though other email comes through fine. Best we can tell in house, this error code is indicating that we might not have a setting correct on the front-end exchange server to allow 8-bit MIME types to get through. Are there any suggestions on what might be occurring. Are there default settings ...

How can you set a column of cells to be hyperlinks?
Hi, I'm trying to set a column of cell values to be hyperlinks so that when clicked on a web page will open showing the information for the paricular order selected from the spreadsheet. What I have done so far is to insert a column next to the column that has the data in it and use the HYPERLINK function with reference to the cell to the left. That works just fine but now I want to get rid of the column to the left and leave just the column with the hyperlnks in it. If I remove the column then the cell reference is bad because the cells have been removed. What can I do to get t...

The e-mail address in contacts is a hyperlink but it does not work
The e-mail address field in CRM is a hyperlink but it does not fire up outlook and put the e-mail address in. Is this our installation or does it not work for anybody? If anyone has got it working, do you know how? Thanks, Clare Clare, CRM 3.0 did not build in a double-click function to perform that action. What I have seen done to get around the issue is to add a JavaScript function on the contact (or account) onLoad event that adds this behavior. Here is a sample JavaScript to perform this action: var EmailHyperlink = function() { var emailAddress = crmForm.all.emailaddress1....

Business Contact Manager #8
When I try to add a new contact into either Accounts in Business Contact Manager or Business Contacts in Business Manager i receive a message, The messaging interface has returned an unknown error,I problem persists restart Outlook, this I have done with no success, further I have uninstalled & then reinstalled Business Contacts Manager with no success?????????? ...

Can't Re-Edit Draft
When I save a draft, WLM places it in the Drafts folder. When I open the draft, WLM won't allow me to edit it. Why is this? How can I open a saved draft at a later time for editing and eventual posting? TIA Geoff Right click on the message header | Open, or double click on it to open it back up in a compose window. -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "GeoffG" <geoffcg@nospamplease.com> wrote in message news:%23SsaGGhnKHA.1548@TK2MSFTNGP06.phx.gbl... > When I save a draft, WLM places it in the Drafts f...

Is there a way to add more reason codes
Is there a way to add more reason codes? Yes, in Manager look under the Database menu and there is a Reason Codes function there. -- Jason Hunt Advanced Computer Systems ...

Launch jscript code when clicking on view to see all Cases
Hello I realise that this is totally going to be an unsupported customisation, but I was wondering whether anyone knew how to change the inbuilt grid form for Cases so that I could call some jscript that altered the look of the default grid used for viewing cases. Any advice or pointers appreciated. Cheers Saira Hi, Saira. Have not had a chance to try it myself, but in theory this should work. Here is the general idea: Create some *.HTM page and put it on CRM Web Server. On the page, put iframe 100% x 100%, which has it's source set to actual Cases grid. Now, you'll need t...

CRM Sales for Outlook Install error 09-15-04
Hi, I'm having some problems installing the Sales for Outlook add in, the install detects the MSDE is needed & SP3. The install gets to the point where it tries to install the MSDE then brings up a Windows Installer dialog box, pressing ok on that displays another dialog box with the following error: Setup has failed because of an error. Setup was unable to install Microsoft SQL Server 2000 Desktop engine (SP3a). Install it seperately and restart setup.exe. I've done a manual install and the MSDE is there & running. Any clues on how to move forward with this? Thanks, R...

upgrading to newer edition of Money
Can any answer this: I'm using Microsoft Money 2002, and recently got Money 2004 for free when I bought some tax software, and I want to install it, but keep all the info. currently in my Money program. How do I do this? Do I just put the Money 2004 CD in the computer, install it, and all my info. will automatically transfer over? Thanks .. Anthony In microsoft.public.money, Anthony Martini wrote: >Can any answer this: I'm using Microsoft Money 2002, and >recently got Money 2004 for free when I bought some tax >software, and I want to install it, but keep all the...

How to return an error code from a function
Hi, after some discussions on the ng I decided to keep input data checking inside my functions. This prompts the problem of how to return an error code from the function: for example Function MySqrt(x as Double) As Double Dim err As Boolean If x <0 Ihen err=True Exit Function Else err=False x = Application.Worksheetfunction.sqrt(x) End If End Function However, err cannot be passed back to the caller! I've read about different workarounds, and I would like to know your opinion on them, or just which is your approach: 1. convert the Function to a Sub (easie...

formatting zip codes
I use Office XP and keep running into the same problem. When I export addresses from my database (FileMaker Pro) into Excel, the first zero in the zip codes never appears. So I use the format Cells, special, zip code option. The zero now appears in Excel, however, when I mail merge this in Word (in order to make labels, envelopes, form letters, etc.) the first zeros in the zip codes disappear again-- they do not appear in the merged Word document! What is going on? The only way I can get the zeros to remain in the merged Word document is by manually going through the Excel sheet ...

Migrate from Mas 90 to GP 8.0
We have a few companies that are running Mas 90 and we would like to migrate these companies to GP 8.0. Does anyone know of a Migration Tool? If so, the cost???? Thank You! ...

Money 2003 US Edition
I have purchsed a copy of Microsoft Money 2003 in the US, I am now trying to use it in south africa, but none of the online banking facilities are availabe. Only american banks apear on the list of institutions. Is there a site or download which I can use to update the program to inlude British and South African banks. Thans for your assistance ...

How to get an edit control with flat style( no 3d borders)
Select the Edit control in the resource editor and goto its properties. Select the Style tab and remove the Border style. -- Cheers Check Abdoul [ VC++ MVP ] ----------------------------------- "Jeff Huang" <jeff_hh@msn.com> wrote in message news:eC7xa42TDHA.1552@TK2MSFTNGP10.phx.gbl... > > ...

How to edit a named range
I have a named range in a source spreadsheet which is linked to other spreadsheets. I edited one of the values in the range (I changed C27 to C20) and I use this as an example to show references in the column - =C23, =C24, =C25, =C26, =C20, =C28, =C29 etc. However, when I run the macro to update the linked spreadsheet, the reference has reverted back to C27. How can I stop the link from changing back? Thanks in advance. ...

Word editing in outlook
I have my email setup to edit my outlook messages with word in Outlook 2000 and when I get html format messages I am unable to use highlighter in word to edit these messages. Any suggestions on how i might be able to get this to work? Kind of a weird question but I would appreciate the feedback. Thanks. ...

Publisher 2003 on Vista Home Edition
I bought a new computer with Vista Home Edition already installed. I have Publisher 2003, and when I installed it to the new computer it seems to work fine; but, it only wants to open the files I saved as a word 2007 doc, not as a pub file even though the publisher file was saved as a publisher document. I thought, since 2003 is not the most current version, that the computer is having problems saving the docs. So I downloaded the 2007 publisher tria,l the same thing happened. I checked the properties of publisher and they seem to be ok. WHAT'S WRONG ? PLEASE HELP! Are you opening t...

Upg 04
I've been using Money since 2001. I skipped the 05 upg and now just did the 06. It d/l and added two accounts from my bank that don't belong in Money. Older versions didn't do this. How do I fix. I started to delete the acct in money but decided I might mess something up. Please advise. ...

Convert Smartlist queries to Reports using SQL Reporting Services (GP 8)
I have a bunch of custom smartlists, but would now like to retrieve these reports using SRS. Is there a way to get the SQL query that smartlists is using? Assuming you did not base your SmartList objects on SQL queries or views, you could run a SQL trace to get the SQL statements generated. You could also turn on the DEXSQL.LOG file. -- Charles Allen, MVP "legendsfan" wrote: > I have a bunch of custom smartlists, but would now like to retrieve > these reports using SRS. Is there a way to get the SQL query that > smartlists is using? > > ...

How to view html source code
How do I view HTML source code in outlook 2003 ...