Solved

Create Hyperlinks in Excel?

  • 19 November 2013
  • 16 replies
  • 125 views
davideagle
Rank
Badge +14
Does anyone have any bright ideas. We're trying to write hyperlinks into an Excel worksheet but failing to do so. The old Excel ADO writer doesn't support this (http://fmepedia.safe.com/articles/FAQ/Reading-hyperlinks-from-an-Excel-spreadsheet) but I thought the new one might with perhaps some of the format codes that are now supported in the new writer (http://office.microsoft.com/en-ca/help/number-date-and-time-format-codes-HP005207595.aspx) but it appears not.

 

 

If you try to be clever and write a concatenated formula string into Excel with either writer like this:

 

 

=HYPERLINK("http://www.1spatial.com/fme","http://www.1spatial.com/fme")

 

 

...FME just writes it explicitly as above... we've even tried adding a return line in the concatenator to no avail. Ironically if you open the results of this and click into the cell and hit return it enables the hyperlink, so it's not a formatting issue either.

 

 

So, I guess my plea is, has anyone done this or does anyone have any smart Python scripts that could be issued as a shutdown script to open the written out worksheet and enable the hyperlinks and close it out again?

 

 

Here's hoping ;0)

 

Cheers
icon

Best answer by david_r 19 November 2013, 16:17

View original

16 replies

david_r
Rank
Userlevel 4
Hi Dave,

 

 

did you write your formula directly to the cell attribute, or did you write it to <cell attribute name>.formula?

 

 

See here: http://docs.safe.com/fme/html/FME_ReadersWriters/Default.htm#xlsx/Feature_Representation.htm

 

 

David
davideagle
Rank
Badge +14
Thanks David - I'm not familiar with that option? I have just been writing into a standard recieving attribute name. I actually can't get any formula to resolve correctly, for example if I create this in a string concatenator =MEAN($C$2:C2)) and write it out that's exactly what I see in the spreadsheet, rather than the results of the formula.

 

 

This recent blog posting states that you can write formulas, but I can't spot how...

 

 

http://blog.safe.com/2013/10/tale-magical-transformation-excel-spreadsheet/

 

 

I suspect it's obvious but I've been staring for too long!
david_r
Rank
Userlevel 4
I'd say that it's deceptively easy since it isn't terribly well documented...

 

 

If you have an attribute called "myURL" you simply create an attribute called "myURL.formula" and write your formula there.

 

 

Example:

 

 

 

 

Two things to notice:
  1. the HYPERLINK will not automatically be formatted as such, although it will behave as it should. You can override the font and color, though.
  2. For the SUM to work, make sure that the columns "val_a" and "val_b" are defined as "number" on the writer.
Hope this helps.

 

 

David
david_r
Rank
Userlevel 4
Resulting Excel-file could look something like this:

 

 

 

When I switch on the formulas, we can see that the formulas have indeed been written as such:

 

 

 

 

It even translated the formulas for me :-)

 

 

David
davideagle
Rank
Badge +14
David - A thousand thank you's! It took me a few moments to realise that to turn the attribute blue and underline it you actually have to define the attribute twice:

 

 

1) URL - no need to write anything here, just access the 'Edit' button for the User Attribute and set the blue and underline style here.

 

2) URL.formula - Send the concatenated formula here.

 

 

Job done. I agree, it was a fiddle and the documentation is lacking but we got there in the end, thanks so much!
D
Badge +4
I'd say that it's deceptively easy since it isn't terribly well documented...

 

 

If you have an attribute called "myURL" you simply create an attribute called "myURL.formula" and write your formula there.

 

 

Example:

 

 

0684Q00000ArKPWQA3.png

 

 

Two things to notice: 
  1.   the HYPERLINK will not automatically be formatted as such, although it will behave as it should. You can override the font and color, though.
  2.   For the SUM to work, make sure that the columns "val_a" and "val_b" are defined as "number" on the writer.
 Hope this helps.

 

 

David
@david_r I think I tried this but can't get it to work. I'm constructing a URL in one attribute, then made another one like you suggested and called it URL.FORMULA, and that's where I put this: 
=HYPERLINK("@Value(URL)",12345)
.

When I write it to the XLSX, I still have to click in the cell, hit F2 + Enter before it is actually made into a hyperlink. Any suggestions?
D
Badge +4
Hi Dave,

 

 

did you write your formula directly to the cell attribute, or did you write it to <cell attribute name>.formula?

 

 

