topic Re: How to convert XML (named as XLS) to XLSX? in SAS Programming

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

JohnChen_TW — Thu, 30 Mar 2017 03:49:01 GMT

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?

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

Reeza — Thu, 30 Mar 2017 05:31:30 GMT

What does 'not working' mean?

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

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

JohnChen_TW — Thu, 30 Mar 2017 05:53:39 GMT

Vb script was created but the XLS is not converted to XLSX.
If ext=XML and it will be converted to a XLSX file.

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

Ksharp — Thu, 30 Mar 2017 07:20:59 GMT

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.

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

JohnChen_TW — Thu, 30 Mar 2017 08:29:24 GMT

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

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

Ksharp — Thu, 30 Mar 2017 08:37:22 GMT

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

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

Reeza — Thu, 30 Mar 2017 12:27:24 GMT

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.

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

Tom — Thu, 30 Mar 2017 13:08:28 GMT

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

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

JohnChen_TW — Thu, 30 Mar 2017 14:45:06 GMT

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