Excel VBA : Communicating via named pipe

Question

我正在尝试通过 named pipe 设置通信不幸的是，由于某种原因，在 VBA 中它永远不会到达 Debug.Print "Connected 行。在服务器中，客户端也没有连接。似乎是一个简单的场景，但已经尝试了好几个小时。

服务器

Public Sub Server()
      Const szPipeName = "\\.\pipe\bigtest"
      Dim hPipe As Long, readVal As Long, readBytes As Long, sendVal As Long, sentBytes As Long
      Dim sa As SECURITY_ATTRIBUTES

      'Create the NULL security token for the pipe
      pSD = GlobalAlloc(GPTR, SECURITY_DESCRIPTOR_MIN_LENGTH)
      res = InitializeSecurityDescriptor(pSD, SECURITY_DESCRIPTOR_REVISION)
      res = SetSecurityDescriptorDacl(pSD, -1, 0, 0)
      sa.nLength = LenB(sa)
      sa.lpSecurityDescriptor = pSD
      sa.bInheritHandle = True

      'Create the Named Pipe
      hPipe = CreateNamedPipe(szPipeName, PIPE_ACCESS_DUPLEX, PIPE_WAIT Or PIPE_TYPE_MESSAGE Or PIPE_READMODE_MESSAGE, 10, 1000, 1000, 10000, sa)

      'Create separate thread as client
      ID = CreateThread(nil, 0, AddressOf ClientThread, nil, 0, nil)

      Debug.Print "Created thread: " & ID
      Debug.Print "Connecting named pipe: " & hPipe

      res = ConnectNamedPipe(hPipe, ByVal 0)
      'XXXXXXXXXXXXXXXXX NEVER GETS HERE XXXXXXXXXXXXXXXXXXx
      Debug.Print "Connected"
      'Read/Write data over the pipe

      res = ReadFile(hPipe, readVal, LenB(readVal), readBytes, ByVal 0)
      Debug.Print "Read file: " & readVal

      'res = WriteFile(hPipe, sendVal , LenB(sendVal ), sendBytes, ByVal 0)
      res = FlushFileBuffers(hPipe)
      res = DisconnectNamedPipe(hPipe)

      'Close the pipe handle
      CloseHandle hPipe
      GlobalFree (pSD)

   End Sub

客户

Public Sub ClientThread()
      Const szPipeName As String = "\\.\pipe\bigtest"
      Dim sentBytes As Long, sendVal As Long, fSuccess As Boolean, readVal As Long, readBytes As Long

      sendVal = 500

      'Give server time to ConnectNamedPipe
      Sleep 2000
      Debug.Print "Connecting to pipe..."
      fSuccess= CallNamedPipe(szPipeName, sendVal, LenB(sendVal), readVal, LenB(readVal), readBytes, 5000)
      'XXXXXXXXXXXXXX NEVER GETS HERE XXXXXXXXXXXXXX
      Debug.Print "Successful: " & fSuccess

      '...
   End Sub

Answer 1

(这是我的第一个答案——是时候回馈了)
让我给你看一个这个工作的例子。在下面的示例中，我将为您提供一个函数 Excel 向服务器发送消息、服务器读取消息、服务器编写响应以及接收响应的 VBA 宏模块代码。我们将使用作为字符串的消息来执行此操作，但您可以更进一步，并创建一个您希望在此管道上使用的协议(protocol)。
请记住，Excel 宏是单线程和事件驱动的——因此在 excel 中使用命名管道的方式是作为客户端——响应事件，向服务器发送请求并接收来自该服务器的响应服务器(很像 Web GET 或 POST 发送请求并获得响应然后关闭连接)
在这个例子中，我们使用了 kernel32 中的 CallNamedPipeA 方法
(注意这是为 64 位 excel 编写的。如果使用 32 位，则排除“PtrSafe”) 此方法以消息模式连接到命名管道，发送消息，接收消息，然后关闭连接。
c# 服务器代码主要取自 Microsoft 示例，但我们必须将其转换为处理消息模式，并在每个管道关闭时让它重新启动一个新线程。因此总是有 4 个线程等待客户端连接。
关于字符串的快速说明。请记住 Excel 使用 Unicode。我们应该在命名管道上使用 UTF8 或字节数组。
在 Excel 中调用子 testPipe 来查看这项工作。
Excel VBA(客户端)

Option Explicit

