SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Multiple Worksheets in Excel Using DDE

Reply
Contributor
Posts: 43

Multiple Worksheets in Excel Using DDE

Hi,

Am trying to create a multitab excel sheet using DDE, I was able to create multiple excel sheets but was not able to create a single excel with multiple tabs. Is there any way to do this using DDE.

Thanks

PROC Star
Posts: 7,431

Re: Multiple Worksheets in Excel Using DDE

Do a google search for: multiple worksheets sas dde

You will find numerous examples.

Contributor
Posts: 43

Re: Multiple Worksheets in Excel Using DDE

Fount it, Thank you

New Contributor
Posts: 3

Re: Multiple Worksheets in Excel Using DDE

Sheet name is part of the file reference in DDE so take a look at your file name statements -- sorry don't have the exact syntax in front of me.

Also, for better or worse, DDE is a dying methodology.  And it will be dead once Microsoft pulls the plug.  So don't spend a lot of time using DDE within long term production jobs.  Take a look at ODS or PC File server.  ODS does multi-sheet workbooks.  Hate to see DDE go, but its in the class of buggy whips.

Happy coding to you.

Contributor
Posts: 43

Re: Multiple Worksheets in Excel Using DDE

Hi,

Yeah that's true but didn't see any way to copy some part of text from word doc to excel, for example, a table from word to excel using ODS.

Thanks

SAS Super FREQ
Posts: 8,816

Re: Multiple Worksheets in Excel Using DDE

Hi:

  I guess I don't understand why you would want to copy a table from Word to Excel using ODS. Word and Excel are both proprietary Microsoft products. Word makes documents. Excel has workbooks composed of worksheets. SAS and ODS can -CREATE- output files for Word (using ODS RTF) and for Excel (using ODS CSV, ODS HTML or ODS TAGSETS.EXCELXP).

  But for Word to Excel communication or transfer of document content from Word to Excel, I would not expect SAS to be able to help you much in this regard. I'm not sure where you got the idea that SAS could "copy some part of text from word doc to excel", but that is not something that would happen unless you could 1) read the Word doc with SAS and take the table from Word into a SAS dataset and then 2) once you have a SAS dataset write the dataset table to a format taht Excel can open.

  However, #1 is very problematic. Word documents are in a proprietary document format -- Word's primary purpose is to have paragraphs of text --it may have tables, but you could have a whole document without a single table.

  See the code below. It is creating an RTF file based on SASHELP.SHOES and a file for Excel using the same SAS dataset. I know that this is not exactly what you said you wanted to do -- but cutting from Word and pasting into Excel does sound like something you would do with DDE or a VB Script or VBA -- Microsoft technology and not SAS technology.

cynthia

ods listing close;

ods rtf file='c:\temp\two_regions.rtf' ;

ods tagsets.excelxp file='c:\temp\two_regions.xml' style=sasweb;

 

ods tagsets.excelxp options(sheet_name='Asia');

 

proc report data=sashelp.shoes nowd;

where region = 'Asia';

column region product sales inventory returns;

define region / group;

define product / group;

rbreak after/ summarize;;

run;

 

ods tagsets.excelxp options(sheet_name='Canada');

 

proc report data=sashelp.shoes nowd;

where region = 'Canada';

column region product sales inventory returns;

define region / group;

define product / group;

rbreak after/ summarize;;

run;

ods _all_ close;

Contributor
Posts: 43

Re: Multiple Worksheets in Excel Using DDE

Hi ,

Thanks for your reply and that's true, I was asking myself why do we need to do  this when both the word and excel coming from microsoft before seeing the actual problem. The problem was, we have the data coming from another source where they entered data into a word file in a table format. we can copy that table into an excel file manually and then import it to SAS. But I want to do this using only SAS instead of manually copying.

Thanks

PROC Star
Posts: 7,431

Re: Multiple Worksheets in Excel Using DDE

If the task doesn't have to be automated, here is one approach you can try.  It is a method a group of us proposed in a paper that was presented at the forum last week: http://www.sascommunity.org/wiki/Copy_and_Paste_Almost_Anything

Let us know if it provides a satisfactory solution to what you are trying to accomplish.

If it the method works for what you have to accomplish, it could be automated with some VB Script.

Ask a Question
Discussion stats
  • 7 replies
  • 1284 views
  • 0 likes
  • 4 in conversation