Sniptools » Tips/Tricks

Deleting all hyperlinks from a Microsoft Office document

January 9, 2003 Views (61,263) /  Comments (49) /  Trackback (0) / Digg/Share


Tired of getting Microsoft Office documents (Word, Excel, Powerpoint) that are chock-full of hyperlinks that are bright blue and easy to accidentally click, making Word goes berserk and open the link?

You can easily remove all those pesky URLs with a simple macro to remove all the hyperlinks in a document in one full swoop. With macros available to you in one keystroke (Alt F8) this is quite a convenient way of managing your logic ACROSS all Office software — the basic logic remains the same. Let’s see some code now:

A. Microsoft Word

  1. From a Word document, press ALT F11 to open the MS Visual Basic.
  2. The Visual Basic interface will open up. From the INSERT menu, click on MODULE to add a module.
  3. A new document opens up. In there, paste the following code: 
    Sub RemoveHyperLinksGLobally()
    Dim i As Integer
    For i = ActiveDocument.Hyperlinks.Count To 1 Step -1
          ActiveDocument.Hyperlinks(i).Delete
    Next i
End Sub

  4. Press CTRL S to save this module.
  5. Now, from the FILE menu, select “CLOSE AND RETURN TO MICROSOFT WORD”.
  6. When back into your Word document, now just press ALT F8 to bring up the MACROS selection window (or you can always do TOOLS —> MACRO —> MACROS)
Our macros is added

Not to worry, this does not delete the text, it just converts the hyperlink fields to plain text. However, if your original hyperlink had some formatting and you don’t want to lose that formatting for the text, but just want to get rid of the underlying URL, here’s some handy code for that too: 

Sub RemoveHyperLinksGLoballyButPreserveCharacterStyle()
    Dim i As Long, myRange As Range
    For i = ActiveDocument.Hyperlinks.Count To 1 Step -1
        With ActiveDocument.Hyperlinks(i)
            Set myRange = .Range
            .Delete
            myRange.Style = wdStyleHyperlink
        End With
    Next i
End Sub
TIP: Microsoft Word also has the “function” to auto-convert any text you type, that looks like a URL, into a hyperlinked URL. If you want to prevent this, select TOOLS —> AUTOCORRECT —> AUTOFORMAT AS YOU TYPE, and under “Replace as you type”, turn off “Internet and network paths with hyperlinks”.

B. Microsoft Excel

  1. Same process as a MS Word document. From Excel, fire up the VB editor (ALT F11).
  2. The Visual Basic interface will open up. From the INSERT menu, click on MODULE to add a module.
  3. A new document opens up. In there, paste the following code: 
    Sub RemoveHyperLinksGLobally()
    Dim i As Integer
    For i = ActiveSheet.Hyperlinks.Count To 1 Step -1
          ActiveSheet.Hyperlinks(i).Delete
    Next i
End Sub

  4. The only difference from the code for MS Word is highlighted in green. ActiveSheet, instead of ActiveDocument.
  5. Now, from the FILE menu, select “CLOSE AND RETURN TO MICROSOFT EXCEL”.
  6. When back into your Excel document, now just press ALT F8 to bring up the MACROS selection window (or you can always do TOOLS —> MACRO —> MACROS) and select the right macro. Bingo.

However, since we only work with the “ActiveSheet” in the above example, we remove the hyperlinks only from the single active sheet that is currently on the screen. To remove all the hyperlinks from all the worksheets in an Excel document, this code is handy: 

Sub RemoveHyperLinksGLoballyFromAllWorksheets()
    Dim i As Integer, wSheet As Worksheet
    For Each wSheet In Worksheets
        For i = wSheet.Hyperlinks.Count To 1 Step -1
          wSheet.Hyperlinks(i).Delete
        Next i
    Next wSheet
End Sub

Hopefull, this gives you ideas on how to author other macros too. With slight tweaks in code, they can be used across all Office software. Macros are powerful ways of making your editing fast. Plus, they are only a keystroke away (ALT F8) whenever you need them!

