%
******************************************************************************************;
%*
%* MACRO: CONVERT_FILES
%*
%* USAGE: %convert_files(arg1=value, arg2=value, ... );
%*
%* DESCRIPTION:
%* This macro is used to convert files generated with ODS destinations
%* to native Excel files. For more information see the below link.
%* http://www.pharmasug.org/proceedings/2011/SAS/PharmaSUG-2011-SAS-TT02.pdf?page=12
%*
%*
%*
%*
%*******************************************************************************************;
options noxsync noxwait;
/* Example: %ConvertXML(Default=&SASProgramPath.\output\,
Store=&SASProgramPath.\output\temp.vbs); */
%macro ConvertXML(default=c:\temp\convert, store=c:\temp\, ext=xml);
data _null_;
file "&store";
put " Dim oXL ";
put " Dim oFolder";
put " Dim aFile";
put " Dim FSO";
put " Set oXL = CreateObject(""Excel.Application"")";
put " Set FSO = CreateObject(""Scripting.FileSystemObject"")";
put " oXL.DefaultFilePath = ""&default""";
put " oXL.DisplayAlerts = False";
put " if FSO.FolderExists(oXL.DefaultFilePath) then";
put " Set oFolder = FSO.GetFolder(oXL.DefaultFilePath)";
put " For each aFile in oFolder.Files ";
put " If Right(LCase(aFile.Name), 4) = "".&ext"" Then";
put " oXL.Workbooks.Open(aFile.Name)";
put " oXL.Visible = false";
put " if (oXL.Version) >= 12 Then" ;
put " oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) -4) & "".xlsx"",51";
put " Else";
put " oXL.ActiveWorkBook.SaveAs Left(aFile, Len(aFile) -4) & "".xls"",-4143";
put " End If";
put " oXL.ActiveWorkBook.Close SaveChanges = True";
put " End If";
put " Next";
put " Set oFolder = Nothing";
put " end if";
put " FSO.DeleteFile(""&default\*.&ext""), DeleteReadOnly";
put " oXL.DisplayAlerts = True";
put " oXL.Quit";
put " Set oXL = Nothing";
call system("&store");
run;
%mend;