Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
TCP/IP
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
TCP/IP
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 "fernando" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
TCP/IP
You may want to check out the follwing link
http://msdn.microsoft.com/library/de...pconnect_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 serverAddress.sin_port = htons(Port) 'port number serverAddress.sin_addr = inet_addr(Address) 'ip address ret = w_connect(SocketHandle, serverAddress, SOCKADDR_IN_SIZE) 'Connect to remote host URIRequest = URI ret = w_send(SocketHandle, ByVal URIRequest, Len(URIRequest), 0) 'send connect string ret = w_recv(SocketHandle, retBuff(0), 1024, 0) 'Receive string Simon "fernando" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
TCP/IP
Thanks!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|