Need a primer to Microsoft Office macros? Read this interesting 101 piece from PC World first: http://snipurl.com/macros.

(642 Words | )

 
Possibly Related
Other stuff that may be of interest  
Comments
What readers have asked/said/added.

^1 Ozma said on July 8, 2003 6:18 PM:

Thank you, thank you, thank you!

^2 Thanh Hai said on July 23, 2003 6:08 PM:

Well done. Although some hyperlinks are still left unremoved.

^3 Dan Kahan said on August 30, 2003 9:56 PM:

works great! thanks for making it public & free!

^4 K.P. Ng said on September 3, 2003 4:02 AM:

Didnt work for me but I found a better way.

On the Edit menu, click Select All or CTRL+A.
Then CTRL+SHIFT+F9 on your keyboard. Duh!

^5 Shanx said on September 3, 2003 11:11 PM:

Thanks KP, but that one is easy googleable..the macro will work for more than one occasions (especially to used in other macros, which I find very useful).

Why did the macro not work for you though?

^6 AuWinger76 said on November 24, 2003 1:02 PM:

I didn’t have any luck with the macro either but the keyboard shortcut worked perfectly for me too! I’m not real familiar with creating macros so the errors could be mine! At any rate I’m thrilled to not have to do each hyperlink separately!! I couldn’t even do the ‘record macro’ function and use the keyboard shortcuts mentioned to set up the macro…! At any rate, thanks to all ‘cause I’m at least closer than I was!!

^7 Rashmi Patel said on December 11, 2003 8:17 PM:

KP That was great!!!!!!!!!!!!!!!!!!…
The macro didnt work for me too

^8 Shashank said on December 13, 2003 9:23 AM:

Hi Rashmi,

The method described above works for ALL MS Word installations I have tried it on — and this includes Office 97 through XP, En/Cn/Jp/Es/Kr versions. Let me know what error(s) you get, should be a cinch to get it sorted out.

Just so you know:

(1) If you are such a freak for keyboard shortcuts, macros are just one keystroke away — ALT F8. Most of my functions are in that one keystroke, instead of remembering CTRL SHIFT F9 for this, CTRL SHIFT SOMETHING ELSE for that etc etc.

(2) Macros are faster than “Select All” (Ctrl A) functionality. If you know the first thing about how Office works, you’ll know that the latter takes a lot of RAM, especially on large documents. On a 2-3 page Word document, it doesn’t matter what you use.

(3) Thirdly, and more importantly, as you will see when I modify/add to this article above, the macro is very useful for tech-savvy folk because the same logic can be used across all Office tools. Try CTRL+SHIFT+F9 in Excel and we’ll talk.

Cheers,
Shanx

^9 Seth! said on January 14, 2004 3:38 PM:

Shashank, this is very cool but can you post one that removes all hyperlinks globally from all worksheets (in Excel) AND leaves other text formatting alone? You did that for the Word macro and I tried to modify your Excel macro but was unsuccessful. Thanks!!

^10 Sridhar Vemuru said on February 6, 2004 3:46 AM:

Good Work Shanx. It worked great for me.

Thanks

Sridhar

^11 Colleen said on February 20, 2004 5:05 AM:

You can do this in Excel: With the excel ssheet open, press ALT 11. select Module under the Inser menu.

Type this into the new module:

Sub RemoveHyperlinks()

‘Remove all hyperlinks from the active sheet
ActiveSheet.Hyperlinks.Delete

End Sub

Then,select “Close and REturn to Microsoft Excel”

then run the macro called “RemoveHyperlinks” in your excel ssheet…

^12 Shanx said on February 21, 2004 11:22 AM:

Hi Colleen, this is the code I had before editing this entry. But that version did not always work for everyone, a bug in MS Office I guess. It’s useful to know anyway, thanks.

^13 hbchrist said on March 31, 2004 3:46 AM:

