VBA and enc28j60 - 18f4550

[Roy Johnston]

Morning All,
I have a problem that i have been working on now for 2 weeks, and i just cannot get it to work.
I want to introduce tcp/ip comms to my project.
My hardware is functioning. The ping example works perfectly from cmd.
What I need to do is every 3 seconds send a byte array of 6 bytes to the 18f4550 via tcp and receive back a different set of 7 bytes. This is to set the outputs and read back inputs.
I don’t know how to set up flowcode to receive and send the bytes.
Also I don’t know how to send these requests from VB.
Ant help would be appreciated.
in vba I do it like this for usb.

lRetVal = ECIO_Open(0, sID(0))
lRetVal = ECIO_Transmit(bDataOut(0), 6, nTxDataCnt, bDataIn(0), 7, nRxDataCnt, 200)
ECIO_Close

[Roy Johnston]

Could any one point me in the right direction please, Articles , books that I could read up on.

[QMESAR]

You can take a look here
This woman writes great books and she uses the MCHP parts and has some VB examples on USB however if you search her web you probably fined VB examples in her books on the page
for ethernet

Sorry about that
here it is http://janaxelson.com/

[Roy Johnston]

Thank You,
your post did not state where "Here" is.

[Rudi]

Thank You,
your post did not state where "Here" is.

hi

which VBA / VB version you use?
do you use VB Studio express, which version?

you need "sockets" ( winsocket, tcp, udp )
create a socket and open it.
for a server you go "listen" on the socket
for a client you go "connect" with the socket

see here


info 1
C examples if you use VSC too



a client example:
SRC

Code: Select all

Option Explicit

Dim sPage As String
Dim WithEvents wsTCP As OSWINSCK.Winsock
Dim txtSource As String

Public Function GetServerData() As Boolean
On Error GoTo ErrHandler
  Dim sServer As String
  Dim nPort As Long
  Dim txtURL As String
  
  txtURL = "http://www.google.com"
  
  nPort = 80
  sServer = Trim(txtURL)
  If InStr(sServer, "://") > 0 Then _
     sServer = Mid(sServer, InStr(sServer, "://") + 3)
  If InStr(sServer, "/") > 0 Then
    sPage = Mid(sServer, InStr(sServer, "/") + 1)
    sServer = Left(sServer, InStr(sServer, "/") - 1)
  End If
  If InStr(sServer, ":") > 0 Then
    nPort = Mid(sServer, InStr(sServer, ":") + 1)
    sServer = Left(sServer, InStr(sServer, ":") - 1)
  End If
  If sServer = "" Then Err.Raise 12001, , "Invalid URL"
  
  Set wsTCP = CreateObject("OSWINSCK.Winsock")
  wsTCP.Connect sServer, nPort
  GetServerData = True
  Exit Function

ErrHandler:
  MsgBox "Error " & Err.Number & ": " & Err.Description
  GetServerData = False
End Function

Private Sub wsTCP_OnClose()
  wsTCP.CloseWinsock
End Sub

Private Sub wsTCP_OnConnect()
  wsTCP.SendData "GET /" & sPage & " HTTP/1.0" & vbCrLf & vbCrLf
End Sub

Private Sub wsTCP_OnDataArrival(ByVal bytesTotal As Long)
  Dim sBuffer As String
  wsTCP.GetData sBuffer
  txtSource = txtSource & sBuffer
  Debug.Print txtSource
End Sub

best wishes
rudi

[Roy Johnston]

Thank you,
I am using msaccess 2013
Microsoft visual basic for applications 7.1.
I will study your example and revert back to you

[Roy Johnston]

I cannot figure it out,

I am prepared to pay someone who is prepared to give me a working copy in msaccess, and a copy of the flowcode side to do the following.
one button pressed , we connect to the pic via ip 192.1678.1.90, write 10 bytes from table (a byte array), write those bytes into a variable in the pic,
and then read 10 bytes (a byte array) from a different variable in the pic ,back to the pc and store it in a table.

write
name dataout , field 1 thru 10

read name datain , fiels 1 thru 10

I would even be prepare to prepare the fields and form.

[Rudi]

I cannot figure it out,

