CODEWITHSUNDEEP
vbscriptsshsas
Adnan
Adnan

Reputation: 2967

Running a VBScript over SSH from Linux to Windows

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)

Upvotes: 1

Views: 5590

Answers (4)

Adnan
Adnan

Reputation: 2967

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

Upvotes: 1

John XXX
John XXX

Reputation: 11

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.

Upvotes: 0

Robert Penridge
Robert Penridge

Reputation: 8513

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...

Upvotes: 2

daalbert
daalbert

Reputation: 1475

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

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

Upvotes: 1

Related Questions