Desktop Applications: October 2006 Archives

2006 Oct 14

Adding Dynamic Hyperlinks in Excel

Bookmark and Share By
tildemark
| | Comments (0) | TrackBacks (0)

Creating a hyperlink in excel is simple. Notice that after typing any uri in excel the text automatically turned into a hyperlink. You can add any link as you like in excel as long as there is a prepended http:// phrase before the text entered.

Now, what if we are going to add a dynamic URL? It would be best explained by example, so here it goes.

For example: We are asked to check multiple sites for the following internet standards; W3C HTML Validator and WDG HTML Validator.

We have a reference cell A1 have the text http://tildemark.com. We all know the URL for the respective standards as:

W3C - http://validator.w3.org/check?uri=http://www.tildemark.com
WDG - http://www.htmlhelp.com/cgi-bin/validate.cgi?url=http://www.tildemark.com&warnings=yes

Now, all we need to create an entry such that each time we change the domain names the entry on each validator also changes.

Do the following on Excel:

Cell A1 - http://www.tildemark.com

  A B
1http://www.tildemark.com       
2  
3  
4  
5  
...  
9=HYPERLINK
("http://validator.w3.org/check?uri="&A1,"Check W3C HTML Validator")		  
10

=HYPERLINK
("http://www.htmlhelp.com/cgi-bin/validate.cgi?url="&A9&"&warnings=yes",
"Page passed WDG validator")

 

More info at Office Website.

2006 Oct 11

Removing multiple hyperlinks in Excel

Bookmark and Share By
tildemark
| | Comments (1) | TrackBacks (0)

Removing a bunch of hyperlinks can be tedious. imagine the time you have to right-click on each of those links and selecting 'remove hyperlink' on the context menu. Of course it would just take two clicks, but what if you are to remove a hundred of them or perhaps 1000 of them? I'm sure that would scrape out the paint of your right-mouse button. :)

I have some simple techniques you can use in order to remove multiple hyperlinks on a page:

Removing just one hyperlink
1. Right-Click on the cell and select Remove Hyperlink on the context menu.

Removing the cell format
1. Select the cells having hyperlinks. You can use CTRL+LEFT CLICK to randomly select a cell.
2. On the menu click on Edit. Hover your mouse to the option Clear and select Fomats. Please note that the cell is still clickable to remove the hyperlink completely you have to select Clear->All.

Remove hyperlink on selected cells the better way
1. Type in any text or number in a blank cell
2. Right-click and select Copy on the context menu.
3. While pressing CTRL, select each hyperlink you wish to be removed
4. On the Edit menu, select Paste Special.
5. Under Operation, click Multiply and then click OK.

Removing using a macro
Assuming you know excel programming you could create a macro to automatically remove the hyperlinks.
1. Start Visual Basic Editor. Alternatively you can press ALT-F11 to start the editor.
2. Double click the workbook you are using on the Project Explorer.
3. Type the following text:


Sub RemoveHyperlinks()
ActiveSheet.Hyperlinks.Delete
End Sub

4. Save your work.
5. Run your macro by pressing ALT-F8 or using the menu by Tools->Macro->Macros
6. Select the macro you have made, it should have the name 'RemoveHyperlinks'.


« Desktop Applications: March 2005 | Main Index | Archives | Desktop Applications: December 2007 »

Today is

Search

About this Archive

This page is a archive of entries in the Desktop Applications category from October 2006.

Desktop Applications: March 2005 is the previous archive.

Desktop Applications: December 2007 is the next archive.

Find recent content on the main index or look in the archives to find all content.

Recent Activity

Today

Wednesday

  • tildemark tweeted, "a very disturbing statistics on facebook http://bit.ly/efnM"
  • tildemark tweeted, "gmail offline, will this also mean that my mails will be saved locally whenever i go? even in internet cafes?"
  • tildemark tweeted, "@dorksterdave maybe its already blocked??"
  • tildemark tweeted, "@insic you might find this one interesting as well. http://www.stickmanlabs.com/lightwindow/"
  • tildemark tweeted, "exploring the world of expression engine..."
  • tildemark tweeted, "Hello Twitterland, Im back from the great sandawa flood."

Tuesday

  • tildemark tweeted, "Flood! Enjoyed the experience."

Monday