BookmarkSubscribeRSS Feed
asishgautam
Calcite | Level 5

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?


screenshot.JPG
7 REPLIES 7
Haikuo
Onyx | Level 15

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

Haikuo

asishgautam
Calcite | Level 5

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. 

screenshot2.bmp

Haikuo
Onyx | Level 15

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

asishgautam
Calcite | Level 5

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

data_null__
Jade | Level 19

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;

Cynthia_sas
SAS Super FREQ

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

asishgautam
Calcite | Level 5

Thanks - that clears up a few issues for me.

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
  • 3193 views
  • 3 likes
  • 4 in conversation