Using DDE to Read and Write to Excel Workbooks - Exchanging Data Between SAS and Microsoft Excel - page 179

Figure 11.6.4: Excel File with “Hello” “World” in Cells R1C1 and R1C2 (A1:B1).
Being able to find the words “Hello” and “World” in Excel completes this project. In fact, the words appear
while the SAS program executes or shortly after it finishes running. The space between the words caused
the data to be placed into two separate cells in the Excel worksheet. This simple example made two
assumptions:
● The Excel file was in the same directory that SAS was using.
• The Excel file was open.
The SAS FILENAME syntax that will point to an open Excel file in another directory follows:
Figure 11.6.5: SAS FILENAME Statement to Assign ddewrite to a File.
FILENAME ddewrite DDE 'excel|C:\Excel_Files\[Book1.xls]Sheet1!r1c1:r1c2';
Example 11.6.2 The Hello World Project When the Excel Workbook Is
Closed
This example is very similar to the first example except that it starts with the Excel file closed. The SAS
“X” command is used to issue an operating system command to start the Excel application before running
the DDE commands from SAS. Some of the newer versions of Microsoft Excel take several seconds to
open and activate the add-ins that are installed. The number and type of add-ins may vary for each
computer installed in your area. Since SAS and Excel must both be running at the same time, it is important
to wait long enough for Excel to be fully active before issuing a DDE command.
Figure 11.5.2 has a list of common default locations for where Microsoft Excel is installed. Your location
may be different. Ask your Information Technologies Department if your location is different.
The SAS code in Figure 11.6.6 shows how to open an Excel workbook using the SAS X command, and
then how to write “Hello World” to the Excel worksheet. This example is using Excel 2013 on a 64-bit
system. One of the new features of Excel 2013 is “Splash Screen” that allows you to choose the type of
workbook or template you want to open. Previous versions of Excel also had “Splash Screens”, but none of
them waited until you entered a response.