Winsock API in VBA

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
LVL 3
ehoutAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crazymanCommented:
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/
ehoutAuthor 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
crazymanCommented:
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.
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

svaikCommented:
Search on this site....
http://www.pscode.com/vb/
ehoutAuthor Commented:
OK, Don't worry 'bout it.
You still have the article around somewhere?

Grtx
AndrewDevCommented:
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
Richie_SimonettiIT OperationsCommented:
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
msterjevCommented:
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


ehoutAuthor 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
msterjevCommented:
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!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ehoutAuthor 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
msterjevCommented:
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!
ehoutAuthor Commented:
OK, I know enough.

Thanx again.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming Languages-Other

From novice to tech pro — start learning today.