Excel - show Webpage name 1

[JeanW]

I just got a Excel file with 9000+ rows to import into a FM database.

A column in the Excelfile shows the 'text to display' in bleu, which is just a name, and the cell is formatted as a button to go to the webpage name.

Example:
on screen (column C) Brussels ( In bleu) -
Webpage when mouse-over:

http://www.world-codes.gov/belgium/brussels-566.html

A click on this cell will open the given webpage.

Is there a way to have to whole internetaddress in a cell ?
I need that specific address to import into the database.

I have Excel 2000 SP3
TIA

 

[GeekGirlau]

Create a function in the VBE window of your workbook as follows:

Public Function GetLink(rng As Range)
    GetLink = rng.Hyperlinks(1).Address
End Function

In your worksheet, your formula would be "=GetLink(A1)", assuming that cell A1 contains the link.

 

[JeanW]

Thanks for the tip GeekGirlau.

When I follow your steps, I get a #NAME? result.

I created the function, but it doesn't show in the function list. (How to save ?? )
I am obviously missing something.

I'm obsolutely not used to work in Excel and even less making functions in spreadsheet programs.
FM databese is my daily tool.
Maybe I need more details.

 

[GeekGirlau]

Where did you paste the code?

* Open your workbook
* Press Alt-F11 to go to the VBE window
* Make sure the Project Explorer is displayed down the left - you should see "VBAProject (YourWorkbook)". If not, select View | Project Explorer
* Insert a module into your workbook by selecting Insert | Module
* Paste the code into the new window that appears
* Exit out of the VBE window
* Save your workbook

You should find that the formula now works for this workbook.

 

[JeanW]

* Open your workbook - Yes
* Press Alt-F11 to go to the VBE window - Gives me a 'chartWindow', but Tools -> Macro -> VB Editor seems to work
* Make sure the Project Explorer is displayed down the left - you should see "VBAProject (YourWorkbook)". If not, select View | Project Explorer - OK
* Insert a module into your workbook by selecting Insert | Module - Yes
* Paste the code into the new window that appears - OK
* Exit out of the VBE window - Yes
* Save your workbook - OK
You should find that the formula now works for this workbook.

I can use the function Get(Link) now, but when I point to cell A1, in B1 I get the #VALUE! message.

I would say I'm half way and I learned something today, not everything is lost !!

 

[GeekGirlau]

Your formula should be "=GetLink(cell)", where cell is the cell address containing the hyperlink.

 

[JeanW]

Sorry GeekGirlau, your right,it is =GetLink(A1).

I'm used to write the functionsyntax as in FileMaker.

I checked everything twice,but still have the #VALUE!.

The function is working for a 'range' (which is OK) but seems not to work for just a cell.
Or do I have to give A1::A1....will try that...

 

[GeekGirlau]

No, should be fine for just a single cell.

Maybe try it first on another example. Start a new workbook and copy and paste the code into it. In cell A1, type the word "Test". In cell A1 again, press Ctrl+K to create a hyperlink, and enter a valid web address in the Address section.

In cell B1, type your formula as "=GetLink(A1)".

Does this work?

 

[JeanW]

It seems to be a refresh problem...

Changng the hyperlink text doesn't change the result of the function.
When I re-enter the cell value in the formula bar the result is ok.

For now I can live with that, I have all my 'real' values and can import them in FM.
Thank you very much.

I will look further to resolve the refresh issue.

 

[GeekGirlau]

You probably need to recalculate those cells in order to refresh (one of the down-sides of a custom function).