I am prepared to pay someone who is prepared to give me a working copy in msaccess, and a copy of the flowcode side to do the following.
one button pressed , we connect to the pic via ip 192.1678.1.90, write 10 bytes from table (a byte array), write those bytes into a variable in the pic,
and then read 10 bytes (a byte array) from a different variable in the pic ,back to the pc and store it in a table.

write
name dataout , field 1 thru 10

read name datain , fiels 1 thru 10

I would even be prepare to prepare the fields and form.

where to download ms access 2013 - does the free 60 day trial version support vba?
do you know? the vba is really simply.

where is the problem?
ms access winsock
enc28j60
or all

which windows version you use?
32/64

best wishes
rudi

[Roy Johnston]

I am sure the trial supports vba,
I cannot get my head around tying up the vba side to the flowcode side.
also understanding the vba side.
I have written many programs in msaccess , no problem.
I just cannot understand how the two integrate.
at the moment I use USB , that was simple.
I am sure once I see the code for both access and the flowcode side I will understand it, then I can integrate it into my existing flowcode and msaccess programs.
it does not have to be access 2013, 2007 or 2010 will suffice.

[Rudi]

hi
can not promise anything, but would see on weekends.
have you try to ping your18f4550 device with enc28j60?
which adapter you use?

best wishes
rudi

[Roy Johnston]

my hardware works, I can ping it using CMD and the flowcode example ,
I attach a copy of the adapter.
thanks for the help

[Rudi]

hi Roy,

can you upload or send by PM a small demo of your ping flowchart?
it must not be the project, but must work in ping from your later desktop pc.
so i can append in this the communication between the vba and your flowchart.
then you can transfer the thing in your project flowchart.

little questions:
which ip addresses you use, perhabs the Default Gateway ( DFGTW 192.168.1.1 ) if you use sub net
example
Pic device ( 192.168.1.10 / 255.255.255.0 , DFGTW 192.168.1.1 )
Desktop ( 192.168.1.11 / 255.255.255.0 , DFGTW 192.168.1.1 )
router/gateway ( 192.168.1.1 / 255.255.255.0 )

do you only connect the pic device by Access or do you connect over HTTP Browser by Post / Request command too?

best wishes
rudi

Edit:
Roy, Because the 64 bit version has some missing features that the 32 bit version has...
which Version you use of (VBA) MS Office (Access..Excel..Word ) 64 or 32 ?

[Roy Johnston]

I only want to communicate to the device via Local are network, it will not work over the internet.

Pic device ( 192.168.1.90 / 255.255.255.0 , DFGTW 192.168.1.1 )
Desktop ( 192.168.1.130 / 255.255.255.0 , DFGTW 192.168.1.1 )
router/gateway ( 192.168.1.1 / 255.255.255.0 )

I only want to connect via access.

I basically want to replace my usb comms that I am currently using due to the limitations of cable length.

I use 32 bit version of access

[Roy Johnston]

Sorry Rudi,
I only see now my PM did not get to you last week.
we can us excel,
I am sure I can use it.

[Rudi]

Sorry Rudi,
I only see now my PM did not get to you last week.
we can us excel,
I am sure I can use it.

hi roy
sorry - i have small time for this just in time
but i am on this to work.

part 1
see the video - i upload just in time
perhabs 1-2 hour later you can see it
EDIT - its done - video is online now:
custom: flowcode & enc28j60

shows, how you can check the flowchart code .. test the device connection from desktop and then you can change like you need more.

download the test client:
here is the test client

testClient.zip

here are few infos about:

roy_1.png

roy_2.png

part 2 follows.. uploads are limited.
..
..

[Rudi]

part 2..

roy_3.png

roy_4.png

and here ..
your flowchart like you wanted

its a general flowchart, tested with
- Arduino Mega 2560 R3
- PIC 18F2550
- PIC 24FJ256GB106
- ESP8266
- ESP32

do simple change the Target
and look in the workbench_B_for_the_10Bytes Makro
you can set then your Port pins to set/watch and what ever..
Pic 18F2550 i will append a separate Flowchart today with the vba things.
and comment a little then..

ENC28J60_demo.fcfx

have phun with desktop testing
i think i get the excel/access 2-3 days runing - its only a time faktor

