topic Re: Starting Excel in Microsoft Integration with SAS

Starting Excel

deleted_user — Mon, 09 Mar 2009 10:12:48 GMT

In the Vyverman paper Using Dynamic Data Exchange to Export Your SAS® Data to MS Excel
— Against All ODS, Part I —

at http://www.sas-consultant.com/professional/SUGI26-Against-All-ODS-1.pdf,

you can find code by Roper on how to start Excel. It looks like this:

options noxsync noxwait;
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;

When I try to run the code I get the message:

ERROR 23-2: Invalid option name excel .

What can be wrong? Of course I have Excel installed.

Anne

Re: Starting Excel

data_null__ — Mon, 09 Mar 2009 11:52:42 GMT

I copied the code you posted and it does start excel on my PC. Excel2003.

Try this...
Determine the "command" that starts excel from the excel short cut on your PC.
[pre]
Right click "properties".
Copy the target field from that dialog.
Open CMD window
Enter START "title" "and paste from target"
[/pre]
Where past from target is what you copied from properties.target.
"title" is a place holder.

If you can get that to work than replace the START command in the ROPER code, with the start command that actually works on your system.

Re: Starting Excel

deleted_user — Mon, 09 Mar 2009 12:59:49 GMT

I tried once more before I got your answer. It worked.

There must have been some unwanted character in my code.

But now I have another question: In the cited paper, on page 5, some formatting is made, like

put '[format.font("Verdana", ... )]';.

How can I set the number of decimal places with similar code?

Re: Starting Excel

data_null__ — Mon, 09 Mar 2009 14:16:03 GMT

I don't use this enough to be much help but I belive this is the function you need.

Equivalent to choosing the Number tab in the Format Cells dialog box, which appears when you choose Cells from the Format menu. Formats numbers, dates, and times in the selected cells, data labels, and axis labels on charts. Use FORMAT.NUMBER to apply built-in formats or to create and apply custom formats.

Syntax

FORMAT.NUMBER(format_text)
FORMAT.NUMBER?(format_text)
Format_text is a format string, such as "#, ##0.00", specifying which format to apply to the selection.