topic Re: ODS EXCEL creating hyperlinks improperly in ODS and Base Reporting

ODS EXCEL creating hyperlinks improperly

PaigeMiller — Mon, 11 Jan 2016 21:01:49 GMT

I am trying to create a spreadsheet using ODS EXCEL. Everything comes out properly, except for the hyperlinks.

As you can see, I am trying to use the HYPERLINK command to create the hyperlink and the text in the cell. (By the way, this works in ODS TAGSETS.EXCELXP). However, the B5 cell, the cell selected in the screen capture, does not recognize the text as a hyperlink. In fact, if you look at the text box (highlighted in red) where the actual text of the cell is displayed, the text is split across three lines.

If I manually remove the line breaks by editing the text box so all text is on one line, then the hyperlink works, as shown in the next screen capture.

How do I fix this? Or is this a bug?

Re: ODS EXCEL creating hyperlinks improperly

Ksharp — Tue, 12 Jan 2016 02:41:04 GMT

Can you use STYLE to get it ?

style={url='.........'}

" ~S={url='.......' } sqrt(Pixel......) "

Re: ODS EXCEL creating hyperlinks improperly

PaigeMiller — Tue, 12 Jan 2016 14:49:00 GMT

There's still something going on that I don't understand, seems like a bug to me. The text that I want to write in the field still shows line breaks in Excel, when the input data has no line breaks or other non-printing characters.

Re: ODS EXCEL creating hyperlinks improperly

Dan_Miller — Tue, 21 Mar 2017 03:30:45 GMT

I know this question is now over a year old but I have come across it a couple of times while looking into how to do hyperlinks. Just found what your problem would have been related to while reading this:

http://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/

'When text wraps, a hard return is added automatically to the cell ' so you either need to set the width of the cell in which you want the hyperlink wide enough that it dosn't need to wrap or use the flow option to stop the automatic wrapping:

ods excel file="c:\temp.xlsx" options(flow="tables");

Hope this is useful to someone...

Re: ODS EXCEL creating hyperlinks improperly

PaigeMiller — Tue, 21 Mar 2017 13:20:21 GMT

@Dan_Miller wrote:

I know this question is now over a year old but I have come across it a couple of times while looking into how to do hyperlinks. Just found what your problem would have been related to while reading this:

http://blogs.sas.com/content/sgf/2017/02/20/tips-for-using-the-ods-excel-destination/

'When text wraps, a hard return is added automatically to the cell ' so you either need to set the width of the cell in which you want the hyperlink wide enough that it dosn't need to wrap or use the flow option to stop the automatic wrapping:

ods excel file="c:\temp.xlsx" options(flow="tables");

Hope this is useful to someone...

As I have switched jobs and no longer have my data to test this out, I would like to believe that this is the correct solution, but can't confirm it. Nevertheless, I thank you for the information, I have bookmarked this in case I run into this problem again.