BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
eferencik
Fluorite | Level 6

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

1 REPLY 1
Reeza
Super User

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

 


 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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