BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
C20325A
Calcite | Level 5

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
C20325A
Calcite | Level 5

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.

View solution in original post

4 REPLIES 4
C20325A
Calcite | Level 5

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.

Patrick
Opal | Level 21

@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/

 

 

ballardw
Super User

@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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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). 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1836 views
  • 4 likes
  • 4 in conversation