Tagsets.ExcelXP to XLSX

[ballardw]

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.

Thoughts?

[FriedEgg]

DDE

http://support.sas.com/documentation/cdl/en/hostwin/63285/HTML/default/viewer.htm#ddeexamples.htm

[FriedEgg]

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.

http://support.sas.com/rnd/base/ods/odsmarkup/tableeditor/index.html

[Cynthia_sas]

Hi,

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.

cynthia

[data_null__]

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

data pgm=work.htm2xls;

   run;

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;

      substr(xls,find(htm,'.',-length(htm)))='.xlsx';

      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);

      input;

      putlog _n_ 3. +2 _infile_;

      end;

   /* call the script */

   command = catx(' ','cscript',quote(strip(script)),'//nologo');

   infile dummy3 pipe filevar=command end=eof truncover;

   do while(not eof);

      input;

      putlog _infile_;

      end;

   stop;

   run;

filename FT67F001 'html2xls.htm';

ods results off;

ods html file=FT67F001;

proc print data=sashelp.class;

   run;

ods html close;

data pgm=work.htm2xls;

   run;

Message was edited by: data _null_ Mr. Gates says use 51 workbookdefault.  I made the changes above.

[ballardw]

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;

data _null_;

     file cmds;

     put %unquote(%str(%'[OPEN("&name..xml")]%'));

     put '[ERROR("FALSE")]';

     put %unquote(%str(%'[SAVE.AS("&name..xlsx",51)]%'));

     x=sleep(1);

     Put '[file.close(false)]';

     put %unquote(%str(%'[OPEN("&name2..xml")]%'));

     put '[ERROR("FALSE")]';

     put %unquote(%str(%'[SAVE.AS("&name2..xlsx",51)]%'));

     x=sleep(1);

     Put '[file.close(false)]';

run;

[data_null__]

You won't be happy going forward using DDE.  That data step is a mess.  That was on the SAS web site?

[Tmacfan]

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.

Best regards,

Vikas

[Ksharp]

Maybe you can use the third-part software to batch these files.

Convert XLS to XLSX Easily with 'Convert XLS'.  20 times faster than using Excel. Excel is not requi...

[FloydNevseta]

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.

[ballardw]

To Ksharp and SAS_Bigot: No budget for additional licensing or programs. We had another converter that would have done this in the past but Windows7 and Office 2007 broke it.

[futile · Accepted solution]

Check out this KB  http://support.sas.com/kb/43/496.html

[jitendra_pandey]

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?

[Spacetime]

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 -

Option Explicit

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
              Myworkbook.Close savechanges:=True
               Application.DisplayAlerts = True
       End If
     Next
End
End Sub