[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5404
  • Last Modified:

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

WHAT I WANT:
I want one cell in an Excel spreadsheet to always have an updated GPS position.

CURRENT SITUATION:
GPS is connected to laptop via serial cable.
Via 3rd party software, the serial port is distributed as an TCP/IP port
When software makes a socket connection to the TCP/IP port, one text string will come every second containing current position.

HOW I THINK I WANT IT:
in VBA I plan to create a client socket, which can connect and read the GPS data. I'm able to handle the GPS data, but need help with creating the socket in VBA.

Arild

0
arildj78
Asked:
arildj78
  • 5
  • 3
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
zorvek (Kevin Jones)ConsultantCommented:
There is no code in frmInterApplicationPipe, just a single instance of the WinSock control named "wnsInterApplicationPipe".

Kevin
0
 
arildj78Author Commented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
arildj78Author Commented:
"Microsoft Winsock Control..."  is not one of the options. I'm I missing something?

Arild
0
 
zorvek (Kevin Jones)ConsultantCommented:
Odd, this a Windows OS control...

What version of Windows are you using?

Kevin
0
 
arildj78Author Commented:
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
0
 
zorvek (Kevin Jones)ConsultantCommented:
It seems the WinSock control is part of VB5/VB6 and may not normally be installed with Windows.

See this article for information about how to use WinSock and possible sources for the control: http://tangentsoft.net/wskfaq/.

Here is a page describing how to use OSWinSck which is a wrapper for the Windows WinSock control: http://www.ostrosoft.com/oswinsck/oswinsck_vba.asp and the download (free for non-commercial use): http://www.ostrosoft.com/oswinsck.asp#inst. I Don't know if the OSWinSck package actually includes the WinSock control though but they talk about using it with any language including VBScript.

Kevin
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now