Winsock API in VBA

ehout
ehout used Ask the Experts™
on
Hi,

In VBA I need to use the Winsock API to connect to an IP address (IP number is known, so no resolving, just straight connect), send a textstring and receive a textstring (4K) in return. That's it.

I have seen lot's of Api stuff on this site, of which I don't need the most (I think). The server is available within our Intranet, so also no dial-up stuff etc.

Just connect, send and get some data and close connection.

Could someone help me out?

FYI, I do not wish to use the ocx for the ocx needs to be registered on the client machines. The code will be put in a office add-on (template) which resides on the server, so anyone with office 97 can use it.

Kind regards
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
This is a very good article and is strongly recommended :)
http://www.asptoday.com/articles/20000816.htm

This is another good site all about winsock programming in vb.
http://members.nbci.com/ramgs/

Author

Commented:
Now give me break,

that asptoday is a paid site. I'm not subscribing only for 1 article. And I'm not an asp developer, so I don't see the need for subscribing to it.

Grtx, Eric

Commented:
jeez, sorry dude those links were given to me when i asked the same question so i passed them on, it was free last time i looked.

Chillout man.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Search on this site....
http://www.pscode.com/vb/

Author

Commented:
OK, Don't worry 'bout it.
You still have the article around somewhere?

Grtx
One of these may well help you. They may not do exactly what you want but you should get the idea:

Beginers Winsock example
http://www.freevbcode.com/ShowCode.Asp?ID=1212

Two way file transfer with Winsock
http://www.freevbcode.com/ShowCode.Asp?ID=988

Create a chat room with winsock - multiple connections
http://www.freevbcode.com/ShowCode.Asp?ID=137

