The SAS Output Delivery System and reporting techniques

help to rename the sheet name using ods msoffice2k_x only

Reply
Occasional Contributor
Posts: 19

help to rename the sheet name using ods msoffice2k_x only

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


pop msg error when i open my excel sheet.pngods MSoffice2k_x sheet rename.png
Occasional Contributor
Posts: 19

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

Hi Cynthia

Please reply.

SAS Super FREQ
Posts: 8,820

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

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


Excel_not_blank_sheet_name.png
Occasional Contributor
Posts: 19

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

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 

Super Contributor
Posts: 490

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

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.

Occasional Contributor
Posts: 19

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

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

Super Contributor
Posts: 490

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

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.

Super User
Super User
Posts: 7,720

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

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

Ask a Question
Discussion stats
  • 7 replies
  • 809 views
  • 0 likes
  • 4 in conversation