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?
What does 'not working' mean?
Was the vb script created? What happens if you run that from Windows portion?
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.
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
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
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.
Your steps are out of order in this post. You cannot call the macro before you have compiled it.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.