search

tttapa / ESP8266

Documentation and help with the ESP8266 chip/boards/modules
GNU General Public License v3.0
656 stars 283 forks source link

Anybody able to interface with Office Excel using Winsock? #69

Open Buzzardbait opened 4 years ago

Buzzardbait commented 4 years ago

I've used the ESP8266 Server/LED example that I modified to work with a NodeMCU/Arduino M2560 and a custom designed digitally controlled filter(quite a stack). It interfaces with a browser (Firefox) and works great over my LAN. But what I want to do is duplicate the client end with Office Excel VBA using Winsock for the port 80 and port 81 connections. The advantage this brings is that then I can use an Excel spreadsheet for any data acquisition I need and leverage the graphics/chart capabilities built into Excel plus export data, etc. The trouble is port 80 seems to open ok and indicates it downloaded the html (which I don't need) but refuses to send/receive text over port 81. Looking at the Firefox console(F12) it's only possible to view variables in the javascript and not the actual data being sent to the NodeMCU so I've had to guess what data is actually transmitted over the ports during the connection process and so far this hasn't helped. Has anyone successfully used Winsock to do the interface in VBA?

Buzzardbait commented 4 years ago

Just a follow up. I've tried everything that I can think of to replicate known good Firefox Javascript code in VBA using Winsock. Particularly troublesome is the Javascript code establishing the New websocket on port 81 which is: var connection = new WebSocket('ws://'+'192.168.0.5'+':81/', ['arduino']); It is not possible to instantiate a Winsock connection with this exact string. Winsock wants just a port and a server IP address such as in: ws81.Connect "192.168.0.1" , 81 and fails if the string contains the 'ws://' prolog. This implies that there is some ESP8266 application layer code filtering attempts at connections and rejecting those not having the 'ws://' preamble (and probably the ['arduino']. Why is this necessary? I've used wireShark to observe the TCP packets going back and forth and it shows that the client(Excel VBA/Winsock) dutifully sends its data to the ESP8266 AND IT GETS THE ACK back from the ESP8266 so it thinks the connection is alive and well but the ESP8266 is blind to everything. Very puzzling.

Buzzardbait commented 4 years ago

Update: Found how to get NodeMCU1.0 into TCP mode. Needs http GET request. Code follows: Client Request:

oString = "GET / HTTP/1.1" & vbCrLf oString = oString & "Host: ws://192.168.0.9:81/" & vbCrLf oString = oString & "Connection: Upgrade" & vbCrLf oString = oString & "Pragma: no-cache" & vbCrLf oString = oString & "cache-Control: no-cache" & vbCrLf oString = oString & "Upgrade: WebSocket" & vbCrLf oString = oString & "Sec-WebSocket-Version: 13" & vbCrLf oString = oString & "Sec - WebSocket - Protocol: arduino " & vbCrLf oString = oString & "Sec-WebSocket-Key: yaVX9fn9sKEiN2YbqvF/fg==" & vbCrLf oString = oString & "Sec - WebSocket - Protocol: arduino " & vbCrLf oString = oString & vbCrLf ws81.SendData (oString)

NodeMCU1.0 Response:

HTTP/1.1 101 Switching Protocols Server: arduino-WebSocketsServer Upgrade: websocket Connection: Upgrade Sec-WebSocket-Version: 13 Sec-WebSocket-Accept: xaeFOTHpxO8e54UJtOnwdL+odwo= Sec-WebSocket-Protocol: arduino

NodeMCU (via serial monitor) tells me I'm connected and starts pinging. Reading but not doing anything with the Accept key(Don't think it's necessary). Now the problem is as soon as I try to send text the NodeMCU disconnects and there's no specific error message.

Anyone have any ideas?