topic Re: msoffice2k_x multiple sheets in ODS and Base Reporting

msoffice2k_x multiple sheets

ja_lew — Tue, 20 Sep 2011 16:22:44 GMT

both of my files create without problems, and the code runs without error. however combining the 2 sheets into 1 workbook i get the following error (admin has us locked down so i cannot open the log file)

here is my code to merge the 2 workbooks:

ods tagsets.msoffice2k_x file = 'ranking.xls'

options( worksheet_source = '2010 Campaign Ranking#2010Ranking.xls,

2011 Campaign Ranking#2011Ranking.xls'

);

ods tagsets.msoffice2k_x close;

edit: i was able to get to the log from RUN.

XML ERROR in Worksheet Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2010Ranking.xls

GROUP: ExcelWorksheet

TAG: Name

VALUE: 2010 Campaign Ranking

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2010Ranking.xls

GROUP: ExcelWorkbook

TAG: WindowHeight

VALUE: 12495

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2010Ranking.xls

GROUP: ExcelWorkbook

TAG: WindowWidth

VALUE: 18900

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2010Ranking.xls

GROUP: ExcelWorkbook

TAG: WindowTopX

VALUE: 60

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2010Ranking.xls

GROUP: ExcelWorkbook

TAG: WindowTopY

VALUE: 45

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2010Ranking.xls

GROUP: ExcelWorkbook

TAG: ProtectStructure

VALUE: False

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2010Ranking.xls

GROUP: ExcelWorkbook

TAG: ProtectWindows

VALUE: False

XML ERROR in Worksheet Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2010Ranking.xls

GROUP: ExcelWorksheet

TAG: Name

VALUE: 2010 Campaign Ranking

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2010Ranking.xls

GROUP: ExcelWorkbook

TAG: WindowHeight

VALUE: 12495

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2010Ranking.xls

GROUP: ExcelWorkbook

TAG: WindowWidth

VALUE: 18900

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2010Ranking.xls

GROUP: ExcelWorkbook

TAG: WindowTopX

VALUE: 60

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2010Ranking.xls

GROUP: ExcelWorkbook

TAG: WindowTopY

VALUE: 45

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2010Ranking.xls

GROUP: ExcelWorkbook

TAG: ProtectStructure

VALUE: False

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2010Ranking.xls

GROUP: ExcelWorkbook

TAG: ProtectWindows

VALUE: False

XML ERROR in Worksheet Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2011Ranking.xls

GROUP: ExcelWorksheet

TAG: Name

VALUE: 2011 Campaign Ranking

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2011Ranking.xls

GROUP: ExcelWorkbook

TAG: WindowHeight

VALUE: 12495

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2011Ranking.xls

GROUP: ExcelWorkbook

TAG: WindowWidth

VALUE: 18900

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2011Ranking.xls

GROUP: ExcelWorkbook

TAG: WindowTopX

VALUE: 60

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2011Ranking.xls

GROUP: ExcelWorkbook

TAG: WindowTopY

VALUE: 45

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2011Ranking.xls

GROUP: ExcelWorkbook

TAG: ProtectStructure

VALUE: False

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2011Ranking.xls

GROUP: ExcelWorkbook

TAG: ProtectWindows

VALUE: False

XML ERROR in Worksheet Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2011Ranking.xls

GROUP: ExcelWorksheet

TAG: Name

VALUE: 2011 Campaign Ranking

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2011Ranking.xls

GROUP: ExcelWorkbook

TAG: WindowHeight

VALUE: 12495

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2011Ranking.xls

GROUP: ExcelWorkbook

TAG: WindowWidth

VALUE: 18900

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2011Ranking.xls

GROUP: ExcelWorkbook

TAG: WindowTopX

VALUE: 60

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2011Ranking.xls

GROUP: ExcelWorkbook

TAG: WindowTopY

VALUE: 45

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2011Ranking.xls

GROUP: ExcelWorkbook

TAG: ProtectStructure

VALUE: False

XML ERROR in Workbook Setting

REASON: Illegal Tag

ACTION: Ignoring Tag

FILE: H:\2011Ranking.xls

GROUP: ExcelWorkbook

TAG: ProtectWindows

VALUE: False

Re: msoffice2k_x multiple sheets

Cynthia_sas — Wed, 28 Sep 2011 18:32:35 GMT

Hi:

If you use TAGSETS.MSOFFICE2K_X, the underlying assumption is that you are linking multiple HTML files together into one workbook. As far as I know, you cannot use TAGSETS.MSOFFICE2K_X to insert sheets into an existing Excel workbook or to mix HTML files with true, binary .XLS files.

Remember that when you use TAGSETS.MSOFFICE2K_X, you are creating pure ASCII text HTML files -- even if you name the files with the .XLS file extension, the internal content of the files is in HTML form, which you can prove to yourself by opening the files with Notepad or any other text editor.

