02-17-2014 03:06 PM
I found code on the internet to change an xml file to a xls file using a VBS Script. Seems easy enough, but for some reason, it won't actually execute the VBS. The code below creates the VBS code, executes it (supposedly), deletes the VBS, and deletes the original xml file. The VBS writes perfectly and will execute if I double click the file or run in it a cmd window. But, SAS skips over it. It does successfully deletes both the VBS and the xml.
Does anyone see any reason why this wouldn't work?
FILE '\\fileserver1\Users\S\Snap Rap\2013\SnapRap_wf_today.vbs';
PUT 'Set objExcel = CreateObject("Excel.Application")';
PUT 'objExcel.Visible = FALSE';
PUT 'objExcel.DisplayAlerts = FALSE';
PUT 'Set objWorkbook = objExcel.Workbooks.Open("\\fileserver1\Users\S\Snap Rap\2013\SnapRap_wf_today.xml")';
PUT 'objExcel.ActiveWorkbook.SaveAs"\\fileserver1\Users\S\Snap Rap\2013\SnapRap_wf_today.xlsx",51';
X "\\fileserver1\Users\S\Snap Rap\2013\SnapRap_wf_today.vbs"; * This won't execute *;
X del "\\fileserver1\Users\S\Snap Rap\2013\SnapRap_wf_today.vbs"; * Delete VB Script - this works fine! *;
X del "\\fileserver1\Users\S\SnapRap\2013\SnapRap_wf_today.xml"; * Delete XML File - this works fine! *;
02-17-2014 10:34 PM
You might want to work with Tech Support on this. I thought that this had to be done with a DDE type of communication scenario, where both SAS and Excel had to be open in order for you to send the commands. Or else, you had to use .NET to make this type of thing work. Tech Support is probably your best bet for a resolution.