topic Re: Error using DDE to download Excel spreadshet in SAS Programming

Error using DDE to download Excel spreadshet

C20325A — Wed, 25 Oct 2017 09:12:22 GMT

I am trying to use a DDE connection to import an Excel spreadsheet into SAS. I have the following code:

options noxwait noxsync;
%sysexec "start excel";

data _null_;
	rc=sleep(15);
run;


data HouseTables.England (keep=LAD AREA_NAME _&MYEPrev.-_&FPY.);
	informat LAD $9.	AREA_NAME $35.	AREA_NAME1 $35.	AREA_NAME2 $35.	AREA_NAME3 $35.	_1991-_&FPY. comma16.;
	filename Excel dde "Excel|&rootdir.\Input\[Household_Projections.xlsx]406!R6C1:R410C53" notab;
	infile Excel lrecl=10001 dsd missover delimiter='09'x firstobs=1;
	input LAD	AREA_NAME1	AREA_NAME2	AREA_NAME3	_1991-_&FPY.
run;

The script ran perfectly on somebody else's PC. But running it on mine, I get the following error:

ERROR: Failed to connect to Excel|[Hidden Excel file path]\[Household_Projections.xlsx]406!R6C1:R410C53 : The error
       DMLERR_NO_CONV_ESTABLISHED was returned

Can anybody help with this? Excel is definitely up and running after running the first bit of code, but it won't seem to connect.

Re: Error using DDE to download Excel spreadshet

C20325A — Wed, 25 Oct 2017 09:37:20 GMT

Update: this is now solved!

I used the %sysexec command to open the specific Excel i was using, and then I have to Enable Editing on the document.

Re: Error using DDE to download Excel spreadshet

RW9 — Wed, 25 Oct 2017 09:47:58 GMT

Sorry, I don't recognise the error and its not worth my time debugging this. Basically DDE is over 15 years out of support, it is ancient, and in quite a lot of scenarios does not work, or does not have the functionality needed. There are many ways to get data into SAS, for instance you could start with a proc import, this guesses the data though so not ideal (better to save to CSV and write a datastep, far more robust), or there is, if you have > V9.3 libname excel.

So yes, start by trying proc import - you can specify range - this should be near enough and will work on a broader spectrum (unless the machine does not have Office on it, then you need pcfiles).

Re: Error using DDE to download Excel spreadshet

Patrick — Wed, 25 Oct 2017 10:00:16 GMT

@C20325A

DDE is legacy and even still supported you really shouldn't use it anymore.

https://blogs.sas.com/content/sasdummy/2014/10/14/dde-is-doa/

Re: Error using DDE to download Excel spreadshet

ballardw — Wed, 25 Oct 2017 17:02:22 GMT

@C20325A wrote:

Update: this is now solved!

I used the %sysexec command to open the specific Excel i was using, and then I have to Enable Editing on the document.

There are several other programs out there that will interfere with DDE connections to Excel. If the only purpose is to read the file there are many other ways that are more reliable.