topic Re: Remove sheet name from ods tagsets.msoffice2k_x in ODS and Base Reporting

Remove sheet name from ods tagsets.msoffice2k_x

ameey — Fri, 17 Oct 2014 06:48:38 GMT

Hi Cynthia/ All

This is common question whenever we use ods tagsets.Msoffice2k_x to generate excel reports that time by default sheet name of excel worksheet retains the same name of excel sheet name that i do not want in tagsets.msoffice2k_x. Please tell me if any one know how can I bring the sheet name by blank name ( no sheet name) or how can i rename the sheet name in ods tagsets.Msoffice2k_x. only.

I am giving a test data set and screen shot of error

Re: Remove sheet name from ods tagsets.msoffice2k_x

RW9 — Fri, 17 Oct 2014 08:35:42 GMT

Hi,

Well, a few things. There is no msoffice2k_x tagset, is this something you have inherited from msoffice2k?

Secondly, a tab name in Excel cannot be missing, Excel does not allow this, so the answer is no to your question.

Why would you want a missing name spreadsheet? It doesn't make sense to have a tab with no name as there is no reference point. I would recommend changing to tagsets.excelxp as the following will use the space:

ods listing close;

ODS tagsets.excelxp FILE="s:\temp\rob\test.xls" options(sheet_name=" ") ;

proc print data=sashelp.class;

run;

ods _all_ close;

Will give you a tab with an empty space as the tab name - however the question still remains why you would want that.

Re: Remove sheet name from ods tagsets.msoffice2k_x

ameey — Fri, 17 Oct 2014 09:04:00 GMT

Thanks for your quick response.

Yes you r true tagsets,Msoffice2k_x is inherited from tagsets,Msoffice2k. But i can not take any other other format ( eg. excelXp, msoffice2k). I have only option to use msoffice2k_x because this my client need and i want either rename my sheet name or it will come as "sheet 1" .. Please share your knowledge. if any one can help me out.

Regards

Re: Remove sheet name from ods tagsets.msoffice2k_x

RW9 — Fri, 17 Oct 2014 09:47:15 GMT

I am afraid you will not find a simple methodology for that. The msoffice2k tagset is used for generating HTML output. The HTML output does not have any "sheet" information associated with it. When you open that file in Excel, Excel is reading the HTML output and interpreting that. Therefore it is Excel defaulting the tab name in as no information is provided in the HTML to the contrary. So fit the "requirement" to the technology, my opinion is if a request comes in to use this tagset and have Excel specific information its not going to work.

Re: Remove sheet name from ods tagsets.msoffice2k_x

ameey — Fri, 17 Oct 2014 10:48:41 GMT

Dear RW9

Sorry to say but this possible.

I am sharing one link of sas support as well screen shot where u can check that this is possible to rename yr sheet name using ods tagsets.msoffice2k_x and this link is by sas support only.

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

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 open this file it is giving a one pop message and due to this I am unable to check my output.So please anyone help me out because here I am stuck.

Re: Remove sheet name from ods tagsets.msoffice2k_x

RW9 — Fri, 17 Oct 2014 12:08:39 GMT

So its a pop up box that's bothering you? Is it something along the lines of "The file format and extension don't match" - this is fine and shouldn't affect anything, or does it say file is corrupted? Does it work correctly if you put some text in that sheet_name option, e.g. put "XYXYZ" in and see if it generates.