如何从 VBA (Excel) 调用 Bash 脚本

How to call a Bash script from VBA (Excel)

提问by Mohamed

How can I run a bashfile using VBA on MAC OSX

如何bash在 MAC OSX 上使用 VBA运行文件

I tried the following code

我尝试了以下代码

location = FolderPath(ThisWorkBook.FullName) 

which returns the current directory minus the file name.

它返回当前目录减去文件名。

followed by

其次是

Shell(location &"runmybatch.sh")

I did this because the bash script is located within the same folder as the excel spreadsheet.

我这样做是因为 bash 脚本与 Excel 电子表格位于同一文件夹中。

回答by Joel Spolsky

There are a couple of surprises that make this tricky!

有一些惊喜让这变得棘手！

1. The Shellfunction expects paths to be separated by :, not /
2. When the script runs, it will have the root (/) directory as its working directory

1. 在壳牌函数期望路径通过分离：，而不是/
2. 当脚本运行时，它会将根 ( /) 目录作为其工作目录

Other than that you should be able to run a bash script using the Shellcommand. Here are step-by-step instructions to get you started.

除此之外，您应该能够使用Shell命令运行 bash 脚本。以下是帮助您入门的分步说明。

• Create the following bash script in your Desktop directory and name it test.sh:

• 在您的桌面目录中创建以下 bash 脚本并将其命名为test.sh：

#!/bin/bash
open /Applications/Calculator.app/

#!/bin/bash
open /Applications/Calculator.app/

• Make sure test.sh is executable (from a Terminal window, chmod +x test.sh)
• Try running it (type ./test.shin Terminal). The calculator app should appear. Exit that.
• Run Excel and create a new workbook, Workbook1.
• Save this blank workbook in the Desktopdirectory as a Macro-enabled workbook
• Run VB: Tools > Macro > Visual Basic Editor
• If the immediate window is not showing, View > Immediate Window
• In the immediate window, type the following VBA code:

• 确保 test.sh 是可执行的（从终端窗口，chmod +x test.sh）
• 尝试运行它（在终端中输入./test.sh）。应该会出现计算器应用程序。退出那个。
• 运行 Excel 并创建一个新工作簿Workbook1。
• 将此空白工作簿保存在桌面目录中作为启用宏的工作簿
• 运行 VB：工具 > 宏 > Visual Basic 编辑器
• 如果未显示即时窗口，请查看 > 即时窗口
• 在立即窗口中，键入以下 VBA 代码：

 Shell ActiveWorkbook.Path & ":test.sh"

 Shell ActiveWorkbook.Path & ":test.sh"

• The calculator app should run.

• 计算器应用程序应该运行。

回答by Steph

I had no luck with the shell command, but here's what worked for me through applescript in Excel 2011:

我对 shell 命令没有运气，但这是通过 Excel 2011 中的 applescript 对我有用的方法：

script_s = "do shell script ""/Users/user/path/to/script/rubyscript_09.rb"""
MacScript script_s

I found it necessary to have the script use a shebang of '#!/usr/bin/env ruby' (after the install of ruby version manager)

我发现有必要让脚本使用 ' #!/usr/bin/env ruby'的shebang （在安装 ruby​​ 版本管理器之后）

To address a file that is in a subfolder of the folder of the calling file, use this:

要寻址位于调用文件文件夹子文件夹中的文件，请使用以下命令：

FilePathName_s = ThisWorkbook.Path & ":subfolder:filename"

This returns a path with :'s, which I believe must be converted to /'s for the do shell script. This can be done with the AppleScript, quoted form of POSIX path of FilePathName_s, or with the VBA FilePathName_s = "/" & Replace(FilePathName_s, ":", "/")

这将返回一个带有:'s的路径，我认为/对于do shell script. 这可以通过 AppleScriptquoted form of POSIX path of FilePathName_s或 VBA 来完成FilePathName_s = "/" & Replace(FilePathName_s, ":", "/")

回答by sage

I also had trouble getting my simple bash script to work, even though I had it on the global path (/usr/bin) and all users had read and execute permissions. Two things caused my script to fail: permissions and paths due to differences between the AppleScript environment and my user's bash environment. I found the discussion over at VBA Shell function in Office 2011 for Macmore helpful in solving my problem. Here is the process I eventually settled on.

即使我的简单 bash 脚本位于全局路径 (/usr/bin) 上并且所有用户都具有读取和执行权限，但我也无法使用它。两件事导致我的脚本失败：由于 AppleScript 环境和我用户的 bash 环境之间的差异，权限和路径。我发现Office 2011 for Mac中VBA Shell 函数的讨论对解决我的问题更有帮助。这是我最终确定的过程。