Declare PtrSafe Function CallNamedPipe Lib "kernel32" Alias _
    "CallNamedPipeA" ( _
    ByVal lpNamedPipeName As String, _
    lpInBuffer As Any, _
    ByVal nInBufferSize As Long, _
    lpOutBuffer As Any, _
    ByVal nOutBufferSize As Long, _
    lpBytesRead As Long, _
    ByVal nTimeOut As Long) As Long

Private Sub testPipe()
    Dim ms As String
    Dim mr As String
    Dim returncode As Long
    
    ms = "Message from client;Hello World"

    returncode = namedPipeMessageExchange("testpipe", ms, mr)
    
    If returncode <> 0 Then
        Debug.Print "Sent:     " & ms
        Debug.Print "received: " & mr
    End If
    
End Sub

Public Function namedPipeMessageExchange(pipe As String, messageToSend As String, messageReceived As String) As Long
    Dim res As Long, myStr As String, i As Long, cbRead As Long, sm As String
    Dim numBytes As Long, bArray() As Byte, temp As String

    Dim b() As Byte
    Dim blen As Long
    b = StrConv(messageToSend, vbFromUnicode)
    blen = UBound(b) - LBound(b) + 1
    If blen = 0 Then b = Array(1)


    numBytes = 1000000
    ReDim bArray(numBytes)  'Build the return buffer

    'Call CallNamedPipe to do the transaction all at once
    res = CallNamedPipe("\\.\pipe\" + pipe, b(0), blen, _
        bArray(0), numBytes, _
        cbRead, 3000)  'Wait up to 3 seconds for a response

    If res > 0 Then
        ReDim Preserve bArray(0 To cbRead - 1)
        messageReceived = StrConv(bArray, vbUnicode)
        'Debug.Print "received: " & messageReceived
    Else
        Debug.Print "Error number " & Err.LastDllError & _
            " attempting to call CallNamedPipe.", vbOKOnly
    End If
    
    namedPipeMessageExchange = res
End Function

c# (服务器)

using System;
using System.Collections.Generic;
using System.IO;
using System.IO.Pipes;
using System.Text;
using System.Threading;

public class PipeServer
{
    private static int numThreads = 4;

    public static void Main()
    {
        int i;
        Thread[] servers = new Thread[numThreads];

        Console.WriteLine("\n*** Named pipe server stream with impersonation example ***\n");
        Console.WriteLine("Waiting for client connect...\n");
        for (i = 0; i < numThreads; i++)
        {
            servers[i] = new Thread(ServerThread);
            servers[i].Start();
        }
        Thread.Sleep(250);
        while (i > 0)
        {
            for (int j = 0; j < numThreads; j++)
            {
                if (servers[j] != null)
                {
                    if (servers[j].Join(250))
                    {
                        Console.WriteLine("Server thread[{0}] finished.", servers[j].ManagedThreadId);
                        servers[j] = null;
                        servers[j] = new Thread(ServerThread);
                        servers[j].Start();
                        //i--;    // decrement the thread watch count
                    }
                }
            }
        }
        Console.WriteLine("\nServer threads exhausted, exiting.");
    }

    private static void ServerThread(object data)
    {
        NamedPipeServerStream pipeServer =
            new NamedPipeServerStream("testpipe", PipeDirection.InOut, numThreads, PipeTransmissionMode.Message);

        int threadId = Thread.CurrentThread.ManagedThreadId;

        // Wait for a client to connect
        pipeServer.WaitForConnection();

        Console.WriteLine("Client connected on thread[{0}].", threadId);
        try
        {


            List<byte> intext = new List<byte>();
            do
            {
                byte[] x = new byte[1024*16];
                int read = 0;
                read = pipeServer.Read(x);
                Array.Resize(ref x, read);
                intext.AddRange(x);

            } while (!pipeServer.IsMessageComplete);
            
            string receivedText = System.Text.Encoding.UTF8.GetString(intext.ToArray());

            string sentText = "I am the server!";
            pipeServer.Write(System.Text.Encoding.UTF8.GetBytes(sentText));
            
            Console.WriteLine("Received Text: "+receivedText);
            Console.WriteLine("Sent Text:     " + sentText);

        }
        // Catch the IOException that is raised if the pipe is broken
        // or disconnected.
        catch (IOException e)
        {
            Console.WriteLine("ERROR: {0}", e.Message);
        }
        //pipeServer.WaitForPipeDrain();
        pipeServer.Close();
    }
}