topic Convert XLS to XLSX in SAS Enterprise Guide

Convert XLS to XLSX

Daily1 — Fri, 15 Sep 2023 06:48:33 GMT

Pls help Code not properly work 

%let path=/desreports;

ods tagsets.excelxp file="&path/temp.XLS" Style=my.style;
data class; set sashelp.class; run;

proc report data=class; run;

ods tagsets.excelxp close;

%convert_files(default=&path,ext=XLS);

/*------------------------------Convert () to XLSX--------------------------------*/
 options noxwait noxsync; 
 
%macro convert_files(default=,ext=);
 
data _null_;
file "'&default/temp.vbs'";
put "set xlapp = CreateObject(""Excel.Application"")";
put "set fso = CreateObject(""scripting.filesystemobject"")";
put "set myfolder = fso.GetFolder(""&default"")";
put "set myfiles = myfolder.Files";
put "xlapp.DisplayAlerts = False";
put " ";
put "for each f in myfiles";
put "  ExtName = fso.GetExtensionName(f)";
put "  Filename= fso.GetBaseName(f)";
put "    if ExtName=""&ext"" then";
put "           set mybook = xlapp.Workbooks.Open(f.Path)"; 
put "           xlapp.Visible = false";
put "           mybook.SaveAs ""&default./"" & Filename & "".xlsx"", 51";
put "    End If";
put "  Next";
put "  mybook.Close";
put "  xlapp.DisplayAlerts = True";
/* Removes original files */
put " FSO.DeleteFile(""&default/*.&ext""), DeleteReadOnly";
put " xlapp.Quit";
put " Set xlapp = Nothing";
put " strScript = Wscript.ScriptFullName";
put " FSO.DeleteFile(strScript)"; 
run; 
 
x "cscript ""&default/temp.vbs""";
 
%mend;

Re: Convert XLS to XLSX

AlanC — Fri, 15 Sep 2023 07:09:37 GMT

You don't specify what the issue is but you are using VBS which is not a recommended language.

Look here: Bulk conversion of old Office documents to new formats - Office Watch (office-watch.com)

Easy to also do in VBA, C#, probably Python, and a number of others. If the goal is to just do this once, use a utility. If not, use a better scripting language than VBS. Consider a simple command line utility. Look here: Convert xls to xlsx using Windows cmd · GitHub

I use commercial tools to do this and it is literally 3 lines. There are loads of utilities to help. Just use the X command in SAS.

If you want to do this in VBS, get it to work first then wrap in PUT statements. I just uploaded an old program to github you can use to do the put statements with what you created. I will add source code later.

See: savian-net/SaviPut: Takes any text and wraps it in SAS put statements (github.com)

Re: Convert XLS to XLSX

Patrick — Fri, 15 Sep 2023 12:50:26 GMT

If this is just about using SAS to create *.xlsx files instead of *.xls then use destination ODS EXCEL instead of the outdated ODS tagset.exelxp.