The SAS Output Delivery System and reporting techniques

ODS EXCEL creating hyperlinks improperly

Reply
Trusted Advisor
Posts: 1,294

ODS EXCEL creating hyperlinks improperly

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.

 

Mixed_NoScrub_AA277_WFR_Split.gif

 

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.

 

Mixed_NoScrub_AA277_WFR_Split2.gif

 

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

Grand Advisor
Posts: 9,335

Re: ODS EXCEL creating hyperlinks improperly

Can you use STYLE to get it ?

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

OR

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

Trusted Advisor
Posts: 1,294

Re: ODS EXCEL creating hyperlinks improperly

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.

 

Mixed_NoScrub_AA277_WFR_SplitAnal.gif

 

 

 

 

Senior User
Posts: 1

Re: ODS EXCEL creating hyperlinks improperly

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...

Trusted Advisor
Posts: 1,294

Re: ODS EXCEL creating hyperlinks improperly


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.

Post a Question
Discussion Stats
  • 4 replies
  • 556 views
  • 0 likes
  • 3 in conversation