Excel send latest data to another computer running node red on Docker

Hello,

i would like to do one thing ,
when the excel update new row of data , then the excel can send the new row data to node red.
please give me some suggestion for this issue, thanks a lot in advance...

If the sheet does not need to use fancy fonts/graphs, one approach would be to save the sheet as CSV file. You could then configure a watch node to monitor changes in the CSV file, read it in using a file node and then pass the data though a CSV node to make the data easier to process.

thanks Ristomatti,
for node red watch the excel file, i have two issues
1, the excel file is located in other PC which is in the same local network with node red PC
2, the node red is located in docker.
i don't know how node red can watch and get data from the excel.

Well you did not mention Excel is run on another computer. To get the data to Node-RED, you will either need to automatically transfer the file to the computer were Node-RED is running when it's modified or setup file sharing on the computer running Node-RED.

Which method would sound better to you and which operating systems do the computers have? Is the Excel running on a Windows PC and Node-RED on Raspberry Pi for example?

Edit: you mentioned Node-RED is running on Docker. On a media storage device then perhaps? It will complicate solving the issue and some Docker expert might be needed.

I've edited the message title to better describe the issue so people who might have ideas might find this easier.

We're celebrating Midsummer Eve here in Finland today so I might not be reading the forum in a couple of days (depending on the level of hangover that will result from all this :grin:).

Maybe something like this "Excel Internet of Things Connector"


With this you can connect your excel to a mqtt broker. The broker will publish the data and is available in node red.
3 Likes

both computers run windows system, and i am a newbie with docker :joy:

Unless you have a good reason then I suggest not using Docker. It is excellent in the right circumstances but just causes headaches in situations where it is not needed.

5 Likes

Docker will massively complicate things. I'll ignore it here I'm afaid.

You have a number of options to update Node-RED from Excel. A lot is going to depend on how much control you have over the Excel workbook and whether you are permitted to run macro's on the workbook. If you can let us know that, I can outline what, if any, options you might have beyond having to keep saving the file and watching for changes which, I have to say, I don't believe is safe. It may not be safe because of how Excel manages file saves. Worse, this may vary depending on whether your file is stored in OneDrive/SharePoint or on some other location.

Hello TI,

i have tried to write macros in excel with HTTP POST request to node red web server.
for now i can post the data inside one cell to node red,
i am trying to send the the last row data(several columns) to node red now.

1 Like

If you get it working, please share the macro. Some others might be interested also. Even I might find use for it one way or another. :slightly_smiling_face:

ok , no problem. after i finish, i will share the marco

1 Like

Found a bunch of useful links on using http requests in VBA macros:

https://www.startpage.com/do/dsearch?query=excel+macro+http+POST+request&cat=web&pl=opensearch&language=english

Let me know if you get stuck.

Hello,

i can post data from excel to node red now.
Below is my VBA code, I post data to node red cell by cell.

Sub BasicPOSTRequest()

Dim req As New MSXML2.XMLHTTP30
Dim reqURL As String
Dim i As Integer
Dim m, n As Long

m = ActiveSheet.UsedRange.Rows.Count
n = ActiveSheet.UsedRange.Columns.Count

For i = 1 To n Step 1
    reqURL = "https://10.193.7.229:1880/hello-raw"
    req.Open "POST", reqURL, False
    req.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    req.send ActiveSheet.Cells(m, i).Value
  
    If req.Status <> 200 Then
        MsgBox req.Status & "-" & req.statusText
    Exit Sub
    End If

Next

End Sub

3 Likes

Nice work! Does it send the whole sheet or new rows when they're added?

only send new rows

1 Like

It could be adjusted to detect any cell change.

You could, of course have a function that reacts to a cell change and fires the data off automatically.

There are various ways, here is possibly the most generic - it detects any change on any worksheet and reacts to other macro changes as well as user input - it fires another function only for a specific cell but of course that could be adjusted:

Private Sub Worksheet_Change(ByVal target As Range)
    If Not Intersect(target, target.Worksheet.Range("H5")) Is Nothing Then myFunc
End Sub

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.