Create client socket and read from it, using nothing but Excel and VBA.

I haven't used sockets from VBA...yet...but have using VB. The code should not be too different. I set mine up with a class and a form to contain the WinSock control. I have not tried to do this without the control on a form (using CreateObject) but that is something you might try.

This is the main class I use to set up the pipe:

' Requires frmInterApplicationPipe.

Option Explicit

Public Port As Long

Public Event DataArrival( _
      ByVal Message As String _
   )

Private mInterApplicationPipeForm As frmInterApplicationPipe
Private WithEvents mWinSock As Winsock

Public Sub BindToLocalPort( _
      Optional ByVal Port As Long, _
      Optional ByVal IP As String _
   )
   
   Dim Result As Long

   If Len(IP) = 0 Then IP = "127.0.0.1"
   ClosePort
   If Port = 0 Then
      On Error Resume Next
      mWinSock.Bind LocalIP:=IP
      Result = Err.LastDllError
      On Error GoTo 0
      'If Result <> 0 Then MsgBox "BTLP1:" & Result
   Else
      On Error Resume Next
      mWinSock.Bind LocalPort:=Port, LocalIP:=IP
      Result = Err.LastDllError
      On Error GoTo 0
      'If Result <> 0 Then MsgBox "BTLP2:" & Result
   End If
   Me.Port = mWinSock.LocalPort

End Sub

Public Sub BindToRemotePort( _
      ByVal Port As Long, _
      Optional ByVal IP As String _
   )
   
   Dim Result As Long
   
   If Len(IP) = 0 Then IP = "127.0.0.1"
   If Port = 0 Then Exit Sub
   
   On Error Resume Next
   mWinSock.RemoteHost = IP
   Result = Err.LastDllError
   'If Result <> 0 Then MsgBox "BTRP1:" & Result
   mWinSock.RemotePort = Port
   Result = Err.LastDllError
   On Error GoTo 0
   'If Result <> 0 Then MsgBox "BTRP2:" & Result
   Me.Port = Port

End Sub

Private Sub Class_Initialize()

   Set mInterApplicationPipeForm = New frmInterApplicationPipe
   Set mWinSock = mInterApplicationPipeForm.wnsInterApplicationPipe

End Sub

Private Sub Class_Terminate()

   Unload mInterApplicationPipeForm

End Sub

Public Sub ClosePort()

   Dim Result As Long

   On Error Resume Next
   mWinSock.Close
   Result = Err.LastDllError
   On Error GoTo 0
   'If Result <> 0 Then MsgBox "CP1:" & Result

End Sub

Public Sub Send( _
      ByVal Message As String _
   )
   
   Dim Result As Long
   
   On Error Resume Next
   mWinSock.SendData Message
   Result = Err.LastDllError
   On Error GoTo 0
   'If Result <> 0 Then MsgBox "S1:" & Result

End Sub

Public Sub mWinSock_DataArrival(ByVal bytesTotal As Long)

   Dim Message As String
   
   mWinSock.GetData Message
   
   'MsgBox Message
   RaiseEvent DataArrival(Message)

End Sub

Public Sub mWinSock_Error(ByVal Number As Integer, Description As String, ByVal Scode As Long, ByVal Source As String, ByVal HelpFile As String, ByVal HelpContext As Long, CancelDisplay As Boolean)

   MsgBox "An inter-application error has occured:" & vbCrLf & vbCrLf & "  Number: " & Number & vbCrLf & vbCrLf & "  Description: " & Description

End Sub

Kevin

There is no code in frmInterApplicationPipe, just a single instance of the WinSock control named "wnsInterApplicationPipe".

Kevin

hmmm.... Think I need some more advice on how to create that WinSock control.
In VBA there is no WinSock control in the toolbox, and I'm not to sure on how to create one on my own.

Arild

In the VBE, create a new user form. With the form open choose the menu command Tools->Additional Controls. Find the entry "Microsoft Winsock Control..." and check the box. Click OK. The Winsock control will now be on the Toolbox window and you can place it on the form.

Kevin

"Microsoft Winsock Control..."  is not one of the options. I'm I missing something?

Arild

Odd, this a Windows OS control...

What version of Windows are you using?

Kevin

I'm running Windows XP Pro SP2
Microsoft office 2007
Running Excel
Working in Visual basic for applications (Alt+F11 from Excel)
From Tools->Additional Controls there are lot of things to choose from, but no Winsock control

Arild

Forced accept.

Computer101
EE Admin