This program creates a standard Excel file, opens the file in Excel using Dynamic Data Exchange (DDE), and then appends a password to protect the data set.

In this sample, the SAS program

1. uses the EXPORT procedure to export data to an Excel workbook
2. opens Excel, and this it opens the Excel file that is not password protected
3. uses DDE to append a password to a save copy of the file.

When these steps are completed, the program then has an option to destroy the copy of the file that is not password protected.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

This program creates password-protected Excel data sets.

---

******************************************************************************;
* Program name - Passwordwrite.sas                                          *;
* Purpose: To create Excel files that are password protected.                *;
*                                                                            *;
* THIS PROGRAM SHOULD ONLY BE DISTRIBUTED TO PEOPLE WHO KNOW THE PASSWORDS   *;
* TO THEIR EXCEL FILE. THE PROGRAM ACCESSIBILITY SHOULD ALSO BE LIMITED      *;
* TO THE PEOPLE WHO USE THE EXCEL FILE AND ITS PASSWORDS.                    *;
* NOTE THAT, CURRENTLY, THIS WILL ONLY OPEN FILES WITH READ-PROTECTED        *;
* PASSWORDS.                                                                 *;
*                                                                            *;
* YOU SHOULD ALSO ENSURE THAT THE COPY OF THE XLS FILE IT CREATES IS TRULY   *;
* DESTROYED AT THE END.                                                      *;
******************************************************************************;

%macro writpass(xlsfile1,xlsfile2,password,sasfile,deleteit);
options macrogen symbolgen mprint nocaps;
options noxwait noxsync;

   * First, SAS writes an Excel file here without the password. *;
proc export outfile="&xlsfile1" data=&sasfile dbms=excel replace;
run; 
quit;

   * This next routine starts the Excel application.   *;
filename cmds dde 'excel|system';
x "'C:\Program Files\Microsoft Office\Office11\excel.exe'";

data _null_;
   x=sleep(3);
run;

   * SAS opens the Excel worksheet with its password. *;
filename cmds dde 'excel|system';

data _null_;
   file cmds;
   put '[open("'"&xlsfile1"'")]';
run;

   
   * SAS saves the file without the password. *;
data _null_;
   file cmds;
   put '[error("false")]';
   put '[save.as("'"&xlsfile2"'",1,"'"&password"'")]'; 
   put '[quit]';
run;

   * Then SAS destroys the Excel file that is not password protected. *;
%if &deleteit=yes %then %do;
   systask command "del ""&xlsfile1"" ";
%end;

%mend writpass;

%writpass(c:\sastest\nopass.xls, /* Name of the Excel file that is not */
                                 /* password protected.                */
          c:\sastest\pass.xls,   /* Name of the password-protected Excel file. */
	     password1,          /* Password for the Excel spreadsheet.          */
             sashelp.class,   /* Name of the SAS data set from which you want to */ 
                              /* create.                                         */
	     no);             /* Delete the Excel file that is not password      */
                              /* protected? Yes or no.                           */

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

This program creates a standard Excel file, opens it in Excel using Dynamic Data Exchange, and then appends a password to protect the data set.

Type:

Sample

Date Modified:	2008-09-22 10:42:44
Date Created:	2008-02-28 10:52:02

Operating System and Release Information

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	SAS/ACCESS Interface to PC Files	Microsoft Windows 2000 Professional	9.1 TS1M3
		Microsoft Windows NT Workstation	9.1 TS1M3
		Microsoft Windows Server 2003 Datacenter Edition	9.1 TS1M3
		Microsoft Windows Server 2003 Standard Edition	9.1 TS1M3
		Windows Vista	9.1 TS1M3
		Microsoft Windows XP 64-bit Edition	9.1 TS1M3
		Microsoft® Windows® for x64	9.1 TS1M3
		Microsoft Windows 2000 Datacenter Server	9.1 TS1M3
		Microsoft Windows Server 2003 Enterprise Edition	9.1 TS1M3
		Microsoft Windows XP Professional	9.1 TS1M3
		Microsoft Windows 2000 Server	9.1 TS1M3
		Microsoft Windows 2000 Advanced Server	9.1 TS1M3