1

Here's my dilemma:

I have some SAS code that as part of its [somewhat] extensive processing generates a data quality report in "Excel". The reason for the quotes is that SAS only really generates an XML document that can be opened in Excel.

However, as it turns out, most versions of Excel will complain (via a dialog box) when opening said XML file and some versions of Excel won't even go that far.

To alleviate this, someone has to open this "excel" file manually and save it as a real excel file before sending it out to other [important] people.

Obviously, we'd like to automate this. And that isn't even the problem. I created a simple little VBScript program that opens the file, and saves it as Excel. Boom. Problem solved. Well, not really.

Turns out that incorporating this VBScript into normal data-processing is a PITA since all that happens on a Linux Box. Ok, doesn't seem to bad yet. We set up a virtual windows terminal server with a limited use ID that can ssh into the box and run a certain command. A bash script on the linux box now SCPs the XML file into the windows VM, in a folder along with the VBScript and tries to remote execute the VBScript using 

cscript myscript.vbs myxlsfile.xls

This, in theory, should work, but it errors out with the warning:

Microsoft Excel cannot access the file 'myxlsfile.xls'. There are several possible reasons: etc.

Does anyone have any idea of what might be going wrong?

Here is the VBScript:

Set oXL = CreateObject("Excel.Application")
Set FSO = CreateObject("Scripting.FileSystemObject")
oXL.DefaultFilePath = "C:\Temp"
oXL.DisplayAlerts = False
oXL.Visible = False
If FSO.FolderExists(oXL.DefaultFilePath) Then
   Set xmlFile = FSO.GetFile(oXL.DefaultFilePath & "\" & TargetFileName)
   oXL.Workbooks.Open(xmlFile.Name)

   ' -4143 is Excel 2003 format
   oXL.ActiveWorkBook.SaveAs xmlFile.Name, -4143
   oXL.ActiveWorkBook.Close SaveChanges = True
   Set oFolder = Nothing
End If
oXL.DisplayAlerts = True
oXL.Quit
Set oXL = Nothing

Thanks, -- A

Edit: Maybe its worth reiterating that when I run this from the command line on the windows term server, it seems to work just fine. I've also tried echoing all the various path/filename variables to make sure they are coming in right and they are (in both cases)

Improve this question
3
  • Did you ever find a solution for this ? Other than using XP ? I'm running into the exact same situation Commented Oct 6, 2015 at 18:03
  • Naw.. like i said in the answer i posted, we created an XP VM and its been running this for like 4 years now and we haven't had any issues, so we're not gonna touch it :) Commented Oct 7, 2015 at 18:41
  • thank you! We were able to figure it out changing the dcom settings , has been working fine since then, I though I would share it anyways. Commented Oct 19, 2015 at 20:06

4 Answers 4

Reset to default
2

Do you have some kind of logon script that is being executed when you log on interactively but isn't executed by the SSH client? If the file exists on a network path (I know in your example you show c:\temp ... but just in case) and those network connections aren't being created then it could give you that problem. This holds true even if you are using UNC pathnames...

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thats a good point and one of the first things we ruled out by doing exactly that (use c:\Users as the base folder). Also note that the actual vbscript is in the same folder as the xls file so we're trying our best to make it easy on him :)
1

Does the user running the script have access to c:\temp\myxlsfile.xls?

Try running type c:\temp\myxlsfile.xls from the ssh session.

Improve this answer

9 Comments

Yea, unfortunately it doesn't seem to be a permissions issue. We used the same ID as the one SSHing to login to the TS directly and run the VBScript etc.
Try this: In the ssh session echo a > test.xls, then see if the VB script can open test.xls
I'm using SSH command line, so I used the following command: $SSHCommand -i <sshconnection_details> "cd c:\Users\myUsers\myDir && echo a > test.xls && cscript myscript.vbs test.xls" I see the xls file got created, but same error trying to open it in vbscript
Just to verify everything, try checking FSO.FileExists(oXL.DefaultFilePath & "\" & TargetFileName)
As I suspected FileExists returned -1.. No Idea why.. Aargh windows!
|
1

I solved this by using an XP Virtual machine on which to run the VBS code. It uses Office 2003. We haven't exhaustively ruled out all the variables that caused this to not work on the Windows 7 VM with Office 2007, but this works for us at the moment so we decided not to spend any more time on it. The only downside is that the converted file, when opened in the latest Office version opens in protected mode. This is not a huge issue for us as this spreadsheet is intended for people who use Office 2003 anyway.

Thanks all for the help guys. Appreciate it.

-- A

Improve this answer

Comments

0

If you haven't solved this yet, I'm a little unclear on what you're doing. You're running SAS on Linux and writing an XML file to Windows? Then Excel is reading this XLM file.

This won't do you any good right now, but if you get the SAS/Access Interface to PC File Formats (I presume it's available for Linux) you'd be able to assign a libref with the Excel engine on Linux and point it to a directory on the Windows box, so then you could write directly from SAS on the server to an Excel workbook. That's what we do in our AIX-Windows environment. It's not very fast because it uses ODBC but it's reliable. Of course it requires additional licensing an fee for the SAS Software though.

Good luck.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.