04-20-2012 01:48 PM
Problem: I have to create a relatively large number (several hundred) of Excel workbooks as part of a project. I can create them using ODS Tagsets.ExcelXP but since that is actually XML output some of the files are to large to email through our system. If I convert them to XLSX by using FILE SAVE AS in Excel the size is manageable. Since I firmly believe that computers should do the routine and boring stuff I am hoping someone can give me a hint on how to do that programatically from SAS.
Environment: Windows7 , SAS 9.2.3 BASE, GRAPH and STAT only, Office2010.
04-20-2012 02:07 PM
Tagsets.tableeditor also has a half-way solution. It will produce html output and you can add an option that will put a button on the webpage that will basically perform a copy/paste and save-as in excel.
04-20-2012 02:20 PM
An XLSX file is not just 1 file. It is essentially a zip archive of related files in a format that is proprietary to Microsoft. You can prove this to yourself by going into Excel (plain old Excel) and then create a little worksheet. Save the worksheet as an XLSX file. Then quit Excel and go out to Windows Explorer. Find your file. Rename it to have an extension of .ZIP -- then open the file with WinZIP or something equivalent.
What you should see is that while there is some XML content in the zip archive, that the XLSX content is a mix of proprietary Microsoft content. TAGSETS.EXCELXP cannot create this content because TAGSETS.EXCELXP creates Spreadsheet Markup Language XML 2003 format of XML and the XLSX file format was defined for Office 2007 to be different from XML 2003.
So that's the long explanation why you can't use TAGSETS.EXCELXP to do what you want. The only way to make proprietary XLSX files are to use the LIBNAME engine or PROC EXPORT. Of course, that bypasses many of the cool features of TAGSETS.EXCELXP. What most of my students tell me they do is write the EXCELXP output to a folder location and then run a command file or VB script to open and resave each file to .XLSX format.
04-20-2012 03:01 PM
This example creates and HTM file and then opens it in EXCEL and saves it as XLS. You should be able to modify it easily to XLSX. I'm not sure what the code is for that but you can ask Bill Gates. I wrote it as a stored compiled program so it could be called simply as
the input file is passed via FILEREF FT67F011 this could also be done through a macro variable but be sure to use SYMGET not "double quotes" in a stored compiled program.
data _null_ / pgm=work.htm2xls;
/* gather info */
length htm xls $256 script filevar command $256;
htm = pathname('FT67F001');
xls = htm;
put htm= xls=;
script = catx('\',pathname('WORK'),'SHEETS2TXT.vbs');
filevar = script;
/* write the script */
file dummy1 filevar=filevar;
put 'Const ' htm=$quote256.;
put 'Const ' xls=$quote256.;
*put 'Const xlCSV = 6';
put 'Const xlWorkbookNormal = 51'; *HERE;
put 'Set objExcel = CreateObject("Excel.Application")';
*put 'Set objExcel = GetObject(, "Excel.Application")';
put 'With objExcel';
put +3 '.Visible = False';
put +3 '.DisplayAlerts = False';
put +3 'Set objWorkbook = .Workbooks.Open(htm)';
put +3 'objWorkBook.SaveAs xls,xlWorkbookNormal';
put +3 '.Application.Quit';
put +3 'End With';
/* close the script file by opening another, not used */
filevar = catx('\',pathname('WORK'),'DUMMY.vbs');
file dummy1 filevar=filevar;
/* look at the script, not necessary but may be useful */
infile dummy2 filevar=script end=eof;
do _n_ = 1 by 1 while(not eof);
putlog _n_ 3. +2 _infile_;
/* call the script */
command = catx(' ','cscript',quote(strip(script)),'//nologo');
infile dummy3 pipe filevar=command end=eof truncover;
do while(not eof);
filename FT67F001 'html2xls.htm';
ods results off;
ods html file=FT67F001;
proc print data=sashelp.class;
ods html close;
Message was edited by: data _null_ Mr. Gates says use 51 workbookdefault. I made the changes above.
04-20-2012 07:04 PM
The code below is a sketch of what I'll use. I already have macro variables for the file path and name so the code below will be modified to use the same logic to loop within the data _null_ below.
The major key to this solving the problem is 51 for file type. (slight shift from 42, scary isn't it?).
There's probably many ways to build the strings for the PUT statements, this I cribbed from one of the PDFs on the SAS site.
filename cmds dde 'excel|system';
%LET NAME =D:\Data\Site 1;
%let name2=D:\Data\Site 2;
01-17-2017 06:06 AM
Hi data_null__ ,
Thank you for posting this great little functionality.
Can you please help me on how I can change the path where I store the htm and xls files.
04-24-2012 09:21 AM
If you often work with Excel workbooks or Access databases, then it may be worth licensing SAS/Access to PC Files. With the license, working with MS Office files is almost the same as working with native SAS libraries.
03-03-2017 03:38 PM
This works fine in interactive mode, but doesn't if you use Windows Task Scheduler to run the SAS program that uses this script to convert xls files to xlsx files. The vbscript does not even launch.
Do we have something that works for a scheduled reporting program?
06-07-2012 06:13 PM
I had to do the exact same thing. I recommend use a VBA macro to go and convert them to xlsx after the fact. Here's the code I use to do this -
Public Sub UPCONVERT()
Dim FS As New FileSystemObject
Dim FSfolder As Folder
Dim myFile As File
Dim Myworkbook As Workbook
Dim sFolderPath As String
sFolderPath = "C:\MYFILES"
Set FSfolder = FS.GetFolder(sFolderPath)
For Each myFile In FSfolder.Files
If Right(myFile.Name, 3) = "xls" Or Right(myFile.Name, 4) = "xlsx" Then
Set Myworkbook = Application.Workbooks.Open(myFile.Path)
ActiveWorkbook.CheckCompatibility = False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=myFile.Path, FileFormat:=xlNormal
Application.DisplayAlerts = True
Need further help from the community? Please ask a new question.