topic Re: help to rename the sheet name using ods msoffice2k_x only in ODS and Base Reporting

help to rename the sheet name using ods msoffice2k_x only

ameey — Fri, 17 Oct 2014 10:58:14 GMT

Dear Cynthia / All

I am sharing one link of sas support as well as screen shot where u can check we can rename the sheet name using ods TAGSETS.MSOFFCE2K_X but still I am facing one problem. when I follow this code and generate my excel sheet using ods tagsets.MSOFFICE2K_X and once the file has been generated and I go to open this file it is giving a one pop message and due to this I am unable to check my output. I am sharing screen shot this pop message , please check and help me out why this is happening because I am stuck here.

Base SAS: The MSOffice2K_x Tagset Adds Options to the MSOffice2K Tagset

Re: help to rename the sheet name using ods msoffice2k_x only

ameey — Mon, 20 Oct 2014 05:31:51 GMT

Hi Cynthia

Please reply.

Re: help to rename the sheet name using ods msoffice2k_x only

mohamed_zaki — Mon, 20 Oct 2014 06:15:36 GMT

You should follow any of the options mentioned in:

Usage Note 32394: Installing and Storing Updated Tagsets for ODS MARKUP

For example copy the code msoffice2k_x.sas and run it firstly, before run the example you mentioned. After that run the example code and you will be able to open the output directly.

Re: help to rename the sheet name using ods msoffice2k_x only

ameey — Mon, 20 Oct 2014 07:08:05 GMT

HI Mohamed

This additional code msoffice2k_x has been run successfully but till as per code sheet name is not renaming.Still this sas code is not giving me desired output.

Re: help to rename the sheet name using ods msoffice2k_x only

mohamed_zaki — Mon, 20 Oct 2014 07:57:49 GMT

Hi ameey,

Please be specific, as i understand you have tow problem mentioned:

1. The pop message

-->If you run the msoffice2k_x.sas succesfully when starting your SAS session, then creating your EXCEL sheet. You will not face the pop message that bothering you.

2. The rename of the sheet other than the default in MS Excel

-->You can rename the sheet by setting the sheet_name options as you want, but you can not set it to null "" because MS Excel not allowing that and if you did so it will be set to the default by MS Excel when you open it as "sheet1".

So for now, kindly do the following :

3.1. Open new SAS session.

3.2. Run the msoffice2k_x.sas

3.3. Run the following example code:

ods tagsets.msoffice2k_x file="c:\temp\worksheetoption.xls" style=normal

options( zoom="70"

fittopage="yes"

sheet_name="Ameey Sheet"

tabcolor="yellow"

pagebreaks="no"

embedded_titles="no"

embedded_footnotes="no" );

proc print data=sashelp.prdsale(obs=50);

run;

ods tagsets.msoffice2k_x close;

3.4. after that open the excel and you will find the sheet name as "Ameey Sheet".

Please reply by what you find.

Re: help to rename the sheet name using ods msoffice2k_x only

RW9 — Mon, 20 Oct 2014 08:38:03 GMT

Hi,

Yes, we were talking about this at this thread: https://communities.sas.com/thread/62123

Null sheet names are not recommended. The msoffice2k tagset is rather old, I would still recommend excelxp tagset as its newer and has far more options (plus its better supported in Excel), and you can still arrive at the same output as from the other tagset (unless of course you really want HTML output).

Re: help to rename the sheet name using ods msoffice2k_x only

Cynthia_sas — Tue, 21 Oct 2014 02:30:02 GMT

Hi:

I am teaching this week and not able to watch the threads as closely. It looks like you got some responses.

My only comment is to take SAS out of the equation: does Excel allow you to even have a "null" sheet name in Excel? I thought you had to have something as the sheet name. What does a "null" sheet name mean to you? When I try to leave a sheet name "blank" or "null", Excel gives me an error. How do you get around this error when SAS is not in the picture? SAS has to play by Excel's rules. My recommendation is that you open a track with Tech Support and define for them what a "null" sheet name is and then ask them if it is even possible.

cynthia

Re: help to rename the sheet name using ods msoffice2k_x only

ameey — Tue, 21 Oct 2014 09:10:11 GMT

Hi cynthia

I applied a same code using ODS TAGSETS.MSOFFICE2K_X which is mentioned in sas support link but still sheet name is not changing. I am not understanding if this code is mentioned on sas support link then why it doest not work and unable to renaming excel report sheet name

(Actually I have more then 50 reports where I have to rename excel sheet name by sas code only because by default it is not taking more then 32 character name and this is mandatory for me to use ODS TAGSETS.MSOFFICE2K_X only..please if possible then help me or guide me how can I do it.

Base SAS: The MSOffice2K_x Tagset Adds Options to the MSOffice2K Tagset