通过 SSH 从 Linux 到 Windows 运行 VBScript
这是我的困境：
我有一些 SAS 代码，作为其[某种程度上]广泛处理的一部分，在“Excel”中生成数据质量报告。引号的原因是 SAS 仅真正生成可以在 Excel 中打开的 XML 文档。
然而，事实证明，大多数版本的 Excel 在打开所述 XML 文件时都会抱怨（通过对话框），并且某些版本的 Excel 甚至不会走那么远。
为了缓解这个问题，有人必须手动打开这个“excel”文件并将其保存为真正的 Excel 文件，然后再将其发送给其他[重要]人。
显然，我们希望将其自动化。这甚至不是问题。我创建了一个简单的 VBScript 小程序来打开该文件并将其另存为 Excel。繁荣。问题解决了。嗯，不是真的。
事实证明，将此 VBScript 合并到正常的数据处理中是一个 PITA，因为所有这些都发生在 Linux Box 上。好吧，看来还不算坏。我们设置了一个具有有限使用 ID 的虚拟 Windows 终端服务器，可以通过 ssh 进入该盒子并运行特定命令。 Linux 机器上的 bash 脚本现在将 XML 文件与 VBScript 一起保存到 Windows VM 的一个文件夹中，并尝试使用远程执行 VBScript。
cscript myscript.vbs myxlsfile.xls
理论上，应该可以工作，但它会出错出现警告：
Microsoft Excel 无法访问文件“myxlsfile.xls”。可能的原因有以下几种： 等等。
有谁知道可能出了什么问题吗？
这是 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
谢谢， --
编辑：也许值得重申的是，当我从 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)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您是否有某种登录脚本在您交互登录时正在执行，但 SSH 客户端并未执行？如果该文件存在于网络路径上（我知道在您的示例中您显示了 c:\temp ...但以防万一）并且未创建这些网络连接，那么它可能会给您带来这个问题。即使您使用 UNC 路径名，这也是如此......
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...
运行脚本的用户是否有权访问
c:\temp\myxlsfile.xls？尝试从 ssh 会话运行
type c:\temp\myxlsfile.xls。Does the user running the script have access to
c:\temp\myxlsfile.xls?Try running
type c:\temp\myxlsfile.xlsfrom the ssh session.我通过使用 XP 虚拟机来运行 VBS 代码解决了这个问题。它使用 Office 2003。我们还没有彻底排除导致其无法在装有 Office 2007 的 Windows 7 VM 上运行的所有变量，但目前这对我们有效，因此我们决定不再花更多时间在这上面。唯一的缺点是转换后的文件在最新的 Office 版本中打开时会在保护模式下打开。这对我们来说并不是一个大问题，因为该电子表格是为使用 Office 2003 的用户准备的。
感谢大家的帮助。欣赏它。
-- 一个
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
如果你还没有解决这个问题，我有点不清楚你在做什么。您在 Linux 上运行 SAS 并向 Windows 写入 XML 文件？然后Excel正在读取这个XLM文件。
现在这对你没有任何好处，但如果你获得了 PC 文件格式的 SAS/Access 接口（我认为它可用于 Linux），你就可以在 Linux 上使用 Excel 引擎分配一个 libref 并指向它到 Windows 机器上的目录，这样您就可以直接从服务器上的 SAS 写入 Excel 工作簿。这就是我们在 AIX-Windows 环境中所做的事情。由于它使用 ODBC，因此速度不是很快，但它很可靠。当然，它需要额外的 SAS 软件许可和费用。
祝你好运。
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.