topic Re: Multiple Worksheets in Excel Using DDE in Microsoft Integration with SAS

Multiple Worksheets in Excel Using DDE

sasjourney — Sun, 15 Apr 2012 05:51:53 GMT

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

Re: Multiple Worksheets in Excel Using DDE

art297 — Sun, 15 Apr 2012 14:27:15 GMT

Do a google search for: multiple worksheets sas dde

You will find numerous examples.

Re: Multiple Worksheets in Excel Using DDE

SAS_Parilla — Sun, 15 Apr 2012 16:07:47 GMT

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.

Re: Multiple Worksheets in Excel Using DDE

sasjourney — Sun, 15 Apr 2012 18:17:46 GMT

Fount it, Thank you

Re: Multiple Worksheets in Excel Using DDE

sasjourney — Sun, 15 Apr 2012 18:23:04 GMT

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

Re: Multiple Worksheets in Excel Using DDE

Cynthia_sas — Wed, 18 Apr 2012 18:52:07 GMT

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;

Re: Multiple Worksheets in Excel Using DDE

sasjourney — Sun, 29 Apr 2012 17:03:25 GMT

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

Re: Multiple Worksheets in Excel Using DDE

art297 — Sun, 29 Apr 2012 17:29:15 GMT

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.