Office 2011 for Mac中的VBA Shell函数

oaxa6hgo  于 2022-11-16  发布在  Shell
关注(0)|答案(5)|浏览(176)

我正在尝试从Word 2011 for Mac中的VBA宏启动Shell脚本,该脚本将在终端窗口中运行。我已尝试同时使用Shell函数和**MacScript**函数,但VBA解释器似乎无法在这两种情况下找到该脚本。
根据VBA指涉文件,下列程式应该可以运作:

RetVal = Shell("Macintosh HD:Applications:Calculator.app", vbNormalFocus)

这会产生运行时错误53“找不到文件”。
有什么建议吗?

zfciruhq

zfciruhq1#

Mac上的Shell() VBA函数似乎需要完整路径作为HFS样式的路径(使用冒号而不是斜杠)。它似乎也不像Windows上那样接受参数(如果添加任何参数,则报告“找不到路径”错误)。
也可以使用MacScript() VBA函数:MacScript("do shell script ""command""")。这可能是最简单的选择,也是我建议做的。缺点是它有相当多的开销(每次调用100- 200 ms)。
另一个替代方法是标准C库中的system()函数:

Private Declare Function system Lib "libc.dylib" (ByVal command As String) As Long

Sub RunSafari()
    Dim result As Long
    result = system("open -a Safari --args http://www.google.com")
    Debug.Print Str(result)
End Sub

有关文档,请参见http://pubs.opengroup.org/onlinepubs/009604499/functions/system.html
system()只返回退出代码。如果要从命令中获取输出,可以使用popen()

Private Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As Long
Private Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As Long
Private Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long
Private Declare Function feof Lib "libc.dylib" (ByVal file As Long) As Long

Function execShell(command As String, Optional ByRef exitCode As Long) As String
    Dim file As Long
    file = popen(command, "r")

    If file = 0 Then
        Exit Function
    End If

    While feof(file) = 0
        Dim chunk As String
        Dim read As Long
        chunk = Space(50)
        read = fread(chunk, 1, Len(chunk) - 1, file)
        If read > 0 Then
            chunk = Left$(chunk, read)
            execShell = execShell & chunk
        End If
    Wend

    exitCode = pclose(file)
End Function

Sub RunTest()
    Dim result As String
    Dim exitCode As Long
    result = execShell("echo Hello World", exitCode)
    Debug.Print "Result: """ & result & """"
    Debug.Print "Exit Code: " & str(exitCode)
End Sub

请注意,上例中的几个Long参数是指针,因此如果发布了64位版本的Mac Word,则必须对其进行更改。

blmhpbnm

blmhpbnm2#

希望您现在已经找到了答案,但您只需要完整的路径:

RetVal = Shell("Macintosh HD:Applications:Calculator.app:" & _
              "Contents:MacOS:Calculator", vbNormalFocus)

另一个例子如下:

RetVal = Shell("Macintosh HD:Users:brownj:Documents:" & _
              "rnaseq:IGV_2.0.14:igv.sh", vbNormalFocus)
6jygbczu

6jygbczu3#

另一个问题恰恰是这样出现的:由于AppleScript环境和用户的bash环境之间的差异,权限会导致脚本失败。这篇问答帮助我解决了这个问题。为了让我的脚本工作,我必须解决一些路径和权限问题(不是脚本本身,而是脚本所涉及的内容)。
以下是我的建议,希望它能在故障诊断过程中提供比使用“AppleScript编辑器”之前看到的无意义的Excel错误更好的见解:

  • 使用“AppleScript编辑器”来确认脚本实际上以任何用户的身份工作,并与所使用的任何环境变量配合使用:

1.在Spotlight中,开始键入“applescript editor”,直到它出现,然后单击它
1.创建新的“AppleScript编辑器”文件
1.在新文件中键入简单的脚本,* 不要 * 使用双引号-我的是这样写的

do shell script "parseCsvAndOpen.sh"

1.点击脚本的“运行”按钮

  • 跟踪任何问题,进行更改,然后重复点击“运行”按钮,直到您可以从“AppleScript编辑器”中执行它
  • 这里的好消息是,如果您需要返回StackOverflow或Google寻求帮助,您的搜索范围会更窄;- )

1.现在,将简单脚本从“AppleScript编辑器”复制到vba,并确认它仍然有效
1.我可以将双引号加上双引号,放在MacScript代码后面:

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

这确实是一个,两个,然后三个双引号字符!(可能是转义双引号)

czq61nw1

czq61nw14#

对于任何使用64位Office的人,这里是Robert的代码更新,以兼容32和64位版本。注意,由于沙箱的变化,到libc的完整路径必须在声明中使用,否则你会结束一个错误53“文件找不到”在从来没有版本的OSX/Office。

#If Mac Then
  #If VBA7 Then
    ' 64 bit Office:mac
    Private Declare PtrSafe Function popen Lib "/usr/lib/libc.dylib" (ByVal command As String, ByVal mode As String) As LongPtr
    Private Declare PtrSafe Function pclose Lib "/usr/lib/libc.dylib" (ByVal file As LongPtr) As Long
    Private Declare PtrSafe Function fread Lib "/usr/lib/libc.dylib" (ByVal outStr As String, ByVal size As LongPtr, ByVal items As LongPtr, ByVal stream As LongPtr) As Long
    Private Declare PtrSafe Function feof Lib "/usr/lib/libc.dylib" (ByVal file As LongPtr) As LongPtr
    Private file As LongPtr
  #Else
    ' 32 bit Office:mac
    Private Declare Function popen Lib "libc.dylib" (ByVal command As String, ByVal mode As String) As Long
    Private Declare Function pclose Lib "libc.dylib" (ByVal file As Long) As Long
    Private Declare Function fread Lib "libc.dylib" (ByVal outStr As String, ByVal size As Long, ByVal items As Long, ByVal stream As Long) As Long
    Private Declare Function feof Lib "libc.dylib" (ByVal file As Long) As Long
    Private file As Long
  #End If

  Public Function execShell(command As String, Optional ByRef exitCode As Long) As String
    file = popen(command, "r")
    
    If file = 0 Then
      Exit Function
    End If
    
    While feof(file) = 0
      Dim chunk As String
      Dim read As Long
      chunk = SPACE(50)
      read = fread(chunk, 1, Len(chunk) - 1, file)
      If read > 0 Then
        chunk = Left$(chunk, read)
        execShell = execShell & chunk
      End If
    Wend
    
    exitCode = pclose(file) ' 0 = success
  End Function

#End If
oalqel3c

oalqel3c5#

只是没有足够的积分来发表评论,但觉得这是对罗宾骑士回答的补充。学分当然仍然是给他的。
对于Excel 15.18(2015),标准C库中system()函数中使用的open调用不再需要--args关键字:

Private Declare Function system Lib "libc.dylib" (ByVal command As String) As Long

Sub RunSafari()
    Dim result As Long
    result = system("open -a Safari http://www.google.com")
    Debug.Print Str(result)
End Sub

这个很好用。没有在2011年测试过。

相关问题