I edited the excel sheet for the eWON TAG GENERATOR REV 6.xlsm ( SEE ATTACHED ) and got it to work on
EWON version Firmware: 14.4s1 (#1704)S/N: 2011-0066-24.
Using the ABLOGIX server excel sheet did not hold all of the fields. " CUSTOM UNITS " has been added evidently
Then by creating a var_lst.csv file and transferring it to the EWON after running
"Erase config" "All configuration except COM configuration (you will be unlogged)"
Then re-configuring the ABLOGX server before replacing the var_lst.csv file
I was able to use excel for the tags for this project.
I there an excel sheet that has the VB code and Macros working for the newer EWON units????
(15-09-2021, 11:49 PM)glenharter Wrote: I edited the excel sheet for the eWON TAG GENERATOR REV 6.xlsm ( SEE ATTACHED ) and got it to work on
EWON version Firmware: 14.4s1 (#1704)S/N: 2011-0066-24.
Using the ABLOGIX server excel sheet did not hold all of the fields. " CUSTOM UNITS " has been added evidently
Then by creating a var_lst.csv file and transferring it to the EWON after running
"Erase config" "All configuration except COM configuration (you will be unlogged)"
Then re-configuring the ABLOGX server before replacing the var_lst.csv file
I was able to use excel for the tags for this project.
I there an excel sheet that has the VB code and Macros working for the newer EWON units????
Try the attached file. IT will need to be unzipped. The sheet does have a column for custom units and has been used on units with up to 14.2 firmware.
I know there has been two version 6 developed by different people
Simon
Thank you Simon,
It is the VB script for "EXPORT TO EWON" that does not work.
The "NEW CONFIG ( FWR 13.2)" button deletes the tags in excel and does not transfer them.
The "Import from EWON", "Import from CSV" and "Export to CSV" all work perfectly. The "Send to EWON" returns a FTP error.
But If I export to CSV and FTP using "explorer" which merely overwrites "var_lst.csv" does what I need.
When I get done with this project I will fix the VB code and send it to you, right now I am under a time constraint.
I am using windows 10 and the ABLOGIX server. lets just close this for now.
Glen
I know there has been two version 6 developed by different people
Simon
Thank you Simon,
It is the VB script for "EXPORT TO EWON" that does not work.
The "NEW CONFIG ( FWR 13.2)" button deletes the tags in excel and does not transfer them.
The "Import from EWON", "Import from CSV" and "Export to CSV" all work perfectly. The "Send to EWON" returns a FTP error.
But If I export to CSV and FTP using "explorer" which merely overwrites "var_lst.csv" does what I need.
When I get done with this project I will fix the VB code and send it to you, right now I am under a time constraint.
I am using windows 10 and the ABLOGIX server. lets just close this for now.
Glen
Last spring, I found and fixed the problem, but never took the time to complete the centralization of the different versions, and I wanted to create a GitHub repository with all the different versions, keeping the references of the authors of each revision.
Why Github, for a binary file (except the code)? Because this forum hides the URLs and attachments when not logged in, and the parallel user forum on HMS keeps getting requests and the parallel versions come from there too.
Apart from fixing the upload problem, was fixing once and for all the changes needed to be made when different firmware versions are used.
A CSV is imported, and the header line is skipped, then the other content is pasted below. The changes made each version were to adjust the header each time. But why don't the macro just paste the good headers, at the right place? A conditional formatting is able to keep the header line good-looking. The header is now updated when you import the latest tag info into the Excel, which is something to do anyway before editing. The changes are in the Module2.
For the upload, the problem was with the FTP connection. Being used in active mode, where the local computer opens a port and tells the FTP server to connect to, obviously a firewall should block it, and should be problematic behind a NAT. By looking at the Windows API documentation, I found how to adapt and set the flags in VBA to use passive mode, where the server opens a port and the local computer connects to it. The code changes are in Module3.
Should someone complete the Instructions page, and keeping up the revision table, it could be useful for others.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ExportToTextFile
' This exports a sheet or range to a text file, using a
' user-defined separator character.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub ExportToTextFile(FName As String, _
Sep As String, SelectionOnly As Boolean, _
AppendData As Boolean)
Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String
Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile
If SelectionOnly = True Then
With Selection
StartRow = .Cells(1).Row
StartCol = .Cells(1).Column
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
Else
With ActiveSheet.UsedRange
StartRow = 3
StartCol = 1
EndRow = .Cells(.Cells.Count).Row
EndCol = .Cells(.Cells.Count).Column
End With
EndRow = Cells(Rows.Count, 2).End(xlUp).Row 'Last row of data in col B
EndCol = Cells(3, Columns.Count).End(xlToLeft).Column 'Last col of data in row 3
End If
If AppendData = True Then
Open FName For Append Access Write As #FNum
Else
Open FName For Output Access Write As #FNum
End If
For RowNdx = StartRow To EndRow
WholeLine = ""
For ColNdx = StartCol To EndCol
If Cells(RowNdx, ColNdx).Value = "" Then
CellValue = Chr(34) & Chr(34)
Else
If ColNdx = 48 And ColNdx = 3 And ColNdx = 2 And ColNdx = 44 And ColNdx = 49 And RowNdx <> StartRow And Mid(Cells(RowNdx, ColNdx).Value, 1, 1) <> Chr(34) Then 'SSU, ESU, TGANAME, DESC and STO col that can accept ; or ""
CellValue = Chr(34) & Cells(RowNdx, ColNdx).Value & Chr(34)
Else
CellValue = Cells(RowNdx, ColNdx).Value
End If
End If
WholeLine = WholeLine & CellValue & Sep
Next ColNdx
WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
Print #FNum, WholeLine
Next RowNdx
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ExportTextFile
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DoTheExport
' This prompts the user for the FileName and the separtor
' character and then calls the ExportToTextFile procedure.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub DoTheExport()
Dim FileName As Variant
Dim Sep As String
FileName = Application.GetSaveAsFilename(InitialFileName:="var_lst", FileFilter:="Excel Files (*.csv), *.csv", Title:="Save As")
''FileName = Application.GetSaveAsFilename(InitialFileName:=vbNullString, FileFilter:="Text Files (*.txt),*.txt")
If FileName = False Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If
''Sep = Application.InputBox("Enter a separator character.", Type:=2)
Sep = ";"
If Sep = vbNullString Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If
Debug.Print "FileName: " & FileName, "Separator: " & Sep
ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
SelectionOnly:=False, AppendData:=False
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END DoTheExport
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Module2:
Code:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ImportTextFile
' This imports a text file into Excel.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Public Sub ImportTextFile(FName As String, Sep As String)
Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer
''//This part skips the first two lines
'If Not (EOF(1)) Then Line Input #1, TempStr
While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
'For string params that can accept ";" or dblquotes
If Pos - 1 <> 0 Then 'skip first col
If Mid(WholeLine, Pos - 1, 2) = ";" + Chr$(34) Then
NextPos = InStr(Pos, WholeLine, Chr$(34) + ";") + 1
End If
End If
EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END ImportTextFile
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DoTheImport
' This prompts the user for a FileName as separator character
' and then calls ImportTextFile.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub DoTheImport()
Dim FileName As Variant
Dim Sep As String
FileName = Application.GetOpenFilename(FileFilter:="CSV/Text File (*.txt;*.csv),*.txt;*.csv")
If FileName = False Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If
'Sep = Application.InputBox("Enter a separator character.", Type:=2)
Sep = ";"
If Sep = vbNullString Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If
Debug.Print "FileName: " & FileName, "Separator: " & Sep
ImportTextFile FName:=CStr(FileName), Sep:=CStr(Sep)
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END DoTheImport
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Module3:
Code:
'Open the Internet object
Private Declare PtrSafe Function InternetOpen _
Lib "wininet.dll" _
Alias "InternetOpenA" _
(ByVal sAgent As String, _
ByVal lAccessType As Long, _
ByVal sProxyName As String, _
ByVal sProxyBypass As String, _
ByVal lFlags As Long) As Long
'Connect to the network
Private Declare PtrSafe Function InternetConnect _
Lib "wininet.dll" _
Alias "InternetConnectA" _
(ByVal hInternetSession As Long, _
ByVal sServerName As String, _
ByVal nServerPort As Integer, _
ByVal sUsername As String, _
ByVal sPassword As String, _
ByVal lService As Long, _
ByVal lFlags As Long, _
ByVal lContext As Long) As Long
'Get a file using FTP
Private Declare PtrSafe Function FtpGetFile _
Lib "wininet.dll" _
Alias "FtpGetFileA" _
(ByVal hFtpSession As Long, _
ByVal lpszRemoteFile As String, _
ByVal lpszNewFile As String, _
ByVal fFailIfExists As Boolean, _
ByVal dwFlagsAndAttributes As Long, _
ByVal dwFlags As Long, _
ByVal dwContext As Long) As Boolean
'Send a file using FTP
Private Declare PtrSafe Function FtpPutFile _
Lib "wininet.dll" _
Alias "FtpPutFileA" _
(ByVal hFtpSession As Long, _
ByVal lpszLocalFile As String, _
ByVal lpszRemoteFile As String, _
ByVal dwFlags As Long, _
ByVal dwContext As Long) As Boolean
'Close the Internet object
Private Declare PtrSafe Function InternetCloseHandle _
Lib "wininet.dll" _
(ByVal hInet As Long) As Integer
'Added by echoix (https://github.com/echoix) on 2021-01-28:
'Use passive mode instead of active mode in InternetConnectA
'to prevent the firewall blocking the upload. In active FTP
'mode, the clients listens on a port and informs the server
'that it is listening. With NAT, or firewall active (should
'be active), the FTP upload fails. When using eCatcher, the
'network profile used by the Windows Firewall is "Public",
'even if your internet connection that the VPN connection
'uses is "Private". By setting the passive mode option, the
'client sends the server a PASV command, and gets an IP
'address and port that the client uses to create an outbound
'connection.
Private Const INTERNET_FLAG_PASSIVE As Long = &H8000000
Sub UploadFTP()
Dim hostFile As String
Dim INet As Long
Dim INetConn As Long
Dim Password As String
Dim RetVal As Long
Dim ServerName As String
Dim Success As Long
Dim UserName As String
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
'For string params that can accept ";" or dblquotes
If Pos - 1 <> 0 Then 'skip first col
If Mid(WholeLine, Pos - 1, 2) = ";" + Chr$(34) Then
NextPos = InStr(Pos, WholeLine, Chr$(34) + ";") + 1
End If
End If
24-02-2022, 04:22 PM (This post was last modified: 24-02-2022, 04:23 PM by PrintScreen.)
Hello,
Hardware: Ewon Flexy with FW 14.2
I’m using GENERATOR REV 9 and want to utilise the “tag type” function in the GENERATOR.
I have set column T (the “Auto Type” column) to “0”
I have set column BC (the “Type” column) to “1”
Can you please point me in the direction of a manual for the GENERATOR or the document that highlights what parameters can be set in each column?
(This kind of document must have been used to build the original GENERATOR)
I can see that if I change BC to the following, the type is changed in the Ewon as follows:
· “0” = Bool
· “1” = Floating Point
· “2” = Int
· Etc………
Thanks
