BookmarkSubscribeRSS Feed
ameey
Calcite | Level 5

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
7 REPLIES 7
ameey
Calcite | Level 5

Hi Cynthia

Please reply.

Cynthia_sas
SAS Super FREQ

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
ameey
Calcite | Level 5

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 

mohamed_zaki
Barite | Level 11

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.

ameey
Calcite | Level 5

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

mohamed_zaki
Barite | Level 11

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1776 views
  • 0 likes
  • 4 in conversation