07-09-2013 10:09 AM
Is there a better way to get output into an actual xls? I'm running 9.3 on 64bit windows server 2006r2 and office 2013
ods tagsets.excelxp path=xxxx file=spreadsheet.xml style xlsanspprinter;
ods tagsets.excelxp options (
embedded_titles='yes' embedded_footnotes='yes' sheet_name='Reversibility'
center_horizontal ='yes ');
ods gtagsets.excelxp close;
ods listing close;
right now I'm using a vbscript kicked off by sas to convert the xml to xls
Dim xlApp, xlWkb
Set xlApp = CreateObject("excel.application")
set fs = CreateObject("Scripting.FileSystemObject")
for each file in fs.GetFolder(SourceFolder).files
If Right(LCase(file.Name), 4) = ".xml" Then
Set xlWkb = xlApp.Workbooks.Open(file)
FullTargetPath=TargetFolder & "\" & BaseName
xlWkb.SaveAs FullTargetPath, 51
07-09-2013 06:45 PM
Hi, an "actual xls" file is a proprietary Microsoft format. TAGSETS.EXCELXP creates Microsoft Office Open XML Spreadsheet Markup Language XML 2003 specification. So unless you use the LIBNAME engine or PROC EXPORT, the XML file is as close to Excel "real/actual" format as you can get. with ODS.
The VBScript solution is a good one. It won't work on all operating systems, however, but it is one way to re-save the files, if you MUST have proprietary format .XLS files. Otherwise, I find that naming the files with the .XML extension and educating the folks who open the .xml file is also a viable alternative.
07-11-2013 11:09 AM
according to SAS Tech support 9.4M1 will produce excel 2010 spreadshets
One thing that is puzzeling me is that the vbscripts don't run consistently in midnight batch resulting in xml files and xlsx files remaining in the output folders confusing clients using a web portal to get their reports. The vbscript runs fine if I kick it off in batch but not when run at night