let me know how you get on with the test.
because i have only pic18F2550 and no pic18F4550 here
i took a mega 2560 for this , because it has more ports as the pic18F2550..
you must change the target simply
and you must change perhabs the ports that you can test "workbench B" makro -
you will see it - i am sure.
because you said, that ping is running at your device - i am sure - you can start without problems the testings.
nice weekend and let me know what you think

best wishes
rudi

[Rudi]

update:
here comes the vba - theme now
i have the finger crossed this night so ..
the theme vba is done too,
but i must clear the code first time today - and then you can use the modules in Access like they are,
you do not need import a excel table with makros or other, simple add the vba modules to your access project
and add in your access form the things like you need.
you must then querry with the subs in the modules..
i will comments this later detailed -

here is at first time the excel front
part one only a picture

roy_vba_1.png

edit:
here is the video link,
Custom: ENC28J60 & Flowcode & VBA Theme Excel / Access
video is just in time at upload and in 1-2-3 hour online ( now is 10:10 AM - think 11:30AM / 01:00 PM is uploaded )
..edit: we have now 01:06 PM .. we need 15 minutes rest.. the upload is slow today..
..edit: video is online now [01:25 PM ]




now i clean up the files - this takes more time..


here are then the files in a update post,
edit: see the next posts, files are uploaded.

Code: Select all

to do today after clean the code/upload the video: 
-> Video link...done
-> VBA EXCEL ..done .. see the next posts..
-> VBA MODULE .. done .. see the next posts
-> Coments .. done ... see the next posts..

// it can be later at this afternoon .. just done..
// i do make a pause too after the night .. ..just done..

and if i have cleaned the code, the vba files too.
do not attack the stuff, this was custom help. if you have a problem with the vba theme
so i can help you privat - but think over - this forum forum about flowcode
vba theme are on the www too

ok, because i work with this enc28j60 just in time in other doings i have think,
will be a nice vba tool for all, so you can share it. but do name me !
and sure, flowcode! too

best wishes
rudi

..now i go on to upload the video, and clean the code.. bye..

[Rudi]

update VBA things
as promised:

first
here is a PIC18F2550 Flowchart:

ENC28J60_PIC18F2550Test.fcfx

what was done?
SPI used HW Channel
so we have small Pins on Pic ( with portexpander you can have more pins - but this is not the theme here )
SPI used:
MOSI - PortC.7 // UART Rx .. so we need a SoftUart if we want Debug with UART
MISO - PortB.0
CLK - PortB.1
SS - PortB.6
RST - PortB.7

SoftUart
Rx - PortB.4
Tx - PortB.5

So we have PortB2 and PortB.3 for Controll and Watch in this example..
all other eight Bytes are "NIL" for this, the L stand for Low, the H for High
for testings, pullup one pin, the other pulldown and call the Command
for test port, example, in the pic18f2550 flowchart the pins are
in the workbench B makro.

Pic18F2550_ENC28J60_VBA_Excel_Access.png

now VBA Theme:

we use the winsock for the VBA:

winsock

Code: Select all

'This is the Winsock API definition file for Visual Basic

'Setup the variable type 'hostent' for the WSAStartup command
Type Hostent
  h_name As Long
  h_aliases As Long
  h_addrtype As String * 2
  h_length As String * 2
  h_addr_list As Long
End Type
Public Const SZHOSTENT = 16


'Set the Internet address type to a long integer (32-bit)
Type in_addr
   s_addr As Long
End Type


'A note to those familiar with the C header file for Winsock
'Visual Basic does not permit a user-defined variable type
'to be used as a return structure.  In the case of the
'variable definition below, sin_addr must
'be declared as a long integer rather than the user-defined
'variable type of in_addr.
Type sockaddr_in
   sin_family As Integer
   sin_port As Integer
   sin_addr As Long
   sin_zero As String * 8
End Type

Public Const WSADESCRIPTION_LEN = 256
Public Const WSASYS_STATUS_LEN = 128
Public Const WSA_DescriptionSize = WSADESCRIPTION_LEN + 1
Public Const WSA_SysStatusSize = WSASYS_STATUS_LEN + 1

