BookmarkSubscribeRSS Feed
brophymj
Quartz | Level 8

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;

5 REPLIES 5
Reeza
Super User

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
Quartz | Level 8

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
Super User

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
Super User

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
Obsidian | Level 7

Hello Reeza,

 

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

 

Thanks in advance

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4934 views
  • 0 likes
  • 3 in conversation