E-mailing spreadsheet without hyperlinks

[Wilfred]

I use a spreadsheet to bill a company for goods and services. For my own
control purposes, I set up hyperlinks to cross reference unit totals with
the actual products. For example, cells that contain the daily total photo
batches I upload to the company's FTP server have hyperlinks to the actual
folders on my hard drive that contain the photographs that I am billing for.
This allows me to easily pull up a batch of photos that matches an invoice.

I want to copy the bi-weekly spreadsheet and e-mail it to the company's
billing department without sending the hyperlinks. How do I remove multiple
hyperlinks on a workbook (with multiple sheets) without having to manually
remove each link?

 

[Bernie Deitrick]

Wilfred,

To remove the hyperlinks, but will leave the text:

Sub RemoveAllHyperLinks()
Dim mySht As Worksheet
Dim myHyper As Hyperlink
For Each mySht In Worksheets
mySht.Cells.Hyperlinks.Delete
Next mySht
End Sub

To remove all text and hyperlinks:

Sub RemoveAllHyperLinks2()
Dim mySht As Worksheet
Dim myHyper As Hyperlink
For Each mySht In Worksheets
For Each myHyper In mySht.Hyperlinks
myHyper.Range.ClearContents 'To remove completely
Next myHyper
Next mySht
End Sub

HTH,
Bernie
MS Excel MVP

 

[Wilfred]

Thanks, but I am afraid that the reply is over my head. What do I do with
this information?

 

[Bernie Deitrick]

Wilfred,

Put both the macros into a codemodule in the workbook, then run one of
them to convert your hyperlinks to text (the first one) or to delete
them entirely (the second one). Use Alt-F11 to get into the VB Editor,
then Ctrl-R to show the explorer window, then choose your file in that
window, and use Insert |Module. Then paste the macros, and use Tools |
Macros |MAcros... and run the one of your choice.

HTH,
Bernie
MS Excel MVP

 

[Wilfred]

WOW!

Tthat was just way too easy!

Thanks a lot!