'Setup the structure for the information returned from
'the WSAStartup() function.
Type WSAData
   wVersion As Integer
   wHighVersion As Integer
   szDescription As String * WSA_DescriptionSize
   szSystemStatus As String * WSA_SysStatusSize
   iMaxSockets As Integer
   iMaxUdpDg As Integer
   lpVendorInfo As String * 200
End Type

'Define socket return codes
Public Const INVALID_SOCKET = &HFFFF
Public Const SOCKET_ERROR = -1

'Define socket types
Public Const SOCK_STREAM = 1           'Stream socket
Public Const SOCK_DGRAM = 2            'Datagram socket
Public Const SOCK_RAW = 3              'Raw data socket
Public Const SOCK_RDM = 4              'Reliable Delivery socket
Public Const SOCK_SEQPACKET = 5        'Sequenced Packet socket


'Define address families
Public Const AF_UNSPEC = 0             'unspecified
Public Const AF_UNIX = 1               'local to host (pipes, portals)
Public Const AF_INET = 2               'internetwork: UDP, TCP, etc.
Public Const AF_IMPLINK = 3            'arpanet imp addresses
Public Const AF_PUP = 4                'pup protocols: e.g. BSP
Public Const AF_CHAOS = 5              'mit CHAOS protocols
Public Const AF_NS = 6                 'XEROX NS protocols
Public Const AF_ISO = 7                'ISO protocols
Public Const AF_OSI = AF_ISO           'OSI is ISO
Public Const AF_ECMA = 8               'european computer manufacturers
Public Const AF_DATAKIT = 9            'datakit protocols
Public Const AF_CCITT = 10             'CCITT protocols, X.25 etc
Public Const AF_SNA = 11               'IBM SNA
Public Const AF_DECnet = 12            'DECnet
Public Const AF_DLI = 13               'Direct data link interface
Public Const AF_LAT = 14               'LAT
Public Const AF_HYLINK = 15            'NSC Hyperchannel
Public Const AF_APPLETALK = 16         'AppleTalk
Public Const AF_NETBIOS = 17           'NetBios-style addresses
Public Const AF_MAX = 18               'Maximum # of address families


'Setup sockaddr data type to store Internet addresses
Type sockaddr
  sa_family As Integer
  sa_data As String * 14
End Type
Public Const SADDRLEN = 16


'Declare Socket functions

Public Declare Function closesocket Lib "wsock32.dll" (ByVal s As Long) As Long
Public Declare Function connect Lib "wsock32.dll" (ByVal s As Long, addr As sockaddr_in, ByVal namelen As Long) As Long
Public Declare Function htons Lib "wsock32.dll" (ByVal hostshort As Long) As Integer
Public Declare Function inet_addr Lib "wsock32.dll" (ByVal cp As String) As Long
Public Declare Function recv Lib "wsock32.dll" (ByVal s As Long, ByVal buf As Any, ByVal buflen As Long, ByVal flags As Long) As Long
Public Declare Function recvB Lib "wsock32.dll" Alias "recv" (ByVal s As Long, buf As Any, ByVal buflen As Long, ByVal flags As Long) As Long
Public Declare Function send Lib "wsock32.dll" (ByVal s As Long, buf As Any, ByVal buflen As Long, ByVal flags As Long) As Long
Public Declare Function socket Lib "wsock32.dll" (ByVal af As Long, ByVal socktype As Long, ByVal protocol As Long) As Long
Public Declare Function WSAStartup Lib "wsock32.dll" (ByVal wVersionRequired As Long, lpWSAData As WSAData) As Long
Public Declare Function WSACleanup Lib "wsock32.dll" () As Long
Public Declare Function WSAUnhookBlockingHook Lib "wsock32.dll" () As Long
Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)

our framework

Code: Select all

Public Const COMMAND_ERROR = -1
Public Const RECV_ERROR = -1
Public Const NO_ERROR = 0
Public socketId As Long
'Global Variables for WINSOCK
Global State As Integer

Sub CloseConnection()
' we close our connection here
    x = closesocket(socketId)
    If x = SOCKET_ERROR Then
        MsgBox ("ERROR: closesocket = " + Str$(x))
        Exit Sub
    End If
