BookmarkSubscribeRSS Feed
JohnChen_TW
Quartz | Level 8

Hi, I am using SAS 9.3.

According the following program, how to convert XML (named XLS) to XLSX? 

(Creating XML named as XLS is fixed.)

 

 

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

Before that I created a file by ODS TAGSETS.EXCELXP file="&path.\have.XLS";

 

And then, I expect the code will be "%convert_files(default=&path.,ext=XLS);

But it does not work! Does anyone have the suggestions to modify the program above?

8 REPLIES 8
Reeza
Super User

What does 'not working' mean? 

 

Was the vb script created? What happens if you run that from Windows portion? 

 

JohnChen_TW
Quartz | Level 8
Vb script was created but the XLS is not converted to XLSX.
If ext=XML and it will be converted to a XLSX file.
Ksharp
Super User

Maybe you want this PROC.

 

PROC XSL transforms an XML document into another format, such as HTML, text,
or another XML document type. PROC XSL reads an input XML document, transforms
it by using an XSL style sheet, and then writes an output file.

JohnChen_TW
Quartz | Level 8

Hi Ksharp,

 

Thanks for your reply.

 

Please see the whole program below.

%let path=C:\Users\john.chen\Desktop;

data class; set sashelp.class; run;

ods tagsets.excelxp file="&path\temp.XLS" Style=my.style;
proc report data=class nowd
contents="Table of Contents";
	columns Name Sex Age Height Weight;
	define Name 	/'Name';
	define Sex 	/'Sex';
	define Age 	/'Age';
	define Height 	/'Height';
	define Weight 	/'Weight'; 
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;

ods tagsets.excelxp file="&path\temp.XLS" Style=my.style; is needed and cannot be revised.

I know this code does create XML file instead of excel.XLS file ,and this file would later complains when opening it. So I need to transform it into an XLSX file to import it. As I know, the macro above can work when using ext=XML.

How to conver temp.XLS to XLSX file in this case? Does PROC XSL can resolve this case?

 

(Becasue I've never used PROC XSL before, I have no idea how to do...)

 

JC

Ksharp
Super User

I knew there is a macro in support.sas.com can do this.

Search it at support.sas.com

keywords are  tagsets.excelxp  native excel

Reeza
Super User

What happens if you run the VB script from Windows side? If there's an error it should be in the window. 

Or open the script in a text editor and see what's wrong. 

Tom
Super User Tom
Super User

Your steps are out of order in this post.  You cannot call the macro before you have compiled it.

JohnChen_TW
Quartz | Level 8
No, the macro under the dotted line above is just shown what "%convert_files" does, not runs after called it.

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
  • 8 replies
  • 2983 views
  • 0 likes
  • 4 in conversation