See here: http://docs.safe.com/fme/html/FME_ReadersWriters/Default.htm#xlsx/Feature_Representation.htm

 

 

David
The link you posted in 2013 is now broken, but here's the updated link: https://docs.safe.com/fme/html/FME_Desktop_Documentation/FME_ReadersWriters/xlsx/xlsx.htm.

 

 

takashi
Rank
Userlevel 2
Badge +17
@david_r I think I tried this but can't get it to work. I'm constructing a URL in one attribute, then made another one like you suggested and called it URL.FORMULA, and that's where I put this: 
=HYPERLINK("@Value(URL)",12345)
.

When I write it to the XLSX, I still have to click in the cell, hit F2 + Enter before it is actually made into a hyperlink. Any suggestions?
Hi @dmatranga, It was a workaround in old versions of FME which didn't support Excel hyperlink officially. Currently (maybe 2015+?), you can set the display value (12345) and its hyperlink URL (@Value(URL)) to a destination cell separately, with adding the suffix .hyperlink to the attribute name for the URL, like this.

 

0684Q00000ArN2AQAV.png

D
Badge +4
Hi @dmatranga, It was a workaround in old versions of FME which didn't support Excel hyperlink officially. Currently (maybe 2015+?), you can set the display value (12345) and its hyperlink URL (@Value(URL)) to a destination cell separately, with adding the suffix .hyperlink to the attribute name for the URL, like this.

 

Thanks Takashi. So when you actually write an attribute to the XLSX, it would be Field.hyperlink, correct?

 

 

And what value does the attribute URL contain? Is it just a string with a URL? Or does it contain the Excel formula for HYPERLINK?

 

takashi
Rank
Userlevel 2
Badge +17
Thanks Takashi. So when you actually write an attribute to the XLSX, it would be Field.hyperlink, correct?

 

 

And what value does the attribute URL contain? Is it just a string with a URL? Or does it contain the Excel formula for HYPERLINK?

 

No, you should just write "Field".

 

Assumes that the attribute URL stores a URL string. e.g. http://www.safe.com

 

D
Badge +4
@takashi I finally got it to work! Your screenshot finally helped me figure this out, thanks again.

D
Badge +4

One last note for the benefit of other people experiencing issues: the word 'hyperlink' must be lowercase.

Apparently that was the issue that I was having the entire time. I'm usually OCD about my attribute names being in ALL CAPS, but for formulas they have to be in lowercase. :)

C
Badge
@takashi I finally got it to work! Your screenshot finally helped me figure this out, thanks again.

Digging up an old thread here, but I'm having trouble making the hyperlinks active. I have to double-click inside the cell, then click outside it to make it active, or click in the cell and press F2 and Enter. They are not active (blue, underlined) when I open the spreadsheet.

nampreetatsafe
Rank
Userlevel 1
Badge +10

Digging up an old thread here, but I'm having trouble making the hyperlinks active. I have to double-click inside the cell, then click outside it to make it active, or click in the cell and press F2 and Enter. They are not active (blue, underlined) when I open the spreadsheet.

Hi @ctownsend! I'm sorry you ran into this issue. What version of FME are you using?

I found a problem report (internal reference FMEENGINE-60326) for this issue which seems to have appeared in FME 2018. The suggested workaround is to explicitly add the hyperlink attribute (i.e. Field.hyperlink) to the output schema (Writer Feature Type Parameters > User Attributes):

 

Give this a shot and let us know if it works for you.

We'll try posting any updates to the problem report here on this thread.

C
Badge
@takashi I finally got it to work! Your screenshot finally helped me figure this out, thanks again.

Hi @nampreetatsafe, I have that in my settings. I'm using 2019.0. In any case, after a bit more digging, this seems to be a security setting we have for Excel that prevents active hyperlinks and macros being embedded in spreadsheets that we cannot turn off. So I have a small macro that I have stored separately that I can run which makes the hyperlinks active and persists after saving the spreadsheet.

ponceta
Badge

One last note for the benefit of other people experiencing issues: the word 'hyperlink' must be lowercase.

Apparently that was the issue that I was having the entire time. I'm usually OCD about my attribute names being in ALL CAPS, but for formulas they have to be in lowercase. :)

AND add this attribute Field.hyperlink as a standard attribute to your Excel Writer too ;)

No specific synax for both attribute is required :

Field : lien

Field.hyperlink : https://knowledge.safe.com/questions/3266/create-hyperlinks-in-excel.html?childToView=35259

 

Reply


Community Stats

28,706
Posts
109,467
Replies
36,549
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.