End Sub

Sub EndIt()
    'Shutdown Winsock DLL
    x = WSACleanup()
End Sub

Sub WinSockSelectStart()
' which winsock version we use
' this is here coded

    Dim StartUpInfo As WSAData
    'Version 1.1 (1*256 + 1) = 257
    'version 2.0 (2*256 + 0) = 512
    
    'Get WinSock version
    Sheets("TOP").Select
    'Range("C2").Select
     Range("WINSOCKVERSION").Select
    'version = ActiveCell.FormulaR1C1
     version = ActiveCell.Value
 '   ret = MsgBox("version:", vbDefaultButton1, version)
   
    'Initialize Winsock DLL
    x = WSAStartup(version, StartUpInfo)

End Sub
 
Function OpenSocket(ByVal Hostname As String, ByVal PortNumber As Integer) As Integer
   
    Dim I_SocketAddress As sockaddr_in
    Dim ipAddress As Long
    
    ipAddress = inet_addr(Hostname)

    'Create a new socket
    socketId = socket(AF_INET, SOCK_STREAM, 0)
    If socketId = SOCKET_ERROR Then
        MsgBox ("ERROR: socket = " + Str$(socketId))
        OpenSocket = COMMAND_ERROR
        Exit Function
    End If

    'Open a connection to a server
    I_SocketAddress.sin_family = AF_INET
    I_SocketAddress.sin_port = htons(PortNumber)
    I_SocketAddress.sin_addr = ipAddress
    I_SocketAddress.sin_zero = String$(8, 0)

    x = connect(socketId, I_SocketAddress, Len(I_SocketAddress))
    If socketId = SOCKET_ERROR Then
        MsgBox ("ERROR: connect = " + Str$(x))
        OpenSocket = COMMAND_ERROR
        Exit Function
    End If
    OpenSocket = socketId
End Function

Function SendCommand(ByVal command As String) As Integer
' our communication command...

    Dim strSend As String
    
    strSend = command + vbCrLf
    
    count = send(socketId, ByVal strSend, Len(strSend), 0)
    
    If count = SOCKET_ERROR Then
        MsgBox ("ERROR: send = " + Str$(count))
        SendCommand = COMMAND_ERROR
        Exit Function
    End If
    
    SendCommand = NO_ERROR

End Function

Function RecvAscii(dataBuf As String, ByVal maxLength As Integer) As Integer
' our recv function..

    Dim c As String * 1
    Dim length As Integer
    
    dataBuf = ""
    While length < maxLength
        DoEvents
        count = recv(socketId, c, 1, 0)
        If count < 1 Then
            RecvAscii = RECV_ERROR
            dataBuf = Chr$(0)
            Exit Function
        End If
        
        If c = Chr$(10) Then
           dataBuf = dataBuf + Chr$(0)
           RecvAscii = NO_ERROR
           Exit Function
        End If
        
        length = length + count
        dataBuf = dataBuf + c
    Wend
    
    RecvAscii = RECV_ERROR
    
End Function

Function RecvAryReal(dataBuf() As Double) As Long

' this are special function for byte array doings
' if we want to delimeter the code
' this helps us then complex communication with
' the enc28j60 connected µC device

    ' receive DOS format 64bit binary data

    Dim buf As String * 20
    Dim size As Long
    Dim length As Long
    Dim count As Long
    ' this is special for communicate with
    ' OSZ or other devices later
    ' .. ;-)
    Dim recvBuf(25616) As Byte
    
    ' receive header info "ROY<##"
    ' we can setup here our Startcode too
    ' but we do this in the "worker makro later"
    x = recv(socketId, buf, 8, 0)
    
    size = Val(Mid$(buf, 3, 6))
    
    count = 0
    length = 0
    Do While length < size
        DoEvents
        count = recvB(socketId, recvBuf(length), size - length, 0)
        If (count > 0) Then
            length = length + count
        End If
    Loop
    
    ' receive ending LF
    count = recv(socketId, buf, 1, 0)
    
    ' copy recieved data to Single type array dataBuf()
    CopyMemory dataBuf(LBound(dataBuf)), recvBuf(0), length
    'dataBuf = recvBuf
    
    RecvAryReal = length / 8
    
