Date:         Mon, 8 Aug 2011 09:47:28 -0500
Reply-To:     Sudo Ku <crafty876@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sudo Ku <crafty876@HOTMAIL.COM>
Subject:      Re: File extension problem opening ExcelXP output
In-Reply-To:  <B90B817A9BBB904AAAD7EC321C01596745AB61@USFCH-MAIL1.lewin.com>
Content-Type: text/plain; charset="iso-2022-jp"

Hi Richard:

Rather than using the VB script TED suggests why not use DDE and let excel do the work for you. I have verified the following will work on my session usning SAS 9.2 and excel 2003. My apologies for the mess that Hotmail is going to do the formatting

%let dir = %sysfunc(pathname(WORK));
/* The file you are trying to open, '...', is in a different formatthan specified by the file extension.Verify the file is not corrupted and is from a trusted sourcebefore opening the file.Do you want to open the file now ? Yes No Help*/
ods tagsets.excelxp file="&dir.\report-1.xls";
proc print data=sashelp.class;
run;
ods tagsets.excelxp close;
/* Excel cannot open the file '...' because the file format or fileextension is not valid.
Verify that the file has not been corrupted and that the fileextension matches the format of the
file.Ok*/
ods tagsets.excelxp file="&dir.\report-2.xlsx";
proc print data=sashelp.class; run;
ods tagsets.excelxp close;
/* Opens right up ! */
ods tagsets.excelxp file="&dir.\report-3.xml";
proc print data=sashelp.class;
run;
ods tagsets.excelxp close;
 $B!! (B
***DDE STEPS COme Here***;
***Open Excel using the ROPER (2000) Method***;
options noxwait noxsync;
filename sas2xl dde 'excel|system';
data _null_;
length fid rc start stop time 8;
fid=fopen ('sas2xl','s');
if (fid le 0) then do;
rc=system('start excel');
start=datetime();
stop=start+10;
do while (fid le 0);
fid=fopen ('sas2xl' , 's');
time=datetime();
if (time ge stop) then fid=1;
end;
end;
rc=fclose(fid);
run;
 $B!! (B
***Open up the XML encoded .xls report***;
data _null_;
file sas2xl;
put %unquote(%str(%')[open("&dir.\report-1.xls")]%STR(%'));
run;
 $B!! (B
*** Save As the as a true XLS file***;
data _null_;
file sas2xl;
put %unquote(%str(%')[Save.as("&dir.\report-1a.xls",1)]%STR(%'));
run;
*** Close The file and quit excel***;
Data _null_;
file sas2xl;
put '[File.close(false)]';
put "[quit()]";
run;



> Date: Wed, 3 Aug 2011 14:40:46 -0400
> From: ted.kirby@LEWIN.COM
> Subject: Re: File extension problem opening ExcelXP output
> To: SAS-L@LISTSERV.UGA.EDU
>
> On the first case (must select "yes" to open files): It appears that
> Office 2007 has become "smart" in that it knows when something other
> than Excel(r) (e.g., xml) has been used to create a file with an ".xls"
> extension and thus, questions whether you really want to open this file.
> As far as I know, SAS cannot overcome this obstacle unless you do
> something such as using SAS to create Visual Basic(r) script to open
> Excel(r) then open the xml file and save it as an xls file.
>
> You may need to find the file-source-checking option in MS Office and
> turn it off if you do not want to click on "yes" to open the file.
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
> Richard DeVenezia
> Sent: Wednesday, August 03, 2011 1:46 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: File extension problem opening ExcelXP output
>
> I name my ExcelXP output files (sent to a coworker) with a .xml file
> extension because that causes Windows to open them automatically into
> Excel 2007 on my system.
> Coworker said they didn't want .xml alone for Excel files ( I can't
> blame them )
>
> On my system
> If .xls is used a dialog pops up and I have select Yes to open the file.
> If .xlsx is used a dialog pops up and Excel will not open it !
>
> SAS Tools/Options/Preferences/Results has "View results" and
> "Preferred web browser" selected.
> Preferred browser is Internet Explorer 7.
>
> Any ideas what might be causing the dialogs ? Any way to bypass the
> prompt dialog ?
>
> Sample code:
> ---------------------------------
> %let dir = %sysfunc(pathname(WORK));
>
> /* The file you are trying to open, '...', is in a different format
> than specified by the file extension.
> Verify the file is not corrupted and is from a trusted source
> before opening the file.
> Do you want to open the file now ? Yes No Help
> */
> ods tagsets.excelxp file="&dir.\report-1.xls";
> proc print data=sashelp.class; run;
> ods tagsets.excelxp close;
>
> /* Excel cannot open the file '...' because the file format or file
> extension is not valid.
> Verify that the file has not been corrupted and that the file
> extension matches the format of the file.
> Ok
> */
> ods tagsets.excelxp file="&dir.\report-2.xlsx";
> proc print data=sashelp.class; run;
> ods tagsets.excelxp close;
>
> /* Opens right up ! */
> ods tagsets.excelxp file="&dir.\report-3.xml";
> proc print data=sashelp.class; run;
> ods tagsets.excelxp close;
> ---------------------------------
>
> Thanks,
> Richard DeVenezia
> ************* IMPORTANT - PLEASE READ ********************
>
> This e-mail, including attachments, may include confidential and/or proprietary information,
> and may be used only by the person or entity to which it is addressed. If the reader of this
> e-mail is not the intended recipient or his or her authorized agent, the reader is hereby
> notified that any dissemination, distribution or copying of this e-mail is prohibited. If you
> have received this e-mail in error, please notify the sender by replying to this message
> and delete this e-mail immediately.
>