Convert ODS TAGSETS.EXCELXP .xls output to .xlsx file

eferencik — Mon, 23 Jul 2018 16:20:08 GMT

I attempted using the code shown here, http://support.sas.com/kb/43/496.html, but need something that will convert only the report that the program outputs. I do not want every .xls file in the folder converted this code.

How would I modify the macro program to only convert the report name specified?

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;

I want to be able to specify the file that needs to be converted. Something like the code below where the report name is "CO Turnover Rate &title".

options noxsync noxwait;

%include "c:\convert.sas";

%convert_files(default=c:\users\desktop\Test\CO Turnover Rate &title,ext=xls);

Re: Convert ODS TAGSETS.EXCELXP .xls output to .xlsx file

Reeza — Mon, 23 Jul 2018 16:24:33 GMT

Add an IF condition to check if the FILENAME is the value you want.

Probably at this point, expand the condition to be if extname=... and fileName = .... then...

put "    if ExtName=""&ext"" then";

@eferencik wrote:

How would I modify the macro program to only convert the report name specified?

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;

I want to be able to specify the file that needs to be converted. Something like the code below where the report name is "CO Turnover Rate &title".

options noxsync noxwait;

%include "c:\convert.sas";

%convert_files(default=c:\users\desktop\Test\CO Turnover Rate &title,ext=xls);

topic Re: Convert ODS TAGSETS.EXCELXP .xls output to .xlsx file in ODS and Base Reporting

Convert ODS TAGSETS.EXCELXP .xls output to .xlsx file

Re: Convert ODS TAGSETS.EXCELXP .xls output to .xlsx file