Step 7 Excel Siemens libnodave

[zmnck]

Zuviel Werbung?
-> Hier kostenlos registrieren

Hello,

I attached my Excel program currently i working on it.

I reading DB1.DBW0 from SIEMENS:

ElseIf InStr(TagType_array(1), "DBW") > 0 Then
addrOffset = Replace(TagType_array(1), "DBW", "")
res2 = daveReadBytes(dc, daveDB, dbnum, addrOffset, 2, 0)

This is clear for me.

Then if this data is equal to 1:

If daveGetU16(dc) = 1 Then

a = davePut16(buffer(0), Cells(10, 1))
a = davePut16(buffer(2), Cells(10, 2))
a = davePut16(buffer(4), Cells(10, 3))
a = davePut16(buffer(6), Cells(10, 4))
a = davePut16(buffer(8), Cells(10, 5))
a = davePut16(buffer(10), Cells(10, 6))
a = davePut16(buffer(12), Cells(10, 7))
a = davePut16(buffer(14), Cells(10, 8))
a = davePut16(buffer(16), Cells(10, 9))
a = davePut16(buffer(18), Cells(10, 10))
a = davePut16(buffer(20), Cells(10, 11))
a = davePut16(buffer(22), Cells(10, 12))
a = davePut16(buffer(24), Cells(10, 13))
a = davePut16(buffer(26), Cells(10, 14))
a = davePut16(buffer(28), Cells(10, 15))
a = davePut16(buffer(30), Cells(10, 16))
a = davePut16(buffer(32), Cells(10, 17))
a = davePut16(buffer(34), Cells(10, 18))
a = davePut16(buffer(36), Cells(10, 19))
a = davePut16(buffer(38), Cells(10, 20))
a = davePut16(buffer(40), Cells(10, 21))
a = davePut16(buffer(42), Cells(10, 22))

res2 = daveWriteBytes(dc, daveDB, dbnum, 2, 2,
buffer(0))

res2 = daveWriteBytes(dc, daveDB, dbnum, 4, 2,
buffer(2))
res2 = daveWriteBytes(dc, daveDB, dbnum, 6, 2,
buffer(4))
res2 = daveWriteBytes(dc, daveDB, dbnum, 8, 2,
buffer(6))
res2 = daveWriteBytes(dc, daveDB, dbnum, 10, 2,
buffer(8))
res2 = daveWriteBytes(dc, daveDB, dbnum, 12, 2,
buffer(10))
res2 = daveWriteBytes(dc, daveDB, dbnum, 14, 2,
buffer(12))
res2 = daveWriteBytes(dc, daveDB, dbnum, 16, 2,
buffer(14))
res2 = daveWriteBytes(dc, daveDB, dbnum, 18, 2,
buffer(16))
res2 = daveWriteBytes(dc, daveDB, dbnum, 20, 2,
buffer(18))
res2 = daveWriteBytes(dc, daveDB, dbnum, 22, 2,
buffer(20))
res2 = daveWriteBytes(dc, daveDB, dbnum, 24, 2,
buffer(22))
res2 = daveWriteBytes(dc, daveDB, dbnum, 26, 2,
buffer(24))
res2 = daveWriteBytes(dc, daveDB, dbnum, 28, 2,
buffer(26))
res2 = daveWriteBytes(dc, daveDB, dbnum, 30, 2,
buffer(28))
res2 = daveWriteBytes(dc, daveDB, dbnum, 32, 2,
buffer(30))
res2 = daveWriteBytes(dc, daveDB, dbnum, 34, 2,
buffer(32))
res2 = daveWriteBytes(dc, daveDB, dbnum, 36, 2,
buffer(34))
res2 = daveWriteBytes(dc, daveDB, dbnum, 38, 2,
buffer(36))
res2 = daveWriteBytes(dc, daveDB, dbnum, 40, 2,
buffer(38))
res2 = daveWriteBytes(dc, daveDB, dbnum, 42, 2,
buffer(40))
res2 = daveWriteBytes(dc, daveDB, dbnum, 44, 2,
buffer(42))

I Writing Excel cells information to SIEMENS:

Excel cell (1, 10) -> to DB1.DBW2
Excel cell (2, 10) -> to DB1.DBW4
Excel cell (3, 10) -> to DB1.DBW6
Excel cell (4, 10) -> to DB1.DBW8
Excel cell (5, 10) -> to DB1.DBW10
Excel cell (6, 10) -> to DB1.DBW11
Excel cell (7, 10) -> to DB1.DBW12
Excel cell (8, 10) -> to DB1.DBW14
Excel cell (9, 10) -> to DB1.DBW16
Excel cell (10, 10) -> to DB1.DBW18

And etc.. All Row.

The problem for me is I dont have Live Reading and Writing from/to SIEMENS.

I should push the button all the time.

Question: How to make this excel working Live reading/writing.
Anhang anzeigen geras.rarAnhang anzeigen geras.rar

I attached my working Excel file.

Thank you.

 

[PN/DP]

Question: How to make this excel working Live reading/writing.

http://www.cpearson.com/excel/OnTime.aspx

Harald

 

[zmnck]

Zuviel Werbung?
-> Hier kostenlos registrieren

Thanks for you answer.

I know this method, I doing like this:

Sub Autpen()
Call EventMacro
End Sub

