Open Excel Template without DDE

[KirkH]

Good morning,

At a prior company I was able to open an Excel template by using DDE.  Once the file was open data could be output the appropriate tab and cell location.

Today I have discovered that my DDE code does not work.  Something to the effect of the server is not permissioned for Excel or Access.  SAS is located on a non-Windows server.

My question is this.  Is there a method to open my existing Excel template without using DDE?

Sample Code:

* INITIATE EXCEL *;
options noxwait noxsync xmin;
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;

* Issues occurring with timing, Implementing sleep *;
data _null_;
  slept = sleep(5);
run;

Thank you in advance for your help. It is appreciated.

Kirk

[snoopy369 · Accepted solution]

You won't be able to open it in quite the same way without DDE (and of course if you are on a non-Windows server, DDE is out of the question).

Do you have a PC Files Server with your current installation?  That would be used to do a PROC IMPORT with the EXCELCS DBMS.  If you do, then you can do what I do for some of my projects.  That is to import the template, create a matchkey for the rows in the template (some unique identifier for that row that identifies which row in your data you want to populate it from), merge the data in via that matchkey, and then PROC EXPORT it back out.

If you don't have a PC Files Server, then you may need to use a different method for importing/exporting, such as a CSV.  You also may be able to use XLS or XLSX DBMS (the latter requires 9.3, I believe).  See SAS/ACCESS(R) 9.3 Interface to PC Files: Reference for more information.

[KirkH]

Thank you for for the reply. It confirms what I had orginally thought.

Kirk

[RichardinOz]

You can achieve something like the previous functionality by creating an extra hidden worksheet in the Excel workbook and then using Excel cell references within your template to replicate the data in the desired positions.  So instead of writing directly to the cells using DDE you will write to an intermediate position using any of the standard ways of writing to Excel, and use Excel itself to update the template.

Richard