topic Re: Converting XML to XLSX in SAS Procedures

Converting XML to XLSX

brophymj — Wed, 28 Jan 2015 16:55:10 GMT

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;

Re: Converting XML to XLSX

Reeza — Wed, 28 Jan 2015 17:15:00 GMT

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.

Re: Converting XML to XLSX

brophymj — Wed, 28 Jan 2015 17:19:26 GMT

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?

Re: Converting XML to XLSX

Reeza — Wed, 28 Jan 2015 17:48:00 GMT

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?

Re: Converting XML to XLSX

Reeza — Wed, 28 Jan 2015 18:21:49 GMT

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

Re: Converting XML to XLSX

pavan1 — Tue, 26 Feb 2019 07:55:53 GMT

Hello Reeza,

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

Thanks in advance