Insert a workbook (as icon, not linked to the source file) in a RTF file

[hetuvio]

Hello,

I'm looking for a way to insert an Excel file within an RTF file using SAS 9.1.3.

I would like to insert the workbook as an icon (not linked to the source file).

Thanks so much in advance for your support,

BR,

Violaine

[RW9 · Accepted solution]

Hi,

Well, I don't think it is possible directly.  The reason being is that RTF is a just a markup language - i.e. it adds some styling and layout information to a text file.  The example you provided is actually using Office technology which allows embedding of objects within objects.  You could try to mimic it:

First you would have the two files (i.e. not just one as the file is not "embedded").  In the RTF you add specific RTF code to indicate a hyperlink (help: http://www.lexjansen.com/pharmasug/2006/technicaltechniques/tt21.pdf).  Set the label of the hyperlink to the filename, and add a picture (again RTF code) to the file.  As you can see its a lot of work and doesn't really acheieve anything like the other one.  The only other thing I can think of is to write a small VBA macro in Word itself that opens the RTF file, embeds the additional file, and then saves the final file as .DOCX.  This is however outside the scope of SAS.

[hetuvio]

Hi !

Thanks so much for your answer.

I have a concern... do you think that the proposed way above (hyperlink + label the hyperlink + add a picture) will consider the workbook as attached to the parent RTF file ?

I mean I need to have all my results in one single parent file (.RTF), there won't be any additional document. So, unless I'm wrong, hyperlinks will not work if the source Excel file is not located in the same folder than the main document.

I was wondering if I could use proc report with options such as preimage... or maybe something else.

Thanks in advance for your support,

BR,

Violaine

[hetuvio]

I have investigated the use of VB code.

I have used the macro recorder in a RTF file. And it gave me the following piece of code

    Selection.InlineShapes.AddOLEObject ClassType:="Excel.Sheet.8", FileName:= _

        "C:\Documents and Settings\My Documents\Classeur1.xls", _

        LinkToFile:=False, DisplayAsIcon:=True, IconFileName:= _

        "C:\WINDOWS\Installer\{90120000-0011-0000-0000-0000000FF1CE}\xlicons.exe" _

        , IconIndex:=1, IconLabel:="Classeur1.xls"

By any chance, do you know how I could execute this piece of code when creating the RTF file ? Or should I use DDE (I'm working on SAS 9.1.3)?

Thanks in advance for your help,

BR,

Violaine

[RW9]

Am afraid your hitting the same point and mixing Office with RTF.  RTF is just a plain text file, with some additional tags so a reader can render the output.  Much like HTML.  It doesn't itself contain any other objects, but can link them.  When Word opens an RTF file it uses its own RTF parser/render to take the text file and show it as a Word file.

What you show in that post is VBA code.  This is Visual basic for Applications, it is an Office (and by Office I mean MS Office = Excel, Word, etc. plus the associated components such as VBA and the object model) component.  They are in fact two separate technologies in the same way that HTML is not the same as Javascript, or Flash.

[Cynthia_sas]

HI:

  RW9 is correct. A Microsoft Word document (.DOC or .DOCX) is entirely different from an RTF text file. You can open an RTF file with Notepad and see the markup language tags. You cannot open a .DOCX file with Notepad, you have to use Word.

  ODS RTF creates an RTF file that Microsoft Word knows how to open. An RTF file is NOT a Word doc. A Word doc can have a Word Macro run to manipulate the file *inside* Word. A Word doc can be linked to other Office content (like Excel or PPT). An RTF file is a "plain" file -- it doesn't have all the interactivity and bells and whistles as an Office file. SAS and ODS only know how to create RTF output. The RTF output has far fewer capabilities than a Word document. The snippet of code that you posted that shows adding an OLE Object to a Word document is NOT something that you could make work with SAS. It is entirely an Office process. As suggested, if you know VB, you could write a VB script or Word macro to open the RTF file, after SAS creates the file, then run your script to edit the file and make the link and then you MUST resave the file as .DOCX or else the RTF file will not contain the embedded link. But a VB Script or VBA process would be outside of SAS.

  So, you cannot execute your piece of code when creating the RTF file with SAS -- you would have to use Microsoft technology (VB or VBA) after the RTF file is created to 1) read the RTF file with Word; 2) add the embedded object and 3) resave the file as .DOCX. You cannot do these 3 things with SAS while the RTF file is being created.

Cynthia
    

But, if you take a step back and look at what you CAN do with ODS RTF, this code will put a link on the title (not embed the file), so that if you CTRL+click on the TITLE string, you should be able to open the XLS file from the Berkeley College web site. (If you have an Internet connection).
    

ods rtf file='c:\temp\make_rtf_link.rtf' bodytitle ;

proc print data=sashelp.class;

title link='http://berkeleycollege.edu/browser_check/samples/excel.xls'

           'Look at this XLS file from Berkeley College';

run;

ods rtf close;

[RW9]

Just to note though, for completeness.  DOCX and XLSX are just zip files with subdirectories and XML text files (well apart from VBA at the moment, that is still compiled).  So in theory, if you learnt Open Office format, you could build any type of office document by creating the relevant XML files and then compiling them into a final zip and renaming it to docx.  It is pretty advanced though, you would need to read up on OO format structures, and there isn't, as far as I am aware, currently a good developer environment for this outside office.

[hetuvio]

Ok, so it sounds that there is no easy way to get a workbook attached to an RTF file (I mean without the use of hyperlinks).

Thanks a lot for having provided me with your input on this.

BR,

Violaine