DATA Step, Macro, Functions and more

How to convert XML (named as XLS) to XLSX?

Reply
Frequent Contributor
Posts: 75

How to convert XML (named as XLS) to XLSX?

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?

Super User
Posts: 19,772

Re: How to convert XML (named as XLS) to XLSX?

Posted in reply to JohnChen_TW

What does 'not working' mean? 

 

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

 

Frequent Contributor
Posts: 75

Re: How to convert XML (named as XLS) to XLSX?

Vb script was created but the XLS is not converted to XLSX.
If ext=XML and it will be converted to a XLSX file.
Super User
Posts: 10,020

Re: How to convert XML (named as XLS) to XLSX?

Posted in reply to JohnChen_TW

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.

Frequent Contributor
Posts: 75

Re: How to convert XML (named as XLS) to XLSX?

[ Edited ]

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

Super User
Posts: 10,020

Re: How to convert XML (named as XLS) to XLSX?

Posted in reply to JohnChen_TW

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

Super User
Posts: 19,772

Re: How to convert XML (named as XLS) to XLSX?

Posted in reply to JohnChen_TW

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. 

Super User
Super User
Posts: 7,039

Re: How to convert XML (named as XLS) to XLSX?

Posted in reply to JohnChen_TW

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

Frequent Contributor
Posts: 75

Re: How to convert XML (named as XLS) to XLSX?

No, the macro under the dotted line above is just shown what "%convert_files" does, not runs after called it.
Ask a Question
Discussion stats
  • 8 replies
  • 281 views
  • 0 likes
  • 4 in conversation