Hate to be a Johnny Come Lately, but I just found and used this. Very nice, and thanks!

^14 Kiril said on April 1, 2004 12:17 AM:

It work perfect! Thanx!

^15 BabyBop said on April 14, 2004 6:20 AM:

Terrific macro — it did just what I wanted. Thanks!

^16 a.h. said on April 21, 2004 4:44 PM:

did not work!

BUT THANKS TO REPLY NO.4 I GOT RID OF THOSE PESKY BASTARDS!

Thank you K.P.!

^17 Shanx said on April 21, 2004 8:24 PM:

“a.h.”, can you tell me what version of Word you’re using?

^18 kate said on April 22, 2004 2:53 AM:

Hi—

Works great in Word, but is there any way to do the same thing in PowerPoint (i.e. remove all hyperlinks from all slides at once)? The Word macro doesn’t do anything in PP.

Thanks!
kate austin

^19 kate said on April 22, 2004 3:42 AM:

P.S. Re PowerPoint— I tried substituting “Presentation” for “Document” in the macro, but still no go.

Thanks again,
kate

^20 SAM said on July 21, 2004 4:54 AM:

HOW DO I STOP IE ACCESS FROM WORD AND EXCEL?

^21 Opo said on September 13, 2004 3:51 AM:

thanks!

^22 Kev said on October 7, 2004 6:00 PM:

Useful code. Thanks.

^23 D. Raghunadharao said on January 1, 2005 2:17 PM:

Excellent macro for word document: I followed the following:
Opened the document
Pressed alt F8
Pressed create button
pasted the following:
Sub RemoveHyperLinksGLoballyButPreserveCharacterStyle()
Dim i As Long, myRange As Range
For i = ActiveDocument.Hyperlinks.Count To 1 Step -1
With ActiveDocument.Hyperlinks(i)
Set myRange = .Range
.Delete
myRange.Style = wdStyleHyperlink
End With
Next i
End Sub
saved
returned to main document
tool … macros
run macro created
viola! all hyperlinks removed!

^24 Santosh said on January 18, 2005 2:59 PM:

Hi Guys,

I wanna to add the hyperlinks in my MS Word document automatically. I tried to make it on from autocorrect option. I also uninstalled the MSoffice and reinstalled. But still the problem is there. So can anybody suggest me solution for how to insert hyperlinks automatically.
Thanks
Santosh

^25 appreciative one said on May 4, 2005 2:48 AM:

bad ass this macro is- turned my pain into cynical pleasure- go to hell hyperlinks!

^26 DD said on May 25, 2005 7:04 PM:

The macro worked perfectly for me.

A very large THANK YOU!

^27 Andrew Timleck said on June 19, 2005 5:46 AM:

Thanks, Works in Mac for Office (v 2004). Slight alterations - when returning to the word doc from the macro you’ll find the quit from macro under the “Word” menu, not file. Worked great, saved me tons of time.

^28 veiky said on September 22, 2005 7:02 PM:

Your code is perfct but it does not remove all hyperlinks from a worksheet.
Suppose worksheet has a organization chart with a hyperlink then it dosn’t removes it…
I have tried following thing but not removing from organization chart….

Dim objexcel As Excel.Application
Dim objworksheet As Excel.Worksheet
Dim objworkbook As Excel.Workbooks
Dim hlink As Excel.Hyperlinks

Private Sub cmddelete_Click()
Set objexcel = CreateObject(“Excel.Application”)
objexcel.Workbooks.Open (“c:\test hyper.xls”)
objexcel.WindowState = xlMinimized
objexcel.WindowState = xlMaximized


Dim Shp As Excel.ShapeRange
Dim IShp As Excel.Shape
Dim i As Integer
Dim j As Integer
On Error GoTo ResNextShp


For j = 1 To objexcel.ActiveSheet.Shapes.Count

