Windows 10下Excel VBA调用findstr消除DOS弹窗并读取结果

方案一：纯VBA实现文本搜索（无外部窗口） #

直接用VBA内置功能读取文本文件并完成不区分大小写的搜索，完全避免调用外部命令，彻底消除DOS窗口。替换原代码为以下内容：

Sub SearchUserInEmployeeList()
    Dim targetUser As String
    Dim employeeFile As String
    Dim fileHandle As Integer
    Dim currentLine As String
    Dim matchedLines As Collection
    Dim line As Variant
    
    ' 赋值与原代码一致的变量
    targetUser = USERNAME
    employeeFile = ACTIVEEMPLOYEESLISTFILE
    
    Set matchedLines = New Collection
    
    ' 打开目标文本文件
    fileHandle = FreeFile
    Open employeeFile For Input As #fileHandle
    
    ' 逐行读取并匹配目标内容
    Do Until EOF(fileHandle)
        Line Input #fileHandle, currentLine
        ' vbTextCompare 参数实现不区分大小写匹配
        If InStr(1, currentLine, targetUser, vbTextCompare) > 0 Then
            matchedLines.Add currentLine
        End If
    Loop
    
    ' 关闭文件
    Close #fileHandle
    
    ' 逐个显示匹配结果
    For Each line In matchedLines
        MsgBox line
    Next line
End Sub

优点 #

• 无任何外部窗口弹出
• 纯VBA实现，无需依赖系统命令
• 代码逻辑清晰，易维护调试

方案二：调用findstr但隐藏DOS窗口（保留外部命令） #

如果必须依赖findstr的特殊功能，可通过Windows API创建隐藏进程并捕获输出，无需写入临时文件：

Option Explicit

Private Type STARTUPINFO
    cb As Long
    lpReserved As String
    lpDesktop As String
    lpTitle As String
    dwX As Long
    dwY As Long
    dwXSize As Long
    dwYSize As Long
    dwXCountChars As Long
    dwYCountChars As Long
    dwFillAttribute As Long
    dwFlags As Long
    wShowWindow As Integer
    cbReserved2 As Integer
    lpReserved2 As Byte
    hStdInput As Long
    hStdOutput As Long
    hStdError As Long
End Type

Private Type PROCESS_INFORMATION
    hProcess As Long
    hThread As Long
    dwProcessId As Long
    dwThreadId As Long
End Type

Private Declare Function CreateProcess Lib "kernel32" Alias "CreateProcessA" ( _
    ByVal lpApplicationName As String, _
    ByVal lpCommandLine As String, _
    lpProcessAttributes As Any, _
    lpThreadAttributes As Any, _
    ByVal bInheritHandles As Long, _
    ByVal dwCreationFlags As Long, _
    lpEnvironment As Any, _
    ByVal lpCurrentDirectory As String, _
    lpStartupInfo As STARTUPINFO, _
    lpProcessInformation As PROCESS_INFORMATION) As Long

Private Declare Function ReadFile Lib "kernel32" ( _
    ByVal hFile As Long, _
    ByVal lpBuffer As String, _
    ByVal nNumberOfBytesToRead As Long, _
    lpNumberOfBytesRead As Long, _
    lpOverlapped As Any) As Long

Private Declare Function CreatePipe Lib "kernel32" ( _
    phReadPipe As Long, _
    phWritePipe As Long, _
    lpPipeAttributes As Any, _
    ByVal nSize As Long) As Long

Private Declare Function CloseHandle Lib "kernel32" ( _
    ByVal hObject As Long) As Long

Private Declare Function GetExitCodeProcess Lib "kernel32" ( _
    ByVal hProcess As Long, _
    lpExitCode As Long) As Long

Private Const STARTF_USESTDHANDLES = &H100
Private Const STARTF_USESHOWWINDOW = &H1
Private Const SW_HIDE = 0
Private Const NORMAL_PRIORITY_CLASS = &H20
Private Const INFINITE = &HFFFFFFFF

Sub FindStrHidden()
    Dim cmdLine As String
    Dim si As STARTUPINFO
    Dim pi As PROCESS_INFORMATION
    Dim hReadPipe As Long, hWritePipe As Long
    Dim buffer As String
    Dim bytesRead As Long
    Dim output As String
    
    ' 构造带引号的命令行，避免路径含空格出错
    cmdLine = "cmd.exe /c findstr /I /P """ & USERNAME & """ """ & ACTIVEEMPLOYEESLISTFILE & """"
    
    ' 创建管道用于捕获命令输出
    CreatePipe hReadPipe, hWritePipe, ByVal 0&, 0
    
    ' 设置进程启动参数：隐藏窗口+重定向输出
    With si
        .cb = Len(si)
        .dwFlags = STARTF_USESHOWWINDOW Or STARTF_USESTDHANDLES
        .wShowWindow = SW_HIDE
        .hStdOutput = hWritePipe
        .hStdError = hWritePipe
    End With
    
    ' 创建隐藏进程执行命令
    If CreateProcess(vbNullString, cmdLine, ByVal 0&, ByVal 0&, 1, NORMAL_PRIORITY_CLASS, ByVal 0&, vbNullString, si, pi) Then
        CloseHandle hWritePipe
        
        ' 读取管道中的输出内容
        buffer = Space$(4096)
        Do
            ReadFile hReadPipe, buffer, Len(buffer), bytesRead, ByVal 0&
            output = output & Left$(buffer, bytesRead)
        Loop While bytesRead > 0
        
        ' 等待进程结束并清理系统句柄
        WaitForSingleObject pi.hProcess, INFINITE
        CloseHandle pi.hProcess
        CloseHandle pi.hThread
        CloseHandle hReadPipe
        
        ' 分割输出行并显示结果
        Dim lines() As String
        lines = Split(output, vbCrLf)
        Dim i As Integer
        For i = 0 To UBound(lines)
            If lines(i) <> "" Then
                MsgBox lines(i)
            End If
        Next i
    End If
End Sub

Private Declare Function WaitForSingleObject Lib "kernel32" ( _
    ByVal hHandle As Long, _
    ByVal dwMilliseconds As Long) As Long

说明 #

• 通过Windows API创建完全隐藏的进程执行findstr
• 使用管道捕获命令输出，全程无需写入临时文件
• 代码复杂度较高，适合必须依赖findstr特殊功能的场景

内容的提问来源于stack exchange，提问作者bgr