Good Luck
AVD
A long time ago I have used an ftp connection with Excel VBA.
It is not 100% VBA code but does the trick:
Create a .bat file like this:
(These values are examples only
ftp 128.0.0.1
root
password
cd /path/myfiles
put mytextstring
get myfile.txt c:\mydocuments\myfile.txt
close
quit

And use code like this:
shell "command.com /c mybat.bat",vbHide

Hope it helps

Commented:
Public Const AF_INET = 2
Public Const SOCK_STREAM = 1
Public Const SOCKET_ERROR = 1
Public Const FD_SETSIZE = 64
Public Const FIONBIO = 2147772030#
Public Const SOCKADDR_IN_SIZE = 16
Public Const FORMAT_MESSAGE_FROM_SYSTEM = &H1000

Public Type WSADATA
    wVersion As Integer
    wHighVersion As Integer
    szDescription As String * 257
    szSystemStatus As String * 129
    iMaxSockets As Integer
    iMaxUdpDg As Integer
    lpVendorInfo As Long
End Type

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

Public Type fd_set
    fd_count As Long
    fd_array(FD_SETSIZE) As Long
End Type

Public Type timeval
    tv_sec As Long
    tv_usec As Long
End Type

Public Declare Function WSAStartup Lib "wsock32.dll" (ByVal intVersionRequested As Integer, lpWSAData As WSADATA) As Long
Public Declare Function WSACleanup Lib "wsock32.dll" () As Long
Public Declare Function w_socket Lib "wsock32.dll" Alias "socket" (ByVal lngAf As Long, ByVal lngType As Long, ByVal lngProtocol As Long) As Long
Public Declare Function w_closesocket Lib "wsock32.dll" Alias "closesocket" (ByVal SocketHandle As Long) As Long
Public Declare Function w_bind Lib "wsock32.dll" Alias "bind" (ByVal socket As Long, Name As SOCKADDR_IN, ByVal namelen As Long) As Long
Public Declare Function w_connect Lib "wsock32.dll" Alias "connect" (ByVal socket As Long, Name As SOCKADDR_IN, ByVal namelen As Long) As Long
Public Declare Function w_send Lib "wsock32.dll" Alias "send" (ByVal socket As Long, buf As Any, ByVal length As Long, ByVal flags As Long) As Long
Public Declare Function w_recv Lib "wsock32.dll" Alias "recv" (ByVal socket As Long, buf As Any, ByVal length As Long, ByVal flags As Long) As Long
Public Declare Function w_select Lib "wsock32.dll" Alias "select" (ByVal nfds As Long, readfds As fd_set, writefds As fd_set, exceptfds As fd_set, timeout As timeval) As Long
Public Declare Function htons Lib "wsock32.dll" (ByVal hostshort As Integer) As Integer
Public Declare Function ntohl Lib "wsock32.dll" (ByVal netlong As Long) As Long
Public Declare Function inet_addr Lib "wsock32.dll" (ByVal Address As String) As Long
Public Declare Function ioctlsocket Lib "wsock32.dll" (ByVal socket As Long, ByVal cmd As Long, argp As Long) As Long
Public Declare Function FormatMessage Lib "kernel32" Alias "FormatMessageA" (ByVal dwFlags As Long, lpSource As Any, ByVal dwMessageId As Long, ByVal dwLanguageId As Long, ByVal lpBuffer As String, ByVal nSize As Long, Arguments As Long) As Long


Private Sub CloseSocket(socket As Long)
    If socket <> -1 Then
        w_closesocket socket
    End If
    WSACleanup
End Sub

Public Function ReadURI(Address, URI)
    Dim ret As Long
    Dim SocketHandle As Long
    Dim wd As WSADATA
    Dim localAddress As SOCKADDR_IN
    Dim serverAddress As SOCKADDR_IN
    Dim URIRequest As String
    Dim retBuff(1000) As Byte
    Dim retString As String
    Dim tempString As String
    VcitajURI = ""
    SocketHandle = -1
    ret = WSAStartup(&H101, wd)
    If ret <> 0 Then GoTo ErrorHandler
    SocketHandle = w_socket(AF_INET, SOCK_STREAM, 0)
    If SocketHandle = -1 Then GoTo ErrorHandler
    localAddress.sin_family = AF_INET
    localAddress.sin_port = 0
    localAddress.sin_addr = 0
    ret = w_bind(SocketHandle, localAddress, SOCKADDR_IN_SIZE)
    If ret = -1 Then GoTo ErrorHandler
    serverAddress.sin_family = AF_INET
    serverAddress.sin_port = htons(80)
    serverAddress.sin_addr = inet_addr(Address)
    ret = w_connect(SocketHandle, serverAddress, SOCKADDR_IN_SIZE)
    If ret = -1 Then GoTo ErrorHandler
    URIRequest = "GET /" & URI & " HTTP/1.0" & vbCrLf & vbCrLf
    ret = w_send(SocketHandle, ByVal URIRequest, Len(URIRequest), 0)
    If ret = -1 Then GoTo ErrorHandler
    Do
        ret = w_recv(SocketHandle, retBuff(0), 1000, 0)
        If ret = -1 Then GoTo ErrorHandler
        If ret > 0 Then
            tempString = StrConv(retBuff, vbUnicode)
            retString = retString & Left(tempString, ret)
        End If
    Loop While ret > 0
    VcitajURI = retString
ErrorHandler:
    CloseSocket SocketHandle
End Function


Author

Commented:
msterjev,

An impressive piece of code, but as stated earlier, I'm not familiair with this kind of programming. A little explanation might help?

Kind regards
Commented:
Thirst as I can see in a hurry I rename the function VcitajURI to ReadURI )( I am from Macedonia). Just ignore this and rename back ReadURI to VcitajURI. This peace of code is a quite simple. First you must initialize Winsock with WSAStartup(). After that  we create socket with w_socket. The socket has two ends. The local end we bind with local port and address. To local end we pass zero's and operating system is responsible for choosing correct port which is available. The port and address are contained in SOCKADDR_IN structure. After this local binding , we connect the remote end of the socket with another SOCKADDR_IN structure which , as you can see is address of the WEB server (port 80 - you can choose port depending of the servis, for example SMPT is 25, telnet is 23,daytime 13, POP3 110). I use htons (host- to -network -short) to reverse byte order of the port because the network order is different from Intel's host byte order. The function inet_addr converts string address for example "127.0.0.1" to long representation. After we have connection we send text string with w_send, and call w_receive as long as this function returns 0 (the server gracefully shutdown) or -1 in the case of Error. All of the winsock funciton return -1 in the case of error. In that case I break the code and go to the ErrorHandler().
You can use the function VcitajURI by passing to this function string address , for example "192.168.100,2", and URI which can writed in the case of WEB server. In your case URI string will be string which you want to send to the server! Change the port if the server use another!

Author

Commented:
Hi,

Thanks for the answer so far. It did a good job.
Could you please tell me if it is possible to keep the connection open for a while to exchange more than 1 text strings?

(FYI, In MS-Office where I use this thing, in a short time, many requests will be done in a short time. To open a seperate connection each time seems a bit overkill to me?)

Kind regards

Commented:
A Web server closes connection after sending context. If it is upon you to close connection, than you can manage connection. In that case you must know how much data server must send to you ! For example, a time server send to us 4 byte's long ( we know how much it send to us)! Knowing the amount of the sent data is essential for knowing when the sending is finished and its time for knew request!

Author

Commented:
OK, I know enough.

Thanx again.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial