topic Re: Hyperlink in ODS Excel was truncated in ODS and Base Reporting

Hyperlink in ODS Excel was truncated

jingliu178 — Sat, 30 Mar 2024 20:06:18 GMT

data class; set sashelp.class; Display_Link='=HYPERLINK("https://communities.sas.com/","SAS Community")'; run; ods excel file="/usr/apps/sasdata/CAO/CAOSec/local_access/CAO_DropBox/Kiran/test/hyperlink.xlsx" style=statistical options( suppress_bylines='yes' sheet_interval='none' ); proc print data=class; run; ods excel close;

Re: Hyperlink in ODS Excel was truncated

jingliu178 — Sat, 30 Mar 2024 20:07:43 GMT

the code below works,
data class;
set sashelp.class;
Display_Link='=HYPERLINK("https://communities.sas.com/","SAS Community")';
run;

ods excel file="/usr/apps/sasdata/CAO/CAOSec/local_access/CAO_DropBox/Kiran/test/hyperlink.xlsx" style=statistical
options( suppress_bylines='yes' sheet_interval='none' );
proc print data=class style(data)={width=10};
run;

ods excel close;

after adding the width, the hyperlink was truncated

Re: Hyperlink in ODS Excel was truncated

ballardw — Sat, 30 Mar 2024 22:29:50 GMT

Define exactly what you mean by "was truncated"?

What you have created is supposed to show "SAS Community" in the spread sheet and link.

By any chance is it that the column width in your Excel file just isn't wide enough to show the full text of "SAS Community"?

I run your code and when writing to my local file path there is no problem. So I can't replicate any truncation to see what it may be.

And what does your log show?

Re: Hyperlink in ODS Excel was truncated

jingliu178 — Tue, 02 Apr 2024 20:25:14 GMT

I am confused now, i ran the same code on a different computer, seems works fine.

data link;
name2='=Hyperlink("#''AA''!A6","Next >>")';
run;
ods excel file="hyperlinks.xlsx"
options(flow='data,table' autofilter='yes' embedded_titles='yes'
frozen_headers='yes' );

ods excel options(sheet_interval="none" sheet_name="sheet" );
proc report noheader data=link out=b style(header)={verticalalign=middle} style(column)={verticalalign=top just=c asis=on BACKGROUNDCOLOR=white color=blue font=("Arial",8pt,bold)};
columns name2 ;
define name2/format =$200. style(column)={ width=1in} ;
run;
ods excel close;

but on the computer i met the issue, it show me the following error, when i click the white cell to see the value, it is empty, which should be '=Hyperlink("#''AA''!A6","Next >>")' if executed correctly.

but if i remove "style(column)={ width=1in} ;" in proc report, the result is good. just the column width is wider than expected.

Re: Hyperlink in ODS Excel was truncated

ballardw — Tue, 02 Apr 2024 21:02:25 GMT

Mysteries of why the "same" (version and subversion differences unknown) behaves differently for Microsoft products is a fact of life.

Once upon a time I worked in shop that was running Office suite on Windows 95, 98 and another I don't remember. We had an application involved for converting Word documents to the file format a production print company wanted.

Word documents with the same font going through the converter had interesting problems such a bullet lists appearing as bullets (the actual dot version) , right arrows or pointing hands depending on whose computer ran the conversion. Out of 15 computers in the office only one would render the document correctly.

Notice that you have a note about correcting XML. XML files are basically text. You can change the XLSX extension on a file to ZIP (yes Windows will complain). Then access the XML pages in the ZIP file. You might see the problem text result that way IF you don't let Excel try to fix the file.