End Function

and our modul for our workbench/sheet/access form or what ever

Code: Select all

' our 10byte send and receive data vars
Dim DataTx As String
Dim DataRx As String
' our complet recv var
Dim DataRcv As String

' we do privated the Hostname var
Private Hostname$
'Hostport
Public Const HostPort = 255

Sub get_hostname()
    ' we get the hostip from the sheet
    ' so we select the worksheet named "TOP"..
     Sheets("TOP").Select
    ' we can use cell x/y
    'Range("C5").Select
    ' but we use here fieldnames
    ' its better if we change the sheet
    Range("HOSTIP").Select
    ' we can use the formulaR1C1 but...
    'Hostname$ = ActiveCell.FormulaR1C1
    ' this is better
     Hostname$ = ActiveCell.Value
     
End Sub

Sub InitSendData()
' we init our SendData here
' from the "Sheet" named "TOP"
 Sheets("TOP").Select
 ' our data are with forumula created
 ' so we need only the "value" of this cell..
 DataTx$ = Range("DATATX").Value
 ' not need..
 ' DataTx$ = ActiveCell.Value

End Sub

Sub clearDataRx()
' our clear button :)
Sheets("TOP").Select
Range("DATArx1").Value = ""
Range("DATArx2").Value = ""
Range("DATArx3").Value = ""
Range("DATArx4").Value = ""
Range("DATArx5").Value = ""
Range("DATArx6").Value = ""
Range("DATArx7").Value = ""
Range("DATArx8").Value = ""
Range("DATArx9").Value = ""
Range("DATArx10").Value = ""

End Sub



Sub dataworking()
' here you go on with your data...
'
' DataRcv$ is your friend
' all data are here in this var
' DataRx is your A[0]..A[9]
' DataTx was your SendData

' so we update here simple the cells..
' we read from DataRcv$ at position 1 ..- 10 -.. Bytes
' and set it to the DataRx$ Var for our work
' and this data fill then the sheet...
DataRx$ = Mid(DataRcv$, 1, 10)
' your received 10 bytes data in a msgbox...
' x = MsgBox(DataRx$, vbDefaultButton1, "your data from µC")
'...and here goes the data to the cells..
Sheets("TOP").Select
Range("DATArx1").Value = Mid(DataRx$, 1, 1)
Range("DATArx2").Value = Mid(DataRx$, 2, 1)
Range("DATArx3").Value = Mid(DataRx$, 3, 1)
Range("DATArx4").Value = Mid(DataRx$, 4, 1)
Range("DATArx5").Value = Mid(DataRx$, 5, 1)
Range("DATArx6").Value = Mid(DataRx$, 6, 1)
Range("DATArx7").Value = Mid(DataRx$, 7, 1)
Range("DATArx8").Value = Mid(DataRx$, 8, 1)
Range("DATArx9").Value = Mid(DataRx$, 9, 1)
Range("DATArx10").Value = Mid(DataRx$, 10, 1)

 
End Sub



Sub herewego()

    Dim x As Long
    
    ' ok - Init Send Data
    Call InitSendData
    
    ' ok - WS Version
    Call WinSockSelectStart
        
    ' ok - HOSTIP
    Call get_hostname
    
    ' ok
    x = OpenSocket(Hostname$, HostPort)

    ' we could test here.....
    ' x = SendCommand(DataTx)
    
    ' but we use our function in .recvbuf lies the data later..
    Call openpeerconnection
       
    ' we have done..so we close the connection
    ' if we want open the connection
    ' we must change the code in flowcode too
    ' because we have the !ACK in flowcode done..
    Call CloseConnection
    
    ' we clean the socket
    Call EndIt
    
    ' and now we work with out data..
    Call dataworking

End Sub