objexcel.ActiveSheet.Shapes(j).Select
Set Shp = Selection.ShapeRange
If Shp.HasDiagramNode = msoTrue Then
For i = 1 To Shp.DiagramNode.Diagram.Nodes.Count
Set IShp = Shp.DiagramNode.Diagram.Nodes(i).Shape
If IShp.Hyperlink.Address > “” Then
IShp.Hyperlink.Delete
End If
Next i
End If
Set IShp = objexcel.ActiveSheet.Shapes(j)
If IShp.Hyperlink.Address > “” Then
IShp.Hyperlink.Delete
End If
Next j
i = 0

For i = objexcel.ActiveSheet.Hyperlinks.Count To 1 Step -1
objexcel.ActiveSheet.Hyperlinks(i).Delete
Next i

Exit Sub

ResNextShp:
Resume Next

End Sub

^29 Dan said on September 29, 2005 9:07 PM:

Please someone enlighten me. I have a document where I actually want to delete any text which is a hyperlink. ie. take all the links out of document

Thanks in advance

^30 echo said on October 27, 2005 7:43 PM:

Great tool - but in my documents I have a table of contents (from Insert>Reference>Index and Tables) which hyperlinks within the document, and for some reason, when I run the macro, the hyperlinks in the table of contents are removed, but the text is formatted as hyperlinked (blue and underlined) when they weren’t before the macro was run. I’d like the table of contents to remain as formatted before - any way to to this? Thanks.

^31 Colin said on November 8, 2005 5:44 AM:

I need the opposite of this. I have hundreds of Pictures (picture 1, picture 2 etc.) with hyperlinks associated with them. I want a macro to run through all of the pictures and then write the associated hyperlink in text format in a row (a1, a2, a3 etc)

Ideas please?

^32 Joseph said on December 7, 2005 6:57 AM:

It work perfect…………….
Thank you so much……………………………….

^33 dp said on January 18, 2006 4:31 PM:

thanks mate, swell job and a real help.

^34 Serious Sam said on March 8, 2006 1:48 PM:

The macro was very useful…but there has been a slight glitch.
By mistake I have applied the Word document Macro to all templates and documents. As a result whenever I copy and paste any web page into microsoft word, all the hyperlinks get converted into plaintext. Tell me a way to remedy this problem.

Thanks a lot.

^35 Soefje said on March 11, 2006 1:07 AM:

I have tried this on a large spreadsheet and keep getting a run time error.

Run time error ‘6’:

Overflow

When I open the debugger it points to the following line:

For i = ActiveSheet.Hyperlinks.Count To 1 Step -1

I am not a program, so I don’t know what is going on. I am using Excel 2000.

Any help would be appreciated.

Thanks

^36 Soefje said on March 11, 2006 2:26 AM:

Nevermind, figured it out.

In the second line, I changed integer to long. Interger go up to 32767.

It works fine now. Looks like this.


Sub RemoveHyperLinksGLobally()
Dim i As Long
For i = ActiveSheet.Hyperlinks.Count To 1 Step -1
ActiveSheet.Hyperlinks(i).Delete
Next i
End Sub

^37 Sara said on March 17, 2006 12:36 PM:

I have been trying to write a macro that will create an hyperlink from one tab to another tab within my excel workbook.

This macro needs to be flexible so I’m trying to embed a variable like “strtabname & !A1” to set the hyperlink’s link to cell A1 of a particular tab.

So far I can create an inactive hyperlink. Suggestions will be most appreciated, this is driving me crazy and I have yet to find a suitable example online or amongst co-workers. Thank you.

^38 TBJC said on April 20, 2006 4:08 AM:

Thanks! Worked great - saved me hours!!

^39 Syed Mohammad Ali said on May 30, 2006 3:16 PM:

Can somebody please tell me how to convert text that looks like a URL programmatically to a hyperlink. I know this feature can be incorporated by autoformating but I want to do it programmatically.

^40 Tom LaRussa said on May 31, 2006 11:16 PM:

THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!! THANK YOU!!!


