DATA Step, Macro, Functions and more

Tagsets.ExcelXP to XLSX

Accepted Solution Solved
Reply
Super User
Posts: 10,483
Accepted Solution

Tagsets.ExcelXP to XLSX

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?


Accepted Solutions
Solution
‎06-07-2012 04:16 PM
N/A
Posts: 1

Re: Tagsets.ExcelXP to XLSX


All Replies
Trusted Advisor
Posts: 1,300
Trusted Advisor
Posts: 1,300

Re: Tagsets.ExcelXP to XLSX

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

SAS Super FREQ
Posts: 8,742

Re: Tagsets.ExcelXP to XLSX

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

Respected Advisor
Posts: 3,777

Re: Tagsets.ExcelXP to XLSX

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.

Attachment
Super User
Posts: 10,483

Re: Tagsets.ExcelXP to XLSX

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;

Respected Advisor
Posts: 3,777

Re: Tagsets.ExcelXP to XLSX

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

Occasional Contributor
Posts: 7

Re: Tagsets.ExcelXP to XLSX

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

Super User
Posts: 9,671

Re: Tagsets.ExcelXP to XLSX

Frequent Contributor
Posts: 101

Re: Tagsets.ExcelXP to XLSX

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.

Super User
Posts: 10,483

Re: Tagsets.ExcelXP to XLSX

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.

Solution
‎06-07-2012 04:16 PM
N/A
Posts: 1

Re: Tagsets.ExcelXP to XLSX

Occasional Contributor
Posts: 12

Re: Tagsets.ExcelXP to XLSX

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?

Thanks and regards,
Jitendra Pandey
Occasional Contributor
Posts: 14

Re: Tagsets.ExcelXP to XLSX

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 13 replies
  • 12829 views
  • 4 likes
  • 10 in conversation