Sub openpeerconnection()
' our "open peer connection"
'
' wait operation complete
'
    Dim x As Long
    ' we use 10 Bytes..
    Dim recvBuf As String * 10
    ' we can set a pre command send here ..
    ' and we can communicate here if need..
    'x = SendCommand("ready?")
    ' we simple send our generated 10 bytes DataTx data..
    x = SendCommand(DataTx)
    ' we can set the receive buffer here to , example if
    ' we need more bytes from the device
    'x = RecvAscii(recvBuf, 10)
    ' we wait for the  10 bytes..
     x = RecvAscii(recvBuf, 10)
     ' and put them in the Receive Var...
     ' that we can work with this data later...
     ' and we have a Master(DataRcv$) and a Work(DataRx$) Var
     DataRcv$ = recvBuf
     
     ' if you want controll it..
     ' Z = MsgBox(DataRcv$, vbDefaultButton1, "Data comes")
          
End Sub

comments:

our connect button do only start the makro herewego in the Test1.xlsm
this makro calls step by step the things for
- winsock version
- Hostip ( the Hostport is used as constant, you can change like you want )
- and so on.

think the code is comment enough.
if you need a help, so feel free to write.

here comes the excel 2007 with the makro

Test1.zip

inside are the VBA 's
Framework, Module1 and the things for winsocks
simple press after load the excel file ALT and F11 for the VBA editor, then you see the parts.
be sure, you activate the makros

if you export the parts for your access, simple import then the things to your access
do play with the markos for the form in access, update the code for cells update like you need
and select your button the function "herewego"
this is your start..


FYI:
there are many infos and possible doings how we can run winsock in excel / access or other VBA
be carefull with the code , the winsock part is old standard code, the user code is written self, self tested and runs!
simple - VBA ENC28J60 Flowcode - its done!

last but not least:
do allways first a ping,
better, let the ping command run the time

Code: Select all

// windows

ping 192.168.0.90 -t

the device sleep sometimes and the connection out time can be critical in this moment
so do a ping first, wait for reply - then start the vba things, and the connect is allways fast done.

let me know how you get on in this.
you can share and modify like you want.
but do not sell it without reward me

have phun guys
happy coding
and nice sunday and week start

best wishes
rudi

[Rudi]

( upload files are limited to three )

even a word ...
..if you have no Excel and you need the VBA Parts..
here you go on:

the VBA Parts only:

onlyVBA.zip

now let me know.

best wishes
rudi

[Roy Johnston]

Thank you,
I will try it and let you know,
again
thank you

[Roy Johnston]

Good morning Rudi,
I cannot express my thanks and gratitude enough.
you samples were so well layout and explained, that it was easy for me to migrate the code over to Ms access. and it works.
Flowcode should use this as an example for others to study.
with your permission , when I am complete with my first basic program in MSaccess I would like to post it for others to use.
I am actually a huge conservations and I am a corporate sponsor of the Ann van Dyk cheetah center, who has done more for the survival of the cheetah that any one else in the world ( www.dewildt.co.za) . in actual fact the reason why I needed Ethernet comms is to automate a borehole management system in order to save and manage water on their farm.
If you send me your personal email via PM, I would like to on your behalf adopt a cheetah in your name as a thank you for what you have done for me. then you will receive a monthly letter from the cheetah, furthermore you will get to name the cheetah.

once again thank you, thank you.

[Roy Johnston]

Hi Rudi, just one small problem ,
would you know how to check if the ip exists before connecting to the server.
my system hangs if the server is not connected.
it hangs at the point after we open the socket.
thanks

[Rudi]

Hi Rudi, just one small problem ,
would you know how to check if the ip exists before connecting to the server.
my system hangs if the server is not connected.
it hangs at the point after we open the socket.
thanks

hi roy,
this is the point with the winsock and the time out in "VBA"

my solution was allways this

in a test:
before i work with winsock
i open the console in windows by
run "cmd"
then i ping the device nonstop in this window box..
(btw: here you can see the same, the first ping is negative - no response and this is the point in winsock because it do not byself try to connect again in this time point - so its "hang" and wait ,, and wait,,, and wait ..)

after the device is response, then i open the winsock.

btw: in the console the ping run nonstop in this time ( the parameter is -t so use ping 192.168.0.90 -t )

this is for a test ok, but not in produktive.

then i do sometime this:

in the (vba) programm then i use a "shellexecute" or "createprocess" and run the ping command one time, hidden or minimized,
check the response in the pipe connection to the shell by hwnd ( handle window) , if response was a reply, then i open the winsock, if not, then i try again in a loop, or while or until.
after 3 times without a reply, then must here a error message come to inform the user, that the device is not response.

