Solved

Find and replace hyperlink in Excel 2007

Posted on 2013-12-17
5
510 Views
Last Modified: 2013-12-17
Hi

Trying to save my Excel 2007 has an xml file.  When I do a "Save As" I select xml -- then I get a warning saying "Cannot save XML data because the workbook does not contain any XML mappings."

Basically what I want to do a search and replace of hyperlinks.

For example, My hyperlink would be http://www.bananas.com/apples.html

And I want to change it to

/apples.html

I googled hyperlink search and replace and was told one method is to save as hyperlink and then open in notepad or similar and do the search and replace there.

However as I write this question, perhaps there is an easier way to do it?  Perhaps with a visual basic script????

Or a formula?

Thanks

Rowby
0
Comment
Question by:Rowby Goren
  • 2
  • 2
5 Comments
 
LVL 8

Assisted Solution

by:5teveo
5teveo earned 100 total points
ID: 39724528
Try this for starters... MAcro runs agains data where link searched is assumed to be in column 'A'. I attached Excel Spreahsheet w/ macro

Sub Macro1()
'
' Macro1 Macro
'
'
 Worksheets("Data1").Select
 Cells.Select
 Selection.ClearContents

 RowIndexDestination = 1

 Worksheets("Data1").Cells(1, "A").Value = "Extracted Data"


'Get Date
 For rx1 = 1 To 10000
     With Worksheets("Report").Cells(rx1, "A")
          'Check for Last Line
         If .Value = "" Then
             Exit For
         End If
         
         'Date
         If InStr(1, Worksheets("Report").Cells(rx1, "A"), "http") > 0 Then
            RowIndexDestination = RowIndexDestination + 1
            Worksheets("Data1").Cells(RowIndexDestination, "A") = Right(Worksheets("Report").Cells(rx1, "A"), Len(Worksheets("Report").Cells(rx1, "A")) - InStrRev(Worksheets("Report").Cells(rx1, "A"), "/") + 1)
         End If
       
     End With
     
 Next rx1
 
End Sub
EE-20131217-ChangeText.xlsm
0
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 400 total points
ID: 39724571
Searches the entire sheet for URLs up to the last cell and replaces both the URL and the display value via regex:
Public Sub TrimUrls()
    ' Find the data
    Dim rowMax As Integer, colMax As Integer
    Dim ws As Worksheet
    
    Set ws = Application.ActiveSheet
    rowMax = ws.Cells.SpecialCells(xlLastCell).row
    colMax = ws.Cells.SpecialCells(xlLastCell).Column

    ' Loop replacing
    Dim row As Integer, col As Integer
    Dim ce As Range, re As Variant
    Dim result As String
    
    For row = 1 To rowMax
        For col = 1 To colMax
            Set ce = ws.Cells(row, col)
            If ce.Hyperlinks.Count > 0 Then
                Set regEx = CreateObject("vbscript.regexp")
                With regEx
                    .IgnoreCase = True
                    .MultiLine = False
                    .Pattern = "^[^#]*?://.*?[^/]*"
                End With

                result = regEx.Replace(ce.Hyperlinks(1).Address, "")
                ce.Hyperlinks(1).Address = result
                ce.Value = result
            End If
        Next
    Next

End Sub

Open in new window

Example.xlsm
0
 
LVL 9

Author Comment

by:Rowby Goren
ID: 39724698
Hi

I'm getting an error with 5teveo's version (perhaps I copied and pasted it wrong. (Subscript out of range.)

And Angelplay is also changing the text of the hyperlink.   I need to keep the visible text untouched.

I should have uploaded a sample file.  The attached includes both vb solutions.

Thanks for helping!

Rowby
expert-exchange-1.xlsx
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39724710
Just remove ce.value=result from mine.
0
 
LVL 9

Author Closing Comment

by:Rowby Goren
ID: 39725470
Thanks!  It took care of the issue!

Rowby
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question