muti-sheet xls output for Excel 1997?

[Katie6]

Hi there,

I am trying to export some data into Excel using SAS. I need the Excel document to have multiple sheets, because it would be too confusing to put lots of big tables on one sheet, but my customers only have Excel 1997, which is a right pain because Excel 1997 doesn't support xml.

Is there some way to output the data into Excel with multiple sheets for Excel 1997?

Many, many thanks.

Katie

 

[Katie6]

I have tried the following code:

proc export data=sashelp.class
outfile='c:\myfiles\Femalelist.xls'
dbms=excel97
replace;
run;

It doesn't work. Could this be because the servers run on UNIX?

 

[ChrisW75]

Hi Katie - yes, that's likely to be the case. Excel doesn't run on Unix, therefore SAS doesn't include drivers for outputting to Excel in the Unix package. There is a separate SAS add-on package available I believe that would allow you to do this, but it's something you're likely going to have to pay for.
There might be another SAS based way, but I don't know how it.

I can think of 2 external ways to do this, both require the use of macros in Excel.
1 - write out the data to CSV files or something similar, then write a macro in Excel that brings the data in to different spreadsheets.

2 - output to XML as if you were doing it for Excel2003, then write a macro in Excel which saves the file as an Excel97 version.

I actually did this second method for a client who had Excel97, the VBA command for saving the file correctly is this

ActiveWorkbook.SaveAs Filename:=filnm _
  , FileFormat:=xlNormal, Password:="", writeResPassword:="", _
   ReadOnlyRecommended:=False, CreateBackup:=False

Also in the macro, I included code which lists each of the Excel files in the directory, then opens each one in turn, applies a small number of formatting changes to the sheets in the workbook, then saves and closes. I might be able to provide the VBA module to you if necessary, once I've removed any proprietary items and customer references out of it. Let me know if you need it.


Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.