How to insert sheets into existing workbook in sas

[krishnaalla]

i have two excel .xls files, i need to insert sheets in to existing excel workbook, how can i do this by using DDE in sas.

suggest me any example...  

1. one excel workbook generated using msoffice2k_x & other excelxp tagsets.

2. i face a problem that msoffice2k_x tagsets not able to create mutiple sheets, i need msoffice2k_x syle only.

3. so, one main workbook '.xls'  generated by using msoffice2k_x tagsets & other workbook i use excelxp tagsets.

4. now my problem is i need to insert one workbook in to other.

[Reeza]

Those won't be 'Excel' workbooks they're either XML or HTML under the hood. 

So your first step is converting them to native XLS or XLSX files. There's a VBS on here (support.sas.com) that can help automate that conversion. You should be able to find a VBS command to incorporate the merge of the two files as well. 

Otherwise, How are the sheets defined? Are you going to know all of them ahead of time? 

 You can use DDE to move sheets, I would guess you would select sheet and then there's likely a COPY command.  

DDE is considered outdated and I wouldn't be shocked if it didn't work with Office 2016. 

Is there no option to use ODS EXCEL, instead? Upgrades are supported.  

---

@krishnaalla wrote:

i have two excel .xls files, i need to insert sheets in to existing excel workbook, how can i do this by using DDE in sas.

suggest me any example...  

1. one excel workbook generated using msoffice2k_x & other excelxp tagsets.

2. i face a problem that msoffice2k_x tagsets not able to create mutiple sheets, i need msoffice2k_x syle only.

3. so, one main workbook '.xls'  generated by using msoffice2k_x tagsets & other workbook i use excelxp tagsets.

4. now my problem is i need to insert one workbook in to other.

---

[RW9]

Why do you need one sheet msoffice2k and other sheets tagsets.excelxp.   Excel files fit into one of the below:

XLS - this is the older MS file format for plain Excel files, it is binary not human readable, small files.

XLSX - this is the current MS file format for plain Excel files, it is a compressed file which contains folders and XML files.

Now when you use MSOFFIC2k - this creates HTML output - a plain text file which contains tags and such like to tell the recipient program how to render the data.

Now when you use tagsets.excelxp - this creates XML output - a plain text file which contains tags etc. but is its own distinct language from HTML.

You can't directly interleave the two.  Now, what I suspect your question is would be "I have been asked to provide MSOFFIC2k file with different sheets" which that tagset does not allow.  So first question back to the responder would be is the whole file as tagsets.excelxp ok, liklihood is they wont know the difference.

You also have a third option - assuming you have 9.4 libname excel can create native xlsx files.

[krishnaalla]

i understand what you said but i use 'MSOFFICE2K_X' tagsets which generate .xls with a html features( in my words fronts and colours etc.), but excelxp doesn't support a html features.

i have a problem that the previous reports were msoffice2k_x, so i need to generate a report in the by using msoffice2k_x tagsets ods.

but the new problem according to the msoffice2k_x it doesnt support to generate a multiple sheets, so the main sheet i generated msoffice2k_x tagsets, and the remaining sheets i use excelxp tagsets.

the main sheet i need a same like before (msoffice2k_x) designed, so generated individally, iam not able to understand the exact syntax for DDE.

any example can you suggest.

[RW9]

MSOFFICE2k does not create XLS, it creates HTML.  Tagsets.excelxp can do exactly the same functionality and more, than that tagset.  Have a look at the many posts on how to create Excel reports using tagsets.excelxp and proc report.  Personally I would not go back to using the old methods, and even excelxp is quite old now.

"iam not able to understand the exact syntax for DDE" - I did not mention DDE, nor would I recommend using it at all.

Example, print some SAS files to multiple sheet:

ods tagsets.excelxp file="s:\temp\rob\text.xml";

ods tagsets.excelxp options(sheet_name="First");
proc report data=sashelp.class nowd;
  columns _all_;
  define name / style={foreground=red font_weight=bold};
run;
ods tagsets.excelxp options(sheet_name="Second");
proc report data=sashelp.cars nowd;
  columns _all_;
run;
ods tagsets.excelxp close;

[krishnaalla]

Thank you for suggesting,

But i am using 'MSOFFICE2K_X' tagsets its not 'MSOFFICE2K' ..

MSOFFICE2K_X will create .xml and html both,

MSOFFICE2K will create html only.

currently my problem with Msoffice2k_x not able to create a mutiple sheets, but previously the sheet generated by using msoffice2k_x.

according to modifications i need mutiplesheets from two report procedures one report procedure will generate one main sheet and other contains mutiple sheets.

so, the main sheet i geneerated by using msoffice2k_x and the other i generated by excelxp tagsets both are xls only.

Now, i want to insert those sheets in one excel workbook. by using DDE.  

I dont know how to use DDE.

[RW9 · Accepted solution]

Nope, sorry, msoffice2k_x is merely a slighty more feature rich version of msoffice2k:

http://support.sas.com/rnd/base/ods/odsmarkup/msoffice2k/

You cannot get multiple sheets using this tagset because - quite simply - HTML output does not have the concept of pages or sheets.  You are using HTML thus there is no option to do this. 

You can do this by creating XML using excelxp tagsets - I do not understand your aversion to moving to this?

As for inserting sheets using DDE two things:
Firstly your file is HTML not an Excel file.  Excel can read and interpret the HTML and show you the data in Excel, but you would still need to save that conversion to Excel to get the Excel functionality you require - HTML does not have this.

Secondly DDE is ancient, well over 15 years old now and discontinued by MS, it doesn't work in several scenarios.  I would really advise you look at modern technique sucj as output via Excelxp tagsets, libname Excel, or CSV output and use VBA in Excel to create the output you need.