- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
在 http://www.cpearson.com/excel/ShellAndWait.aspx 上找到此功能
但我还需要捕获 shell 的输出。任何代码建议?
Option Explicit
Option Compare Text
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' modShellAndWait
' By Chip Pearson, chip@cpearson.com, www.cpearson.com
' This page on the web site: www.cpearson.com/Excel/ShellAndWait.aspx
' 9-September-2008
'
' This module contains code for the ShellAndWait function that will Shell to a process
' and wait for that process to end before returning to the caller.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Declare Function WaitForSingleObject Lib "kernel32" ( _
ByVal hHandle As Long, _
ByVal dwMilliseconds As Long) As Long
Private Declare Function OpenProcess Lib "kernel32.dll" ( _
ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long
Private Declare Function CloseHandle Lib "kernel32" ( _
ByVal hObject As Long) As Long
Private Const SYNCHRONIZE = &H100000
Public Enum ShellAndWaitResult
Success = 0
Failure = 1
TimeOut = 2
InvalidParameter = 3
SysWaitAbandoned = 4
UserWaitAbandoned = 5
UserBreak = 6
End Enum
Public Enum ActionOnBreak
IgnoreBreak = 0
AbandonWait = 1
PromptUser = 2
End Enum
Private Const STATUS_ABANDONED_WAIT_0 As Long = &H80
Private Const STATUS_WAIT_0 As Long = &H0
Private Const WAIT_ABANDONED As Long = (STATUS_ABANDONED_WAIT_0 + 0)
Private Const WAIT_OBJECT_0 As Long = (STATUS_WAIT_0 + 0)
Private Const WAIT_TIMEOUT As Long = 258&
Private Const WAIT_FAILED As Long = &HFFFFFFFF
Private Const WAIT_INFINITE = -1&
Public Function ShellAndWait(ShellCommand As String, _
TimeOutMs As Long, _
ShellWindowState As VbAppWinStyle, _
BreakKey As ActionOnBreak) As ShellAndWaitResult
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ShellAndWait
'
' This function calls Shell and passes to it the command text in ShellCommand. The function
' then waits for TimeOutMs (in milliseconds) to expire.
'
' Parameters:
' ShellCommand
' is the command text to pass to the Shell function.
'
' TimeOutMs
' is the number of milliseconds to wait for the shell'd program to wait. If the
' shell'd program terminates before TimeOutMs has expired, the function returns
' ShellAndWaitResult.Success = 0. If TimeOutMs expires before the shell'd program
' terminates, the return value is ShellAndWaitResult.TimeOut = 2.
'
' ShellWindowState
' is an item in VbAppWinStyle specifying the window state for the shell'd program.
'
' BreakKey
' is an item in ActionOnBreak indicating how to handle the application's cancel key
' (Ctrl Break). If BreakKey is ActionOnBreak.AbandonWait and the user cancels, the
' wait is abandoned and the result is ShellAndWaitResult.UserWaitAbandoned = 5.
' If BreakKey is ActionOnBreak.IgnoreBreak, the cancel key is ignored. If
' BreakKey is ActionOnBreak.PromptUser, the user is given a ?Continue? message. If the
' user selects "do not continue", the function returns ShellAndWaitResult.UserBreak = 6.
' If the user selects "continue", the wait is continued.
'
' Return values:
' ShellAndWaitResult.Success = 0
' indicates the the process completed successfully.
' ShellAndWaitResult.Failure = 1
' indicates that the Wait operation failed due to a Windows error.
' ShellAndWaitResult.TimeOut = 2
' indicates that the TimeOutMs interval timed out the Wait.
' ShellAndWaitResult.InvalidParameter = 3
' indicates that an invalid value was passed to the procedure.
' ShellAndWaitResult.SysWaitAbandoned = 4
' indicates that the system abandoned the wait.
' ShellAndWaitResult.UserWaitAbandoned = 5
' indicates that the user abandoned the wait via the cancel key (Ctrl+Break).
' This happens only if BreakKey is set to ActionOnBreak.AbandonWait.
' ShellAndWaitResult.UserBreak = 6
' indicates that the user broke out of the wait after being prompted with
' a ?Continue message. This happens only if BreakKey is set to
' ActionOnBreak.PromptUser.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim TaskID As Long
Dim ProcHandle As Long
Dim WaitRes As Long
Dim Ms As Long
Dim MsgRes As VbMsgBoxResult
Dim SaveCancelKey As XlEnableCancelKey
Dim ElapsedTime As Long
Dim Quit As Boolean
Const ERR_BREAK_KEY = 18
Const DEFAULT_POLL_INTERVAL = 500
If Trim(ShellCommand) = vbNullString Then
ShellAndWait = ShellAndWaitResult.InvalidParameter
Exit Function
End If
If TimeOutMs < 0 Then
ShellAndWait = ShellAndWaitResult.InvalidParameter
Exit Function
ElseIf TimeOutMs = 0 Then
Ms = WAIT_INFINITE
Else
Ms = TimeOutMs
End If
Select Case BreakKey
Case AbandonWait, IgnoreBreak, PromptUser
' valid
Case Else
ShellAndWait = ShellAndWaitResult.InvalidParameter
Exit Function
End Select
Select Case ShellWindowState
Case vbHide, vbMaximizedFocus, vbMinimizedFocus, vbMinimizedNoFocus, vbNormalFocus, vbNormalNoFocus
' valid
Case Else
ShellAndWait = ShellAndWaitResult.InvalidParameter
Exit Function
End Select
On Error Resume Next
Err.Clear
TaskID = Shell(ShellCommand, ShellWindowState)
If (Err.Number <> 0) Or (TaskID = 0) Then
ShellAndWait = ShellAndWaitResult.Failure
Exit Function
End If
ProcHandle = OpenProcess(SYNCHRONIZE, False, TaskID)
If ProcHandle = 0 Then
ShellAndWait = ShellAndWaitResult.Failure
Exit Function
End If
On Error GoTo ErrH:
SaveCancelKey = Application.EnableCancelKey
Application.EnableCancelKey = xlErrorHandler
WaitRes = WaitForSingleObject(ProcHandle, DEFAULT_POLL_INTERVAL)
Do Until WaitRes = WAIT_OBJECT_0
DoEvents
Select Case WaitRes
Case WAIT_ABANDONED
' Windows abandoned the wait
ShellAndWait = ShellAndWaitResult.SysWaitAbandoned
Exit Do
Case WAIT_OBJECT_0
' Successful completion
ShellAndWait = ShellAndWaitResult.Success
Exit Do
Case WAIT_FAILED
' attach failed
ShellAndWait = ShellAndWaitResult.Failure
Exit Do
Case WAIT_TIMEOUT
' Wait timed out. Here, this time out is on DEFAULT_POLL_INTERVAL.
' See if ElapsedTime is greater than the user specified wait
' time out. If we have exceed that, get out with a TimeOut status.
' Otherwise, reissue as wait and continue.
ElapsedTime = ElapsedTime + DEFAULT_POLL_INTERVAL
If Ms > 0 Then
' user specified timeout
If ElapsedTime > Ms Then
ShellAndWait = ShellAndWaitResult.TimeOut
Exit Do
Else
' user defined timeout has not expired.
End If
Else
' infinite wait -- do nothing
End If
' reissue the Wait on ProcHandle
WaitRes = WaitForSingleObject(ProcHandle, DEFAULT_POLL_INTERVAL)
Case Else
' unknown result, assume failure
ShellAndWait = ShellAndWaitResult.Failure
Exit Do
Quit = True
End Select
Loop
CloseHandle ProcHandle
Application.EnableCancelKey = SaveCancelKey
Exit Function
ErrH:
Debug.Print "ErrH: Cancel: " & Application.EnableCancelKey
If Err.Number = ERR_BREAK_KEY Then
If BreakKey = ActionOnBreak.AbandonWait Then
CloseHandle ProcHandle
ShellAndWait = ShellAndWaitResult.UserWaitAbandoned
Application.EnableCancelKey = SaveCancelKey
Exit Function
ElseIf BreakKey = ActionOnBreak.IgnoreBreak Then
Err.Clear
Resume
ElseIf BreakKey = ActionOnBreak.PromptUser Then
MsgRes = MsgBox("User Process Break." & vbCrLf & _
"Continue to wait?", vbYesNo)
If MsgRes = vbNo Then
CloseHandle ProcHandle
ShellAndWait = ShellAndWaitResult.UserBreak
Application.EnableCancelKey = SaveCancelKey
Else
Err.Clear
Resume Next
End If
Else
CloseHandle ProcHandle
Application.EnableCancelKey = SaveCancelKey
ShellAndWait = ShellAndWaitResult.Failure
End If
Else
' some other error. assume failure
CloseHandle ProcHandle
ShellAndWait = ShellAndWaitResult.Failure
End If
Application.EnableCancelKey = SaveCancelKey
End Function
最佳答案
根据 Andrew Lessard 的回答,这是一个运行命令并将输出作为字符串返回的函数 -
Public Function ShellRun(sCmd As String) As String
'Run a shell command, returning the output as a string
Dim oShell As Object
Set oShell = CreateObject("WScript.Shell")
'run command
Dim oExec As Object
Dim oOutput As Object
Set oExec = oShell.Exec(sCmd)
Set oOutput = oExec.StdOut
'handle the results as they are written to and read from the StdOut object
Dim s As String
Dim sLine As String
While Not oOutput.AtEndOfStream
sLine = oOutput.ReadLine
If sLine <> "" Then s = s & sLine & vbCrLf
Wend
ShellRun = s
End Function
MsgBox ShellRun("dir c:\")
关于vba - 从 VBA 中的 shell 命令捕获输出值?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/2784367/
我有一个 VBA 脚本,可以将数据从一张表复制到另一张表。复制的数据被放入公式中,计算出的数量被复制回原始工作表。我正在尝试获取它,以便 VBA 脚本为每一行执行此操作。我有 1000 行数据。 Su
如何让 excel 在我的“临时”表上列出所有可用的环境变量?下面的代码没有为我返回任何东西...... Sub ListEnvironVariables() Dim strEnviron A
好的,这就是我想要完成的事情:我正在尝试将所有 VBA 代码从“Sheet2”复制到“Sheet 3”代码 Pane 。我不是指将模块从一个模块复制到另一个模块,而是指 Excel 工作表对象代码。
我正在做一个项目来使用 rule-triggered 处理一些传入的 Outlook 邮件。 VBA 代码。 但是,我不想在代码需要更改的任何时候手动更新每个用户收件箱的代码。所以我的想法是把一个文本
我想从另一个代码 VBA 中评论包含 Msg Box 的行。我正在尝试使用 Library VBA EXTENSIBILITY,但我没有找到解决方案。 欢迎任何帮助。 这是我的代码: Sub Comm
我正在尝试编写程序的最后一部分,我需要从 Access 文档中提取数据并将其打印到新的工作簿中。 首先,我将获取产品供应商的名称并创建一个包含每个供应商名称的工作表,然后我想遍历每个工作表并打印每个供
我有一个要求,我试图查找数据中的日期是否大于或等于当前日期,那么它应该显示"is"。 这是我的代码, RDate = Application.WorksheetFunction.if(RSDate>=
我试图想出一个宏来检查单元格中是否存在任何数字值。如果存在数字值,请复制该行的一部分并将其粘贴到同一电子表格内的另一个工作表中。 Sheet1 是包含我所有数据的工作表。我正在尝试查看 R 列中是否有
我有一个具有密码保护(防止未经授权访问宏)的 VBA 宏,它按预期运行。用户单击按钮,宏运行。内容大致如下: Sub sample() ActiveSheet.Unprotect Pass
我想通过VBA删除工作表中包含的VBA代码。目前,我有一个代码可以将工作表复制到新工作簿并从中删除所有图像。但是,这些图像被设置为在代码中的 Worksheet_Activate 上执行操作,每当我轻
我有一个 vba 代码,它指定要查看的特定工作表名称,例如工作表 2, 但是,如果有人忘记将工作表名称更改为sheet2,我可以添加一段动态代码来自动更改调用工作表名称的vba代码吗?例如,从左边算起
VBAExcel 2016 如果执行某些代码后该范围的列数较少,我将尝试动态调整该范围的大小。引用了 MS 文件和各种在线示例,但没有成功。 https://msdn.microsoft.com/en
我在任何地方都找不到这个问题。在 Visual Basic (excel) 中,我可以按 F8 并循环浏览每一行。但是假设我想开始子程序,然后在执行前两行之后,我想跳到第 200 行。到目前为止,我一
这是我昨天的问题的补充,所以我开始一个新问题。基本上,我在 excel 的工作表上得到不同范围的数据,并且数据范围每周都不同,因此最后使用的列和最后使用的行会有所不同。 我想根据名称合并第 3 行和第
我的想法是创建一个函数来传递这样的双数组: Function pass(a() As Double, b() as double) As Boolean Dim i As Integer, j As
我正在使用 vlookup 运行 VBA 代码,但是,它需要几秒钟才能完成,尽管具有行的工作表只有不到 150 行。 滞后主要出现在 col 23 的生成期间。 包含此代码的主工作表有大约 2300
我在 VBA 中有一个小问题,我想将 Range 函数的行和列以 String 格式放置,如下所示: debut = "BH" & LTrim(Str(i)) fin = "DB" &
我正在尝试使用 Visual Basic 编写 Webcrawler。我有一个包含链接的列表,存储在 Excel 中(第 1 列)。然后宏应打开每个链接并将网站中的某些信息添加到 excel 文件中。
我正在尝试自动生成报告(请原谅我缺乏 Excel 经验),但遇到了这个错误。在单元格中显示#NAME。代码应为工作簿另一页上的所有列 E 选择单元格和 COUNTIF <1。这是一个简单的语法错误吗?
我正在使用“Sheet1”上的命令按钮使用 VBA 创建图表,但是该图表正在添加到另一个工作表(“Sheet2”)。 添加图表后,我使用以下代码根据 DataLabel 值对条形图进行着色并更改 Da
我是一名优秀的程序员,十分优秀!