Excel File Conversion not Working

[eferencik]

I have create a macro program, that converts .xls files to .xlsx files, based on what I found at this link http://support.sas.com/kb/43/496.html.  I call the program in other programs to allow me to produce reports formatted with ODS TAGSETS.EXCELXP and convert the output to .xlsx before emailing the .xlsx version to a distribution list. 

When I manually run a program that calls this macro program on my computer it works.  When I schedule it through the task scheduler on my computer it works.  When we schedule it to run on our production machine (VM) it does not convert to .xlsx and the email logic cannot find the file that should be attached.  My computer runs Windows 10 while the production machine runs Windows 7.  Due to issues with the task scheduler in Windows 10 we cannot upgrade the VM from Windows 7 to Windows 10. 

How do I get SAS to wait for the file conversion to be complete?

[Reeza]

Is it not working or not complete? Those are two very different issues. 

For waiting there's the sleep() command that tells SAS to wait a certain amount of time before proceeding to the next step. This would tell SAS to sleep for one minute and then proceed with the next steps. You can add that into your program. 

data _null_;
y=sleep(60);
run;

If it's not converting the file, I would suspect something with a security protocol preventing the VBS from running. 

---

@eferencik wrote:

I have create a macro program, that converts .xls files to .xlsx files, based on what I found at this link http://support.sas.com/kb/43/496.html.  I call the program in other programs to allow me to produce reports formatted with ODS TAGSETS.EXCELXP and convert the output to .xlsx before emailing the .xlsx version to a distribution list. 

When I manually run a program that calls this macro program on my computer it works.  When I schedule it through the task scheduler on my computer it works.  When we schedule it to run on our production machine (VM) it does not convert to .xlsx and the email logic cannot find the file that should be attached.  My computer runs Windows 10 while the production machine runs Windows 7.  Due to issues with the task scheduler in Windows 10 we cannot upgrade the VM from Windows 7 to Windows 10. 

How do I get SAS to wait for the file conversion to be complete?

---

[eferencik]

We have tested the program several times on the production machine.  One of those times the .xlsx was created but that was 2 minutes after the program finished running so the email was not sent out.  Every other time we tested the program the .xls was produced as expected, but instead of the .xlsx there was a temp.vbs file.

I will looking into the security protocol, but with the conversion working the 1 time (granted 2 minutes later) I don't think that the security protocol is the issue.

[Reeza]

Every other time we tested the program the .xls was produced as expected, but instead of the .xlsx there was a temp.vbs file.

Sorry, but I don't quite understand that statement, it seems contradictory to me.

1. Is the file being created ever, Yes/No? 
2. Is the file being created consistently?
3. Are you trying to create both an XLSX and XLS file? 
4. Why is there an XLS file involved in this process at all? Is that the XML output from ODS TAGSETS.EXCELPXP you're referring to?
5. Are you creating the VBS script each time, or just running/calling it each time?
6. What happens if you call the VBS script from a command line or SAS, not in Batch?

This would be my debugging strategy, not necessarily in that order and you know some of these, but myself and others here wouldn't know the answers to these questions. 

Some other possibilities:

Part of that VBS code is that it loops through and finds all the files first, if you have only those files in the folder it's faster. If you have a lot of files in the folder being converted at once it may take longer than you expect. If it's too slow and you're only doing specific files, perhaps changing the VBS script to target only those files could speed things up as well. 

If just a delay, then it's probably easiest to add a few minutes pause to the program. 

Also, tagsets.excelXP creates an XML file, it's probably easiest to name your files with the XML extension and then convert them. It just helps keep it cleaner with the file types. 

[eferencik]

I need to correct one piece of information I provided earlier.  Both machines are running Windows 7 Professional.

Here are the answers to your questions.  Any tips or suggestions are greatly appreciated.

1. Is the file being created ever, Yes/No?  Yes.  When I manually run or schedule the program on my computer the file is created/converted without issue.  When the program is manually run on the production virtual machine the file is created without issue.  When the program is scheduled through the task scheduler on the virtual machine, the file is not converted and the .vbs file/script is left behind.  When we double click the .vbs it converts to the .xlsx file we need, but that does not work for programs that are automated to run and email to a distribution list.
2. Is the file being created consistently?  The .xls is being created consistently.  The file is converted on my machine to .xlsx consistently.
3. Are you trying to create both an XLSX and XLS file?   The program first outputs the report as .xls using ODS TAGSETS.EXCELXP and then is supposed to convert that file to a .xlsx.
4. Why is there an XLS file involved in this process at all? Is that the XML output from ODS TAGSETS.EXCELPXP you're referring to?  That is how we initially set up the reports.  I don't believe that is the issue since my machine can run the program without issue from the task scheduler.
5. Are you creating the VBS script each time, or just running/calling it each time?  The VBS script is created each time because it is specific to the report that is run, and the file name contains unique information (i.e. month, year, date) so each time the program runs the resulting file name is different.
6. What happens if you call the VBS script from a command line or SAS, not in Batch?  Again, the only time there is an issue is when the program is run from the task scheduler on our virtual machine that runs our production reports.

[Reeza]

Then I'm really suspecting the account that is trying to run in batch, which is usually a system account, doesn't have the correct permissions to run a VB script and it's a security issue from that standpoint. 

TAGSETS.EXCELXP generates an XML file even if you're giving it an XLS extension but that's only to trick Excel into opening it really. Personally, I think it's better to leave them as XML to make the process clear, but I understand not changing things. 

If you can't figure it out beyond this, I'd recommend talking to SAS tech support since they may know what security setting right off the bat. But I've seen this one before, personally and on here, and 99% of the time its the security settings for the account running the code.

---

@eferencik wrote:

I need to correct one piece of information I provided earlier.  Both machines are running Windows 7 Professional.

Here are the answers to your questions.  Any tips or suggestions are greatly appreciated.

1. Is the file being created ever, Yes/No?  Yes.  When I manually run or schedule the program on my computer the file is created/converted without issue.  When the program is manually run on the production virtual machine the file is created without issue.  When the program is scheduled through the task scheduler on the virtual machine, the file is not converted and the .vbs file/script is left behind.  When we double click the .vbs it converts to the .xlsx file we need, but that does not work for programs that are automated to run and email to a distribution list.
2. Is the file being created consistently?  The .xls is being created consistently.  The file is converted on my machine to .xlsx consistently.
3. Are you trying to create both an XLSX and XLS file?   The program first outputs the report as .xls using ODS TAGSETS.EXCELXP and then is supposed to convert that file to a .xlsx.
4. Why is there an XLS file involved in this process at all? Is that the XML output from ODS TAGSETS.EXCELPXP you're referring to?  That is how we initially set up the reports.  I don't believe that is the issue since my machine can run the program without issue from the task scheduler.
5. Are you creating the VBS script each time, or just running/calling it each time?  The VBS script is created each time because it is specific to the report that is run, and the file name contains unique information (i.e. month, year, date) so each time the program runs the resulting file name is different.
6. What happens if you call the VBS script from a command line or SAS, not in Batch?  Again, the only time there is an issue is when the program is run from the task scheduler on our virtual machine that runs our production reports.

---

[Reeza]

And depending on your system/code you can consider converting to ODS EXCEL which generates native XLSX files. This is in production as of 9.4 TS1M3

[eferencik]

We have a couple hundred programs that would have to be modified and converting ODS TAGSETS.EXCELXP to ODS EXCEL is not a quick process.  It would require more time than we have to update all of those programs with ODS EXCEL and have the output formatting the way we currently have it.  Copying and pasting logic into each program to call the conversion program is faster and maintains the desired formatting.