Converting XML to XLSX

[brophymj]

I'm trying to convert xml files to xlsx in sas. This link shows how to do it:

http://support.sas.com/kb/43/496.html

Using the code in the link and setting up the same folder names(i.e. c:\temp ...), the program works perfectly. However, when I change the directory it doesn't work. The log doesn't seem to display errors but a black box appears for a second after the program is run. Here is the code I am using:

ods noresults;

proc sort data=sashelp.class out=test; 

by age; run;

ods tagsets.excelxp file="S:\Matthew\SAS\XML to Excel\convert\temp.xml" newfile=bygroup;

proc print data=test; 

by age;

run;

ods tagsets.excelxp close;

options noxsync noxwait;

filename convert 'S:\Matthew\SAS\XML to Excel\sasmacro' ; %include convert(convert.sas);

%convert_files(default=S:\Matthew\SAS\XML to Excel\convert,store=S:\Matthew\SAS\XML to Excel\temp.vbs,ext=xml);

the convert.sas macro is below:

%macro convert_files(default=S:\Matthew\SAS\XML to Excel\convert, store=S:\Matthew\SAS\XML to Excel\,ext=xml

THE BELOW CODE IS IN THE LINK :-

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;

[Reeza]

Is the VBS script being created in the folder? If you double click it does it run?

You may not be able to run VBS scripts at particular locations on your system due to security reasons.

[brophymj]

I don't think that's the problem since when I set up the folder S;\temp instead of S:\Matthew\SAS\XML to Excel\temp it doesn't work

Could it be to do with the gaps in the folder location i.e. XML to Excel?

[Reeza]

Change the macro to display the macro variables passed in to see if that's the issue.

I'm assuming this means you don't see the VBS code in the folder?

[Reeza]

You didn't specify the STORE macro variable properly, it is meant to be a reference to a VBS file that SAS will generate.

brophymj wrote:

Works:
%convert_files(default=S:\Matthew\SAS\XML to Excel\convert,store=S:\Matthew\SAS\XML to Excel\temp.vbs,ext=xml);

Doesn't work:

%macro convert_files(default=S:\Matthew\SAS\XML to Excel\convert, store=S:\Matthew\SAS\XML to Excel\,ext=xml);

[pavan1]

Hello Reeza,

how and where to define the STORE variable in the macro?

Thanks in advance