Because Excel was masking the underlying details, I recommend using the AppleScript Editor, which hopefully gives better insight during your troubleshooting than the meaningless Excel errors I saw:

因为 Excel 掩盖了底层的细节，我建议使用 AppleScript 编辑器，它有望在您进行故障排除时提供比我看到的无意义的 Excel 错误更好的洞察力：

1. Use AppleScript Editor to confirm that the script actually works as whatever user and with whatever environment variable happens to be used:

   1. In Spotlight, start typing "applescript editor" until it shows up and then click on it
   2. Create a new AppleScript Editor file

   3. Type your simple script into the new file withoutdoubling the double quotes - mine reads

      do shell script "parseCsvAndOpen.sh"
      

   4. Hit the "Run" button for your script
   5. Track down any issues, make changes, and repeat hitting the "Run" button until you get it to execute from within AppleScript Editor
      • The good news here is that you have a narrower search if you need to go back to StackOverflow or Google for help ;-)

2. now copy your simple script from AppleScript Editor to your vba and confirm it still works

   1. I was able to just double my double quotes and put it in double quotes after the MacScript code:

      MacScript "do shell script ""parseCsvAndOpen.sh"""
      

      That is indeed one, two, and then three double-quote characters! (presumably escaping the double quotes)

1. 使用 AppleScript 编辑器确认该脚本实际上可以作为任何用户和碰巧使用的任何环境变量工作：

   1. 在 Spotlight 中，开始输入“applescript editor”直到它出现，然后单击它
   2. 创建一个新的 AppleScript 编辑器文件

   3. 将您的简单脚本输入到新文件中，无需将双引号加倍 - 我的读取

      do shell script "parseCsvAndOpen.sh"
      

   4. 点击脚本的“运行”按钮
   5. 跟踪任何问题，进行更改，然后重复点击“运行”按钮，直到您在 AppleScript 编辑器中执行它
      • 好消息是，如果您需要返回 StackOverflow 或 Google 寻求帮助，您的搜索范围将缩小；-)

2. 现在将您的简单脚本从 AppleScript 编辑器复制到您的 vba 并确认它仍然有效

   1. 我能够将我的双引号加倍，并在 MacScript 代码之后将它放在双引号中：

      MacScript "do shell script ""parseCsvAndOpen.sh"""
      

      那确实是一个，两个，然后三个双引号字符！（大概是转义双引号）

回答by Joe

I did not use the FolderPath command, its seems not to be availble to me so I will investigate later why not. Below is some sample code that I have tested to make sure that it is possible to run a .sh file from OSX Excel.

我没有使用 FolderPath 命令，它似乎对我不可用，所以我稍后会调查为什么不使用。下面是我测试过的一些示例代码，以确保可以从 OSX Excel 运行 .sh 文件。

The .sh file I used to test was placed on my desktop and only contains the following line: "open /Applications/Calculator.app"

我用来测试的 .sh 文件放在我的桌面上，只包含以下行：“打开 /Applications/Calculator.app”

The VBA code to invoke the .sh from Excel on OSX is below:

在 OSX 上从 Excel 调用 .sh 的 VBA 代码如下：

Dim DesktopFolder As String
DesktopFolder = MacScript("return (path to desktop folder) as string")

Dim ScriptFile As String
ScriptFile = DesktopFolder & "test.sh"

RetVal = Shell(ScriptFile, vbNormalFocus)

I decided to check if its only functional on certain folders so I moved the .sh file to the /tmp folder and could not execute the file, simply got "file not found errors" no matter how I encoded the path. Finally realised that the /tmp file on OSX is a symbolic link that maps to /private/tmp and when I used that path it all seems to work fine again.

我决定检查它是否只在某些文件夹上起作用，所以我将 .sh 文件移动到 /tmp 文件夹并且无法执行该文件，无论我如何编码路径，都会得到“找不到文件的错误”。终于意识到 OSX 上的 /tmp 文件是一个映射到 /private/tmp 的符号链接，当我使用该路径时，它似乎又可以正常工作了。

Dim ScriptFile As String
ScriptFile = "Macintosh HD:private:tmp:test.sh"
RetVal = Shell(ScriptFile, vbNormalFocus)

Anyway, hope it helps a bit. I will continue looking for a better way, need something to dynamically find what the file location is reliably.

无论如何，希望它有点帮助。我将继续寻找更好的方法，需要一些东西来可靠地动态查找文件位置。

