Excel >> TCP/IP

by fernando » Tue, 27 Apr 2004 01:36:17 GMT

Hello,

I need to connect to a web server, extract some data and
put it in a worksheet. It would be much easier to extract
this data directly from excel than having to develop an
external application to do it.

Is it possible to open a TCP Socket with VBA in excel? if
it is, where can I find some information about it?

Thanks

Fernando E

Excel >> TCP/IP

by Rob van Gelder » Tue, 27 Apr 2004 14:34:08 GMT


Rather than connect via sockets, why not use some of the functionality
already provided by Excel.

Data | Get External Data | Run Web Query

--
Rob van Gelder - http://www.vangelder.co.nz/excel

Excel >> TCP/IP

by srverity » Tue, 27 Apr 2004 19:58:12 GMT

ou may want to check out the follwing link

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/winsock/winsock/wspconnect_2.asp

The examples are in C# but it gives a good idea how to use the
functions

Here's the declarations needed which I found in an example on another
forum

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 Address As String
Public Port As Integer
Public SocketHandle As Long

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

And the functions called in VBA

ret = WSAStartup(&H101, wd) 'Init winsock

SocketHandle = w_socket(AF_INET, SOCK_STREAM, 0) 'Open socket,
get sockethandle

localAddress.sin_family = AF_INET
localAddress.sin_port = 0 'local port defined by operating system
localAddress.sin_addr = 0 'local address

ret = w_bind(SocketHandle, localAddress, SOCKADDR_IN_SIZE) 'Bind
socket to local port

serverAddress.sin_family = AF_INET

Excel >> TCP/IP

by ZmVybmFuZG8 » Wed, 28 Apr 2004 00:06:06 GMT

Thanks!

Similar Threads

1. TCP/IP in Excel 2003 VBA

Hello,

Is there an easier way to open an IP connection to my networked printer?

currently I've been trying to use:

Private Declare Function WSAStartup Lib "WSOCK32.DLL" Alias "#115" _
     (ByVal wVersionRequired As Integer, _
      ByRef WSAData As WSADataType) As Integer

but this is just an uphill battle.

2. Excel TCP/IP spreadsheet formula help - Excel

3. TCP/IP and Excel

Working with Excel 2000 and Win NT 4.0

Is there a way, maybe through API's, that I can enter data into m
workbook on my computer then have that data sent to another compute
over the internet and have it displayed in there workbook?

Basically I want to be able to have numerous people working with thei
own identical workbooks and have one sheet that displays the same dat
for everyone.

I have read through many books and searched the internet but haven'
been able to come up with anything

4. Clarification: using excel to send data to ethernet device over TCP/IP - Excel

5. How can I replace TCP/IP version 6?

Son uninstalled TCP/IP version 6 in an attempt to improve wireless 
connection.  He was going to reinstall it, but neither of us can find a way 
to do so, can anyone help, please.  Thanks, Lottie

6. tcp/ip error "send to "mail from excel 2007

7. How can I format tcp/ip addresses in Excel?

Is there a template or custom format out there somewhere (I'm an obvious 
Excel rookie)?  I have searched and am now officially blue-in-the-face.  I 
would like to be able to format a column to tak the ip address and sort, 
filter, etc.  Some networking genius must have done this at some time.  
Thanks in advance for pointers!

8. How do I setup Excel to connect to a server via TCP/IP? - Excel