Public Sub EventMacro()
Call readFromPLC
alertTime = Now + TimeValue("00:00:001")
Application.OnTime alertTime, "EventMacro"
End Sub

I searching another way how to do this live connection with libnodave.

 

[siddharthdc]

I tried o implement your code in VBA. Unfortunately it doesnt accept buffer the way you have defined it. I get a compile error. I could only write

For example :

a = davePut32(buffer, Cells(10, 1))
res2 = daveWriteBytes(dc, daveDB, dbnum, 2, 4,
buffer).
. I would be grateful if you could show me the way of copying the cells in excel sheet to a db library

 

[Larry Laffer]

I suppose you have 2 problems :
- Cells(10,1) is a Variant - so you should write first into a VBA-Variable and the assign it to a Cell
- buffer is not only one value - it is an array. So ... how do you think that you could assign an array to a single Variable ? You must assign one element from the buffer to your Variable ...

Cheers
Larry

 

[siddharthdc]

Zuviel Werbung?
-> Hier kostenlos registrieren

Thanks for your reply. My code is working for writing Double Int from an Excel cell. I face problem writing strings of variable byte size from Excel Cell to LibnodaveDb. As the davePut8/ davePut16 / davePut32 will be hard to use along with daveWriteManyBytes

 

[Larry Laffer]

Create an array of Byte from your String (because a String isn't anything else) and transfer it to your PLC ...

Cheers
Larry

 

[siddharthdc]

Thanks for your reply again. I tried reading the string of 20 bytes long from excel sheet Cell( 12, 5) to Cell(12,9). As you suggested also defined array of Byte to read a string

Private Sub CommandButton4_Click()
Dim s As Integer
Dim a As String
Dim h() As Byte
Dim b() As Byte


For j = 5 To 9
h() = ActiveSheet.Cells(12, j)


For i = 0 To 19

a = davePut8(b(i), h(i))
Next i
s = daveWriteBytes(dc, daveDB, DBNR, Count, 20, b())
Count = Count + 20
Next j



I get the error in daveWriteBytes stage as it says *b()* is incompatible with the compiler. Isn't a buffer an array. I am not able to figure out my mistake.

Any corrections or suggestion from your end will be appreciated

 

[Larry Laffer]

Zuviel Werbung?
-> Hier kostenlos registrieren

Perhaps because b is a Null-dimensioned Array - try it with a dimensionized Array :

Dim b([B]19[/B]) As Byte

But basicly I would make the coding like this :

Dim i,j,s As Integer
Dim a As byte
Dim h As string
Dim b(19) As Byte


For j = 5 To 9
        h = ActiveSheet.Cells(12, j)

        For i = 0 To 19
       b(i) = asc(mid$(h,i,1))
        Next i
        s = daveWriteBytes(dc, daveDB, DBNR, Count, 20, b())
        Count = Count + 20
Next j

Cheers
Larry

 

[siddharthdc]

Thanks for your reply and spending out the time to write an example. May I ask why did you use Mid$. I know its used to iterate a string. But the reason is you are converting a string to ascii is a bigger step as you write a string into DB in ascii format. Neverthless I thank you again for your inputs.

I had small corrections

s = daveWriteBytes(dc, daveDB, DBNR, Count, 20, b()) ' that has to be corrected as having *b()* causes an error again. Maybe you use within a loop.


s = daveWriteBytes(dc, daveDB, DBNR, Count, 20, b(i))

 

[Larry Laffer]

If b() should be an Array of Byte it should contain the ASCII-Values of each Character of the String - otherwise it must be an Array of Char.
So ... if you want to translate an String to an Array of Byte this is the way.

I can't test the method daveWriteBytes and so I don't know it's requirement. You have to look which variables and which variable-types this method really requires. Ftom it's name I suppose that it wants to have an Array ...

Cheers
Larry

 

[siddharthdc]

Zuviel Werbung?
-> Hier kostenlos registrieren

Yes true I tested it. It certainly gives the right output if I define my field in Siemens as array of char. But unfortunately its defined as string[20] . So when i write to this field it is writing in a hex format or so. I certainly think its the restriction of using the Function block daveWriteBytes.

The other way to workaround the problem is changing the format in Siemens program String filed to array of char.
Thanks again you have been a great help

 

[Larry Laffer]

I suggest you write your own customized method which works with the parameters (and the types) you need and use it. That would be the way it's be done in the OOP-world.

 

[Heinileini]

... But the reason is you are converting a string to ascii ...

No, asc() converts 1 byte of ASCII to a numeric value!
aux$ = mid$(h,i,1) ' this selects 1 byte from string h at the position i (byte number i of h) and copies it to aux$. aux$ again is a string, but with the length of 1 byte.
i must have a value from 1 thru len(h).
i=0 will produce an error in mid$(h,i,1)!
For i = 0 To 19
b(i) = asc(mid$(h,i,1)) ' change to b(i) = asc(mid$(h,i+1,1))
Next i

b(i) = asc(aux$) ' asc() "converts" the first byte of string aux$, which is already ASCII-code, to a number (0...255). This step is probably not even necessary, it is a type cast.
chr$() converts in the opposite direction (number to ASCII): aux$=chr$(asc(aux$)) or number&=asc(chr$(number&)) with numbers in the range 0...255.
E.g.: 'A' corresponds to number 65, '9' to 57, etc.

Bye, Heinileini