I am trying to export a logo to the excel sheet using the msoffice2k ODS destination and I use the following code to do the export.The code exports the image but it overlaps with a couple of rows of data.
Are thee any options in the msoffice2k tagset where i can specify it to start the data after the image?
I tried looking into the doc="help" option but couldnt figure out anything straightforward that would fix things.
When you use TAGSETS.MSOFFICE2K, you are not creating a true, binary Excel (.XLS) file. You are actually creating an HTML file that Excel knows how to open and render. Naming the file with the ".XLS" extension only "fools" the Windows registry into launching Excel when you double click on the file name.
So, for example, if you open G:\worksheet_msoffice2k_1.xls with Notepad, or any Text Editor, you should see HTML tags in the file. And if you search for the name of your image file, you should soon find an <IMG> tag that pointed to the GIF file.
Personally, I am never happy with the look of logos when I have Excel open and render an HTML file. Excel seems to want to "float" the image over the table. This never looks the way I want. The behavior you observe seems to be inherent in how Excel treats HTML <IMG> tags.
You might want to work with Tech Support to see whether they have any workarounds for this behavior.
Thanks for the reply.
Is there any other ODS destinations that would support exporting images perfectly to excel other than msoffice2k?
The problem with using other Html ODS tagset destinations they donot create a true or pesudo ".xls" sheet as an output and we at the site require that the output file be truly .xls and not .html or .xml
So I chose the msoffice2k ODS tagset.
Can anyone let me know if there is any other way of exporting a logo onto excel and creating a true or pseudo excel (.xls) sheet?
As cool as the new TAGSETS.MSOFFICE2K_X is, it is still producing HTML -- so if you follow the instructions and download the template code and run the template code, if you look down inside the file that's created, it contains:
<meta name="Generator" content="SAS Software Version 9.2, see www.sas.com">
<meta http-equiv="Content-type" content="text/html; charset=windows-1252">
....HTML tags, even when the file extension is .XLS -- so if the desire is to have a "true, binary" .XLS proprietary file, this tagset still creates HTML.
I have had folks write their output to a directory location and then write a VB script to go out and open the HTML files in that directory with Excel and explicitly resave them as Excel proprietary format files (either XLS or XLSX).
The only mechanisms I know of in SAS to create proprietary Excel files from SAS data are to:
1) use PROC EXPORT (with SAS/ACCESS to PC File Formats)
2) use the SAS Excel LIBNAME Engine (needs SAS/Access to PC File Formats)
3) use the SAS Add-in to Microsoft Office (requires the SAS Platform for Business Analytics)
Otherwise, when you use ODS MSOFFICE2K or ODS TAGSETS.MSOFFICE2K_X -- you are still creating Microsoft HTML compliant files that Excel knows how to open and render. When you use ODS TAGSETS.EXCELXP, you are creating Microsoft XML Spreadsheet 2003 compliant files that Excel knows how to open and render.
Thanks cynthia and twocanbazza for the replies.
I have created xml files with excelXP that were directly opened with excel but then they included the requirement that the excel sheet should contain the logo in it.
So had to switch to msoffice2k from excel XP.
We donot have SAS Access to PC files installed on site and i don't see it to be installed in the near future.
I have found another ODS tagset "The tableeditor tagset ".
It says that I can apply the logos in the documentation
From what was said by the OP (underline mine):
"we at the site require that the output file be truly .xls and not .html or .xml
So I chose the msoffice2k ODS tagset."
The OP chose MSOFFICE2K without understanding that it was HTML "under the covers" -- so if HTML turns out to be OK, then, you're right, the new MSOFFICE2K_X tagset template may offer the ability to insert a "non-floating" logo into the HTML file. But it will still be HTML output from either MSOFFICE2K or TAGSETS.MSOFFICE2K_X.
If the client is not willing to have an HTML file instead of proprietary XLS, then other solutions must be found (OLE, ODBC, DDE, populate an XLT with a CSV file, etc, etc.)
As for the other methods -- OLE-DB, ODBC, DDE, creating Microsoft XLT files -- those are all Microsoft topics. To the extent that you can use OLE-DB, ODBC and/or DDE with SAS -- you are basically sending Microsoft commands to Excel using a "conversation" between two applications. If you are using SAS in a client/server scenario -- such as EG on Windows and SAS on Unix, then you may not be able to use DDE, etc, because I believe both apps have to be talking to each other on the same system for the "conversation" to take place.
There are some user group papers on DDE, ODBC, OLE-DB, etc -- probably more DDE examples than anything else out there.
(If you do a Google search on:
Koen Vyverman SAS DDE
Leroy Bessler SAS DDE
you should find some good papers.)
It looks to me like TAGSETS.MSOFFICE2K_X will allow you to load an Excel macro when the file is opened or closed -- this may be another alternative -- if your clients allow the use of Excel macros -- it might be a simple Excel macro as described here: http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/index.html#Macro (of course that means the Excel macro would also have to be written and resident on the user's system).
I don't write VB Script code. But I have had students tell me that a quick way to get "true" Excel files is to write a VB Script to go out, open the HTML or XML file and then do a SAVE AS--> XLS (or whatever Excel version you want) -- before the file was made available for general consumption.
I'm not really sure. The folks who have explained it to me were writing their ODS output to a shared Windows directory, like a Q: drive and then running the VB Script on everything in that directory by opening the file with Excel and doing a file Save AS --> XLS -- so I would imagine that the key is that the VB Script and Excel have to be somewhere in the same vicinity or the files to be saved have to be accessible to Excel, whereEVER it is. Nobody said anything about the mechanics fo the VB Script and Excel, server-wise.