The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

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

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

 


Accepted Solutions
Solution
4 weeks ago
Super User
Posts: 24,010

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

Posted in reply to eferencik

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:

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

 


 

View solution in original post


All Replies
Solution
4 weeks ago
Super User
Posts: 24,010

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

Posted in reply to eferencik

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:

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

 


 

☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 132 views
  • 0 likes
  • 2 in conversation