StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PORunning a VBScript over SSH from Linux to Windows
text
Body
copied!<p>Here's my dilemma:</p> <p>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. </p> <p>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.</p> <p>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.</p> <p>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.</p> <p>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 </p> <pre><code>cscript myscript.vbs myxlsfile.xls </code></pre> <p>This, in theory, <em>should</em> work, but it errors out with the warning:</p> <p>Microsoft Excel cannot access the file 'myxlsfile.xls'. There are several possible reasons: etc.</p> <p>Does anyone have any idea of what might be going wrong?</p> <p>Here is the VBScript:</p> <pre><code>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 </code></pre> <p>Thanks, -- A</p> <p>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)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload