如何通过Excel VBA向指定IP地址及端口发送字符串？

当然有办法，根据你要发送的是HTTP请求还是原始TCP字符串，有几种不同的实现方案：

方案1：用MSXML2.XMLHTTP60发送HTTP请求（支持IP+端口） #

你其实可以直接构造包含IP和端口的HTTP URL，格式为http://[IP地址]:[端口号]，如果对方服务是HTTP协议，这个方法最简单。示例代码：

Sub SendHttpString()
    Dim xmlHttp As Object
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP60")
    
    ' 替换为你的目标IP和端口
    Dim ipPort As String
    ipPort = "http://192.168.1.100:8080"
    ' 替换为要发送的字符串内容
    Dim sendStr As String
    sendStr = "Hello from Excel VBA"
    
    xmlHttp.Open "POST", ipPort, False
    xmlHttp.setRequestHeader "Content-Type", "text/plain"
    xmlHttp.send sendStr
    
    ' 可选：获取并检查响应状态
    If xmlHttp.Status = 200 Then
        MsgBox "发送成功，响应内容：" & xmlHttp.responseText
    Else
        MsgBox "发送失败，状态码：" & xmlHttp.Status
    End If
    Set xmlHttp = Nothing
End Sub

如果对方不是HTTP服务，这个方法不适用，需要用下面的套接字方案。

方案2：用Winsock控件发送原始TCP字符串 #

这个方法适合发送非HTTP的原始字符串，但注意32位/64位Excel兼容性：

1. 打开VBA编辑器，点击【工具】→【引用】，勾选Microsoft Winsock Control 6.0
2. 示例代码：

Sub SendTcpString()
    Dim ws As New Winsock
    ' 替换为目标IP和端口
    Dim targetIp As String
    targetIp = "192.168.1.100"
    Dim targetPort As Integer
    targetPort = 1234
    ' 替换为要发送的字符串
    Dim sendStr As String
    sendStr = "Raw TCP data from Excel"
    
    ' 连接目标服务器
    ws.Connect targetIp, targetPort
    
    ' 等待连接建立
    Do While ws.State <> sckConnected
        DoEvents
    Loop
    
    ' 发送字符串
    ws.SendData sendStr
    
    ' 等待发送完成后关闭连接
    Do While ws.State <> sckClosed
        DoEvents
    Loop
    
    MsgBox "发送完成"
    Set ws = Nothing
End Sub

注意：Winsock控件在64位Excel中可能无法正常使用，优先选方案3。

方案3：用Windows API实现TCP发送（兼容64位Excel） #

这种方式不需要额外控件，直接调用系统API，兼容性最好：

' 声明Windows API（自动适配32/64位Excel）
#If VBA7 Then
    Declare PtrSafe Function WSAStartup Lib "ws2_32.dll" (ByVal wVersionRequired As Long, lpWSAData As WSADATA) As Long
    Declare PtrSafe Function WSACleanup Lib "ws2_32.dll" () As Long
    Declare PtrSafe Function socket Lib "ws2_32.dll" (ByVal af As Long, ByVal sock_type As Long, ByVal protocol As Long) As LongPtr
    Declare PtrSafe Function connect Lib "ws2_32.dll" (ByVal s As LongPtr, ByRef name As sockaddr_in, ByVal namelen As Long) As Long
    Declare PtrSafe Function send Lib "ws2_32.dll" (ByVal s As LongPtr, ByVal buf As String, ByVal len As Long, ByVal flags As Long) As Long
    Declare PtrSafe Function closesocket Lib "ws2_32.dll" (ByVal s As LongPtr) As Long
#Else
    Declare Function WSAStartup Lib "ws2_32.dll" (ByVal wVersionRequired As Long, lpWSAData As WSADATA) As Long
    Declare Function WSACleanup Lib "ws2_32.dll" () As Long
    Declare Function socket Lib "ws2_32.dll" (ByVal af As Long, ByVal sock_type As Long, ByVal protocol As Long) As Long
    Declare Function connect Lib "ws2_32.dll" (ByVal s As Long, ByRef name As sockaddr_in, ByVal namelen As Long) As Long
    Declare Function send Lib "ws2_32.dll" (ByVal s As Long, ByVal buf As String, ByVal len As Long, ByVal flags As Long) As Long
    Declare Function closesocket Lib "ws2_32.dll" (ByVal s As Long) As Long
#End If

' 定义API所需的结构类型
Type WSADATA
    wVersion As Integer
    wHighVersion As Integer
    szDescription As String * 257
    szSystemStatus As String * 129
    iMaxSockets As Long
    iMaxUdpDg As Long
    lpVendorInfo As LongPtr
End Type

Type sockaddr_in
    sin_family As Integer
    sin_port As Integer
    sin_addr As Long
    sin_zero As String * 8
End Type

Sub SendTcpViaAPI()
    Dim wsaData As WSADATA
    Dim sock As LongPtr
    Dim sockAddr As sockaddr_in
    Dim targetIp As String
    Dim targetPort As Integer
    Dim sendStr As String
    Dim ret As Long
    
    ' 初始化Winsock环境
    If WSAStartup(&H202, wsaData) <> 0 Then
        MsgBox "Winsock初始化失败"
        Exit Sub
    End If
    
    ' 替换为目标IP、端口和发送内容
    targetIp = "192.168.1.100"
    targetPort = 1234
    sendStr = "API-based TCP data from Excel"
    
    ' 创建TCP套接字
    sock = socket(2, 1, 0) ' AF_INET=2, SOCK_STREAM=1, IPPROTO_TCP=0
    If sock = -1 Then
        MsgBox "创建套接字失败"
        WSACleanup
        Exit Sub
    End If
    
    ' 填充目标地址结构
    sockAddr.sin_family = 2
    sockAddr.sin_port = htons(targetPort) ' 转换端口为网络字节序
    sockAddr.sin_addr = inet_addr(targetIp) ' 转换IP为长整型
    
    ' 连接目标服务器
    ret = connect(sock, sockAddr, Len(sockAddr))
    If ret = -1 Then
        MsgBox "连接目标服务器失败"
        closesocket sock
        WSACleanup
        Exit Sub
    End If
    
    ' 发送数据
    ret = send(sock, sendStr, Len(sendStr), 0)
    If ret = -1 Then
        MsgBox "数据发送失败"
    Else
        MsgBox "成功发送 " & ret & " 字节数据"
    End If
    
    ' 清理资源
    closesocket sock
    WSACleanup
End Sub

' 辅助函数：将主机字节序端口转换为网络字节序
Function htons(ByVal hostShort As Integer) As Integer
    htons = (hostShort And &HFF) * 256 + (hostShort \ 256)
End Function

' 辅助函数：将IP字符串转换为长整型
Function inet_addr(ByVal ipStr As String) As Long
    Dim parts() As String
    parts = Split(ipStr, ".")
    inet_addr = CLng(parts(3)) * &H1000000 + CLng(parts(2)) * &H10000 + CLng(parts(1)) * &H100 + CLng(parts(0))
End Function

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