Your little bit of code has saved me G*d only knows how many hours of frustration!

I only wish I’d found your tip sooner, as I’ve been removing links from Word docs (I use Word 97) manually for years.

Again,

THANK YOU SO MUCH!

^41 Brent Hugh said on August 20, 2006 5:22 AM:

I had a similar problem, I pasted a page with a bunch of “email this contact” links from a web page into Word. Then I wanted to convert all the hyperlinks Word made into just plain text email addresses rather than a blue link that said “email this contact”.

This script did the trick:

Sub ChangeEmailAddressHyperlinkToPlainText()
‘
’ Macro
’ Macro created 8/19/2006 by Brent Hugh

‘
Dim i As Integer
For i = ActiveDocument.Hyperlinks.Count To 1 Step -1
ActiveDocument.Hyperlinks(i).Range = ActiveDocument.Hyperlinks(i).Address
Next i

End Sub

^42 charuta kulkarni said on September 1, 2006 6:09 PM:

Hey,
this is so cool. i work with gigantic word docs full with links that i m supposed to remove. this is a blessing for me.
thanks a zillion.

^43 Jennie said on October 9, 2006 9:23 AM:

Thanks!!! I was having a really hard time trying to delete the hyperlinks from my 30+ page document and found this. After I did the Macro thing, all the hyperlinks disappeared like magic. Thank a million!!!!!!! You can’t believe how grateful I am right now. =)

^44 Tom said on November 12, 2006 9:30 PM:

Ecellent! Thanks!

^45 gordon said on January 23, 2007 6:18 PM:

Solution for Dan [29]:
This means finding text with different format (usualy Blue + Underlined), and replacing with “nothing” for each found item. Here is a simple method:

Open Find/Replace dialog box

Find What: Font: Underline, Font color: Blue

will find all Underline and Blue strings, and

Replace With: (leave box empty)

click ‘Replace All’


^46 Trippytom said on April 4, 2007 1:44 AM:

This is how you would do it in PowerPoint (works in 2003, not sure of earlier versions):

Sub NoHyperlinks()

Dim i As Long
Dim hl As Hyperlink

For i = 1 To ActivePresentation.Slides.Count
For Each hl In ActivePresentation.Slides(i).Hyperlinks
hl.Delete
Next hl
Next i

End Sub

^47 Polly said on June 12, 2007 5:56 AM:

I have tried all of the above to get rid of hyperlinks from a formatted inventory (mostly color fill, font colors &sizes, EXCEL worksheet I use everyday. I cannot get rid of them. For awhile (about a week) it worked, but today when I opened up the sheet (with macros enabled) it was riddled with them….worse than before. If I run:

Sub RemoveHyperLinksGLoballyFromAllWorksheets()
Dim i As Integer, wSheet As Worksheet
For Each wSheet In Worksheets
For i = wSheet.Hyperlinks.Count To 1 Step -1
wSheet.Hyperlinks(i).Delete
Next i
Next wSheet
End Sub

I get a run time error and Excel auto recovers and closes. I have run others (in addition to the above) that I have found on Google. But to no avail. Where do these d*** things come from. I’ve never added a hyperlink anywhere in this worksheet, but now every cell across and down for 171 rows has the same one. It’s an inventory document and even if I CAN get rid of them, I will have to reformat 2200+ cells to reflect inventory changes. HELP!!!

^48 Jessica said on June 14, 2007 2:51 AM:

thank you this actually worked
but the problem is that is does get rid of the hyperlinks but not the hyperlinks of any pictures

^49 Stephen Bridge said on July 29, 2007 7:16 PM:

Thank you very much! This has been a real help to me.

 

Post a comment
Please be decent while posting comments, don't make us delete them, thanks.




Will be spam-protected.

Remember?


Comment Preview

XX said on :

 

Trackbacks
Ping @ http://sniptools.com/ping/2003-01/25. Listed below are links to weblogs that reference this article.

 

Referrals
*Some* places that viewers have come to this page from: