Convert XLS to XLSX

[Daily1]

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;

[AlanC]

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)

[Patrick]

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.