Open Excel Template without DDE

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Open Excel Template without DDE

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



Accepted Solutions
Solution
‎05-30-2013 12:25 PM
Super Contributor
Posts: 253

Re: Open Excel Template without DDE

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.

View solution in original post


All Replies
Solution
‎05-30-2013 12:25 PM
Super Contributor
Posts: 253

Re: Open Excel Template without DDE

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.

New Contributor
Posts: 2

Re: Open Excel Template without DDE

Posted in reply to snoopy369

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

Kirk

Super Contributor
Posts: 644

Re: Open Excel Template without DDE

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 485 views
  • 0 likes
  • 3 in conversation