an other way is, run the VBA winsock in a try except block and repeat with a time intervall ( 10ms, 50ms ..)
( this is the best .. for a produktive - no ping, no "frozen" )

a simple third would be,
make a simple batch, ping the device,
run the batch in a loop 3 times example with small delays
after a reply, exit the loop successfull and call the vba application
after no reply, break the loop and run a msgbox and inform the user/you.
this you can do allways, before you try to connect with the winsock.



if you test the desktop client, there are this processes included:

if you select checkbox connect, then the client try to connect
after a time out the client shows, there is no response.
( in this moment the device was "wake up" )
then if you click connect again, then the device is response, or in the third try.

for this i use the reconnect checkbox, this do the process automatically.

in vba you can do this with the try except finally block
and the winsock does not frozen if the device do not reply in the first try.

hope this helps.

best wishes
rudi

the code does not have a try ..catch block
here you can do this like this:

Code: Select all

Try
    [ tryStatements ]
    [ Exit Try ]
[ Catch [ exception [ As type ] ] [ When expression ]
    [ catchStatements ]
    [ Exit Try ] ]
[ Catch ... ]
[ Finally
    [ finallyStatements ] ]
End Try

you must build in this, for the winsock connect.
then winsock do not frozen.
if you need help, or do not know, what you must change in the base code,
let me know.

OT:

simple delphi code for a ping check

Code: Select all

procedure TForm1.Button1Click(Sender: TObject) ;

  procedure RunDosInMemo(DosApp:String;AMemo:TMemo) ;
  const
     ReadBuffer = 2400;
  var
   Security : TSecurityAttributes;
   ReadPipe,WritePipe : THandle;
   start : TStartUpInfo;
   ProcessInfo : TProcessInformation;
   Buffer : Pchar;
   BytesRead : DWord;
   Apprunning : DWord;
  begin
   With Security do begin
    nlength := SizeOf(TSecurityAttributes) ;
    binherithandle := true;
    lpsecuritydescriptor := nil;
   end;
   if Createpipe (ReadPipe, WritePipe,
                  @Security, 0) then begin
    Buffer := AllocMem(ReadBuffer + 1) ;
    FillChar(Start,Sizeof(Start),#0) ;
    start.cb := SizeOf(start) ;
    start.hStdOutput := WritePipe;
    start.hStdInput := ReadPipe;
    start.dwFlags := STARTF_USESTDHANDLES + STARTF_USESHOWWINDOW;
    start.wShowWindow := SW_HIDE;

    if CreateProcess(nil,
           PChar(DosApp),
           @Security,
           @Security,
           true,
           NORMAL_PRIORITY_CLASS,
           nil,
           nil,
           start,
           ProcessInfo)
    then
    begin
     repeat
      Apprunning := WaitForSingleObject
                   (ProcessInfo.hProcess,100) ;
      Application.ProcessMessages;
     until (Apprunning <> WAIT_TIMEOUT) ;
      Repeat
        BytesRead := 0;
        ReadFile(ReadPipe,Buffer[0], ReadBuffer,BytesRead,nil) ;
        Buffer[BytesRead]:= #0;
        OemToAnsi(Buffer,Buffer) ;
        AMemo.Text := AMemo.text + String(Buffer) ;
      until (BytesRead < ReadBuffer) ;
   end;
   FreeMem(Buffer) ;
   CloseHandle(ProcessInfo.hProcess) ;
   CloseHandle(ProcessInfo.hThread) ;
   CloseHandle(ReadPipe) ;
   CloseHandle(WritePipe) ;
   end;
  end;

  begin {button 1 code}
    RunDosInMemo('ping 192.168.0.90',Memo1) ;
  end;

btw:
VBA has no Try Catch
VB has Try Catch
use you only VBA?
then you can use
"On error"

[Rudi]

btw: other ways where:
- "ready winsock" as ocx
- third helper programm like "c programm, that run in background and do open a pipe to your vba that you can communicate with this
- ..more..

best wishes
rudi

[Roy Johnston]

Afternoon Rudi,
thanks I came right.