topic Re: excel xp tagset and file extension in ODS and Base Reporting

excel xp tagset and file extension

asishgautam — Mon, 23 Apr 2012 16:40:49 GMT

Hi all,

I have a sas program that creates a bunch of files in excel using the excel xp tagset. as best as i can tell, everything works just fine. When I specify the file location in the "ODS sandwich", I use the .xls extension. After my program runs and creates all the files, when I open the excel file, I get this message - see attachment. Essentially, the message is telling me that it does not recognize the file as an excel file - this is causing some issues for people that need to review the content of the excel files. What do I need to do so that this message does not show up?

Re: excel xp tagset and file extension

Haikuo — Mon, 23 Apr 2012 16:44:47 GMT

ODS exclexp tagset does NOT produce genuine excel files, use xml to replace xls will avoid this kind of warning.

Haikuo

Re: excel xp tagset and file extension

asishgautam — Mon, 23 Apr 2012 17:07:55 GMT

Thanks. That seems to have fixed the problem I was having. However, now I get a popup (see below) asking me if I want to open/save the file. This may or may not cause more questions to be asked.

Re: excel xp tagset and file extension

Haikuo — Mon, 23 Apr 2012 17:29:53 GMT

I am using Office 2007 on XP box, and I have never experienced this pop up window when try to open xml files. I am not sure if this has something to do with your excel settings. Hope someone will come up some solutions for you.

Haikuo

Re: excel xp tagset and file extension

asishgautam — Mon, 23 Apr 2012 17:32:39 GMT

I have the same deal - office 2k7 on xp.

Re: excel xp tagset and file extension

data_null__ — Mon, 23 Apr 2012 18:13:12 GMT

As you create the XLM files use Office automation to "convert" them to true EXCEL format. Then the consumers shouldn't have any conversion issues messages and the rest.

data _null_ / pgm=work.xml2xlsx;

/* gather info */

length xml xlsx $256 script filevar command $256;

xml = pathname('FT67F001');

xlsx = xml;

substr(xlsx,find(xml,'.',-length(xml)))='.xlsx';

put xml= xlsx=;

script = catx('\',pathname('WORK'),'XML2xlsx.vbs');

filevar = script;

/* write the script */

file dummy1 filevar=filevar;

put 'Const ' xml=$quote256.;

put 'Const ' xlsx=$quote256.;

*put 'Const xlCSV = 6';

put 'Const xlWorkbookNormal = 51';

put 'Set objExcel = CreateObject("Excel.Application")';

*put 'Set objExcel = GetObject(, "Excel.Application")';

put 'With objExcel';

put +3 '.Visible = False';

put +3 '.DisplayAlerts = False';

put +3 'Set objWorkbook = .Workbooks.Open(xml)';

put +3 'objWorkBook.SaveAs xlsx,xlWorkbookNormal';

put +3 '.Application.Quit';

put +3 'End With';

/* close the script file by opening another, not used */

filevar = catx('\',pathname('WORK'),'DUMMY.vbs');

file dummy1 filevar=filevar;

/* look at the script, not necessary but may be useful */

infile dummy2 filevar=script end=eof;

do _n_ = 1 by 1 while(not eof);

input;

putlog _n_ 3. +2 _infile_;

end;

/* call the script */

command = catx(' ','cscript',quote(strip(script)),'//nologo');

infile dummy3 pipe filevar=command end=eof truncover;

do while(not eof);

input;

putlog _infile_;

end;

stop;

run;

filename FT67F001 'testXP.xml';

ods results off;

ods tagsets.excelxp file=FT67F001;

proc print data=sashelp.class;

run;

ods tagsets.excelxp close;

data pgm=work.xml2xlsx;

run;

Re: excel xp tagset and file extension

Cynthia_sas — Tue, 24 Apr 2012 04:17:44 GMT

Hi:

A few comments about the comments so far:

1a) popup window issue 1: Excel started issuing this somewhat snarky message in Office 2007, That message is described in this Tech Support note: http://support.sas.com/kb/35/581.html

1b) popup window issue2: this File/Open/Save window is described here for RTF -- but the same general information also applies to files that you might want to open with Excel when you use ODS HTML or ODS TAGSETS.EXCELXP:

http://support.sas.com/kb/9/627.html

2) using the .xls file extension issue: When you use ODS TAGSETS.EXCELXP, you are not creating a "true, binary" Excel file. You are creating an Office 2003 Spreadsheet Markup Language XML file. The correct file extension for this type of file is .XML -- however -- generally an .XML file opens with a browser or XML viewer. so using .XLS as the file extension in earlier versions of SAS was necessary in order to "fool" the Windows Registry and SAS into launching Excel when you double-clicked on the icon in the Results Viewer. In newer versions of SAS (9.2 and 9.3), you can use .XML as the file extension and Excel should launch because the developers build some intelligence into the Results Viewer. If you use .XML as the file extension, then your users should not encounter any popup windows from Microsoft about the wrong extension. You might still get the file download message--but that is something you might not be able to change especially if you cannot change the SAS registry.

cynthia

Re: excel xp tagset and file extension

asishgautam — Tue, 24 Apr 2012 14:51:39 GMT

Thanks - that clears up a few issues for me.