In the example below, I am creating DRESS.HTML and CASUAL.HTML as two separate and distinct HTML files using ODS MSOFFICE2K. Then, I use the ODS MSOFFICE2K_X step to link those two separate files together into the BOTH file, which is an HTML file, but I have named the file as BOTH.XLS for my convenience, so that Windows will launch Excel when I double click on the file name. (If I had named the file with the .HTML extension, then the browser would have launched when I double clicked on the file name.)

I am not exactly sure what your files represent in your invocation example -- whether they are true, binary .XLS files or whether they are HTML files that you created with ODS. In either case, if the example below doesn't help you out, then you might want to open a track with Tech Support.

cynthia

** 1) make dress.html and casual.html;
ods listing close;
title; footnote;
ods msoffice2k file='c:\temp\dress.html' style=analysis;
proc report data=sashelp.shoes nowd
       style(summary)=Header;
    where product contains 'Dress';
    title 'All Dress Shoe Sales';
    column product region sales inventory returns;
    define product / group;
    define region / group;
    rbreak after / summarize;
run;

ods msoffice2k close;

ods msoffice2k file='c:\temp\casual.html' style=analysis;
proc report data=sashelp.shoes nowd
       style(summary)=Header;
    where product contains 'Casual';
    title 'All Casual Shoe Sales';
    column product region sales inventory returns;
    define product / group;
    define region / group;
    rbreak after / summarize;
run;

ods msoffice2k close;

** 2) Now put both HTML files into an HTML file that Excel;
**    will treat like multiple sheets in one workbook.;
**    note that you cannot use this technique to "add" sheets to an existing workbook;
**    Also, you cannot mix HTML files with true, binary .XLS files using;
**    this destination.;
ods listing close;
ods tagsets.msoffice2k_x file="c:\temp\both.xls" style=analysis
      options( worksheet_source="Dress#c:\temp\dress.html,
                                 Casual#c:\temp\casual.html"
               );

** this data step program is only to have something that makes;
** an icon in the Results Pane so you can click on the Excel icon;
** You will not see the text string unless the LISTING destination is open;
data _null_;
    file print;
    put "test";
run;

ods tagsets.msoffice2k_x close;

Re: msoffice2k_x multiple sheets

cnwentsa — Tue, 25 Aug 2015 15:09:52 GMT

Just to say thanks Cynthia.

This has been very helpful for me too, now, struggling to create a native excel file from the xml file crated by msoffice2k_x without opening the file as requested by the DDE.

Regards.

Re: msoffice2k_x multiple sheets

Cynthia_sas — Tue, 25 Aug 2015 22:25:41 GMT

Hi:

MSOFFICE2K_X creates a hybrid HTML/XML file that Excel knows how to open and render. So when you say that you are trying to create a "native" Excel file from MSOFFICE2K_X, I fear that you are on a snark hunt. It would be better to try to use the new ODS EXCEL destination to create an XLSX file instead of using MSOFFICE2K_X file to create the older type of file.

Here's a good blog post about ODS EXCEL: http://blogs.sas.com/content/sasdummy/2014/08/29/experimenting-with-ods-excel-to-create-spreadsheets-from-sas/

cynthia

Re: msoffice2k_x multiple sheets

cnwentsa — Wed, 26 Aug 2015 13:26:32 GMT

Hi Cynthia.

with ods msoffice2k_x, I created myfile.xls which actually is an xml file

ods tagsets.msoffice2k_x file="myfile.xls"

options(sheet_name="sheetname");

proc print data=my_dataset;

run;

ods tagsets.msoffice2k_x close;

Then I convert this file to an excel native file using these commands:

options noxwait noxsync;

filename cmds dde 'excel|system';

data _null_;

file cmds;

x=sleep(5);

put "[open(""myfile.xls"")]";

put '[ERROR("false")]';

put"[save.as(""myfile2.xls"",1)]";

x=sleep(2);

put '[close("true")]';

run;

but going this way to convert the xml file into an excel native file , I need to have excel open yet. I wonder if there is a way to convert my file without getting it open. Please note that I am running SAS 9.1.3

Thanks in advance.

Re: msoffice2k_x multiple sheets

RW9 — Wed, 26 Aug 2015 13:41:49 GMT

First question would be, why do you need an Open Office document - this is the format Office uses for its files? It makes no difference.

I would also suggest to not use DDE, this is old technology and is limited.

What are you trying to achieve by using Excel? I have not found any situation where Excel is the tool for the job. Is it a data transfer, or just for review?

As Cynthia has stated, ods excel is currently the only way to create Open Office documents, alternatively you could learn how the files are structured - rename an XLSX file to have the extension .ZIP, then open it and you will see a directory structure with XML snippets in various files etc. Its not and easy format to work with unlike the Open Document format used by LibreOffice or similar. I would clarify first what you want to do as again, Excel is really not the tool for the job.