topic Re: Exporting password protected Excel files using SAS in Microsoft Integration with SAS

Exporting password protected Excel files using SAS

GCARTE2 — Sat, 29 Mar 2014 13:50:21 GMT

I'm somewhat of a beginner to SAS and trying to export a password protected Excel file to a location on my network using SAS. I've tried to reference the information in the SAS knowledge base under Sample 31328: Creating password-protected Excel files with SAS® software but I keep getting various errors including "ERROR: Insufficient authorization to access DDE". The errors seem to occur for the steps in the process that are supposed to open Excel.

Is there a way to export a password protected Excel file from SAS without having to have the workbook opened? Any help would be greatly appreciated. Thanks.

Re: Exporting password protected Excel files using SAS

esjackso — Mon, 31 Mar 2014 14:52:14 GMT

What version of SAS are you using and on what platform (windows, server, unix etc...)?

One thing to remember is that excel has to be installed on the same machine as where SAS is running for DDE to work.

Eric

Re: Exporting password protected Excel files using SAS

GCARTE2 — Mon, 31 Mar 2014 15:02:50 GMT

Eric,

I'm using SAS Enterprise Guide 5.1 on a Windows 7 workstation and Excel is installed locally on this machine though I cant find the Excel.exe file (I believe IT may have it hidden).

Re: Exporting password protected Excel files using SAS

esjackso — Mon, 31 Mar 2014 15:13:46 GMT

I think your problem is Enterprise Guide had deprecated the use of DDE. Here is a exert from a Chris Hemedinger paper (2008) [http://www.nesug.org/proceedings/nesug08/ff/ff16.pdf]

DDE IS DOA

DDE, or Dynamic Data Exchange, is a 20-year-old protocol that Microsoft Windows applications can use to send

messages and commands to each other. The SAS programming language includes a FILENAME statement access

method for DDE to facilitate conversations between SAS for Microsoft Windows and other applications. For years,

SAS programmers have used DDE to read and write data programmatically in Microsoft Excel worksheets. When

the SAS program runs, it issues commands to start a Microsoft Excel process and establish a communication link,

open workbook files, and access data in particular worksheet cells. It's interesting to watch such programs in action

because Microsoft Excel windows pop up and values appear in cells as if they were typed in by an invisible hand.

DDE technology works only under certain conditions, and these conditions are often not met when using SAS

Enterprise Guide. One condition is two processes that are communicating via DDE must be running on the same

machine. In a distributed environment where SAS is running on a remote server, the version of Microsoft Excel on

your local PC is inaccessible to your SAS program. Remember, the DDE link is between Microsoft Excel and SAS,

not SAS Enterprise Guide. The remote SAS session might even be running on a system other than Windows, such

as UNIX, where DDE isn't supported at all.

The second requirement is that the SAS session must run in a windowing environment. Even if your SAS session is

running on a PC that has Microsoft Excel installed, the SAS session is running headless, which means that it has no

visible windows. Without this window environment in place, DDE (which relies on Windows messages) is not

effective.

SAS Enterprise Guide has built-in features to import and export data to and from Microsoft Excel, and you can use

those features to gain back some of the ground lost without DDE. However, SAS Enterprise Guide doesn't offer the

same level of control at the cell level as DDE.

And a KB note to confirm .... 20767 - Use of DDE in SAS® Enterprise Guide® or SAS\CONNECT® software is not supported

Without digging into the EG settings I think even x commands are out so writing a script that would take the resulting sas file and apply the password is out.

You might want to post under EG as well to see if someone has a work around. I am out of ideas.

Sorry

Re: Exporting password protected Excel files using SAS

GCARTE2 — Mon, 31 Mar 2014 15:20:56 GMT

Eric,

Thanks for the info. No need to be sorry, this was very helpful.

-Greg