-1

I have an Excel file like this :

Excel data

This file can have more than two group. In VBScript (for Siemens WinCC) I want to collect data by group (one visual array). With object and recordset I can browse all the file but how can I get only number value by name?

What I want is for exemple:

Get PLTTZF, 1.043, 0, 1.5, 1.043 where Name=FORME 1.5/1 PLAN42

I've tried with a query like this:

StrSQL = "SELECT [F4] FROM [01$] where [F2]='FORME 1.5/1 PLAN42'"

But it return no value because the name is only written on one cell.

Do you have a solution?

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
Thomas Mantot
  • 5
  • 3

2 Answers2

0

I've done this script : 

Const EXCEL_FILE= "C:\Temp\datatest.xls"
Dim strConnectionString
Dim StrSQL
Dim objConnection, objCommand, objRecordset


Dim i, strNaam, line, flag, counter,counter2, lineOK, row
Dim Search
Search = "FORME 1.5/1 PLAN42"


strConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" &    EXCEL_FILE  & "; ReadOnly=False;"

'StrSQL = "SELECT [F3],[F4],[F5],[F6],[F7],[F8],[F9] FROM [01$] where [F2]='FORME 1.5/1 PLAN42'"
StrSQL = "SELECT * from [01$]"

Set objConnection = CreateObject("ADODB.Connection")

objConnection.ConnectionString = strConnectionString

objConnection.Open

Set objCommand = CreateObject("ADODB.Command")

objCommand.ActiveConnection = objConnection

objCommand.CommandText = strSQL

Set objRecordset=objCommand.Execute

objRecordset.Movefirst

line = 0
flag = 0
counter = 0
counter2 = 0
lineOK = 0
row = 0

Dim tmpvalue
While Not objRecordset.EOF
    line = line + 1

    For i = 0 To 8'objRecordset.Fields.Count - 1
        HMIRuntime.Trace "Data : " & objRecordset.Fields(i).Name & " " & objRecordset.Fields(i).Value & vbNewline

        If flag = 1  Then

            'If counter < 9  Then
                tmpvalue = tmpvalue & objRecordset.Fields(i).Value & ";"
                counter = counter + 1

            'End If 


            'If counter = 9 Then
                'counter = 0
                'tmpvalue = tmpvalue & vbNewline
            'End If
            'HMIRuntime.Trace "Data : " & objRecordset.Fields(i).Name & " " & objRecordset.Fields(i).Value & vbNewline
            'strNaam = objRecordset.Fields(0).Value
            'HMIRuntime.Trace "Data2 : " & strNaam & vbNewline
            'HMIRuntime.Trace "Line : " & line & vbNewline
        End If

        If objRecordset.Fields(i).Value = Search Then
            flag = 1
            lineOK = line
        End If

        If line = lineOK + 5 Then
            flag = 0
        End If

        If i = 8 Then 
            tmpvalue = tmpvalue & vbNewline
        End If
    Next    

    objRecordset.MoveNext

Wend
HMIRuntime.Trace tmpvalue

This is working it give me the result : 

;;;;;;;
;;PLTZF;1.043;0;;1.5;1.043;;
;;FRTZF;0.622;0; ;1;0.622; ;
;;MAX;0.522;0;;0.75;0.522;;
;;MIN;-0.389;0;;0.75;-0.389; ;

BUT the record set miss some column, if I browse all data the record set not found "Actual", "Nominal", "Tol+" and "Dev" : 

Data : F1 
Data : F2 
Data : F3 
Data : F4 
Data : F5 
Data : F6 Tol -
Data : F7 
Data : F8 
Data : F9 Out

So for exemple i don't get "-0,75" I don't known why :

Data : F3 MAX
Data : F4 0.522
Data : F5 0
Data : F6 
Data : F7 0.75
Data : F8 0.522
Thomas Mantot
  • 5
  • 3
-1

Resolved by using other driver.

Thomas Mantot
  • 5
  • 3