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