回答by Tom Kreutz

At first, Joel's excellent tutorial didn't work for me (on OS X 10.11.6 and Excel for Mac 2011). After some sleuthing, I discovered that my problem was caused by "extended attributes" in my script file, test.sh. In Terminal, ls -l test.shyields:

起初，Joel 的优秀教程对我不起作用（在 OS X 10.11.6 和 Excel for Mac 2011 上）。经过一番调查，我发现我的问题是由我的脚本文件 test.sh 中的“扩展属性”引起的。在终端中，ls -l test.sh产生：

  -rwxr-xr-x@ 1 tgk  staff  456 Mar 16 13:12 test.sh

Note that "@" indicates the presence of extended file attributes. More information is given by invoking ls -l@ test.sh, which yields:

请注意，“@”表示存在扩展文件属性。通过调用 提供更多信息ls -l@ test.sh，它产生：

-rwxr-xr-x@ 1 tgk  staff  456 Mar 16 13:12 test.sh
      com.apple.FinderInfo   32 
      com.apple.TextEncoding     15

In other words, depending on how the file was created, it may or may not have extended attributes. So I stripped off the extended attributes via xattr -c test.sh, and then Joel's call: Shell ActiveWorkbook.Path & ":test.sh"worked perfectly! Many thanks to all.

换句话说，根据文件的创建方式，它可能有也可能没有扩展属性。所以我通过 剥离了扩展属性xattr -c test.sh，然后乔尔的电话：Shell ActiveWorkbook.Path & ":test.sh"完美工作！非常感谢大家。

____

____

I later learned that saving the shell script without writing extended attributes is possible in TextWrangler by first invoking defaults write com.barebones.textwrangler WriteExtendedAttributes neverin Terminal. However, this only prevents the addition of the com.apple.FinderInfoattribute; the com.apple.TextEncodingattribute remains. Fortunately, however, Joel's VBA Script command now works when the shell script has just the com.apple.TextEncodingextended attribute! So, TextWrangler is the editor for me!

后来我了解到，通过首先defaults write com.barebones.textwrangler WriteExtendedAttributes never在终端中调用，可以在 TextWrangler 中保存 shell 脚本而不编写扩展属性。但是，这只会阻止添加com.apple.FinderInfo属性；该com.apple.TextEncoding属性仍然存在。然而，幸运的是，当 shell 脚本只有com.apple.TextEncoding扩展属性时，Joel 的 VBA 脚本命令现在可以工作了！所以，TextWrangler 是我的编辑器！

回答by MvG

I don't have MS Office for Mac installed, so I can't test the answer I propose. I suppose it should be possible to call Bash the binary (i.e. /bin/bash) and pass the name of the script you want to execute as an argument. This would avoid any issues with permission bits, and would ensure that the path name for the second argument is the usual Unix-style /-separated one. For the first argument, it might be enough to call bash by its name, without any path, as that should use the PATH environment variable.

我没有安装 MS Office for Mac，因此无法测试我提出的答案。我想应该可以将 Bash 称为二进制文件（即 /bin/bash）并将要执行的脚本的名称作为参数传递。这将避免权限位的任何问题，并确保第二个参数的路径名是通常的 Unix 样式 / 分隔的路径名。对于第一个参数，按名称调用 bash 可能就足够了，没有任何路径，因为它应该使用 PATH 环境变量。

All in all, and still without testing, I'd try this:

总而言之，仍然没有测试，我会试试这个：

Shell("bash runmybatch.sh")

Or, if that doesn't work due to incorrect working directory,

或者，如果由于不正确的工作目录而不起作用，

Shell("bash '" & location & "/runmybatch.sh'")

where location is the /-separated path to the directory of the workbook. The sngle quotes should take care of any spaces or other exotic characters in the path.

其中 location 是工作簿目录的 / 分隔路径。单引号应该处理路径中的任何空格或其他奇异字符。

回答by Christian

What you need to do is write the following command in your VBA?macro:

您需要做的是在您的 VBA? 宏中编写以下命令：

Shell "C:Path\to\script\location\script.sh"

(Remember to include double quotes surrounding the path)

（记得在路径周围加上双引号）

The best way to get your script location is to record a macro to save a workbook in the same folder where your bash script resides. That way you can later see how Excel calls that path and copy/paste it to the shell command above.

获取脚本位置的最佳方法是录制宏以将工作簿保存在 bash 脚本所在的同一文件夹中。这样您以后就可以看到 Excel 如何调用该路径并将其复制/粘贴到上面的 shell 命令。