WinCC RT Prof - ActiveX container (Microsoft Office SpreadSheet 11) Filtering

ProTool, WinCC flexible, WinCC, PP/OP/TP/TD/MP
SamiCAN42
Posts: 7
Joined: Fri Dec 02, 2022 12:25 pm

WinCC RT Prof - ActiveX container (Microsoft Office SpreadSheet 11) Filtering

Post by SamiCAN42 »

Hi,

I can list the variables in the SQL server with VBScript, but after listing, I want to perform various filtering (historical) operations on the listed table by writing VBScript code.
I would be glad if you could help me.

Write https://hizliresim.com/1q0d8g0
Filter https://hizliresim.com/1q0d8g0
sania
Site Admin
Posts: 1386
Joined: Sat Aug 13, 2005 6:15 am

Re: WinCC RT Prof - ActiveX container (Microsoft Office SpreadSheet 11) Filtering

Post by sania »

from internet:

Code: Select all

Dim Conn
Dim RS
Dim SQL
SQL = "SELECT PersonID, FirstName, LastName FROM [TestDB].[dbo].[Persons]"
Set Conn = CreateObject("ADODB.Connection")
Conn.Open = "Provider=SQLOLEDB; Data Source=compname\SQL; Initial Catalog=DB; UID=usera; Integrated Security=SSPI"

Set RS = Conn.Execute(SQL)

Set Sheet = ActiveSheet
Sheet.Activate

Dim R
R = 1
While RS.EOF = False
  Sheet.Cells(R, 1).Value = RS.Fields(0) //can try : RS.Fields(R)
  Sheet.Cells(R, 2).Value = RS.Fields(1)//can try : RS.Fields(R+1)
  Sheet.Cells(R, 3).Value = RS.Fields(2)//can try : RS.Fields(R+2)
  RS.MoveNext
  R = R + 1
Wend

RS.Close
Conn.Close
SamiCAN42
Posts: 7
Joined: Fri Dec 02, 2022 12:25 pm

Re: WinCC RT Prof - ActiveX container (Microsoft Office SpreadSheet 11) Filtering

Post by SamiCAN42 »

Thank you Sania.
I don't have any problems printing the data I get from SQL. I want to filter after printing.
sania
Site Admin
Posts: 1386
Joined: Sat Aug 13, 2005 6:15 am

Re: WinCC RT Prof - ActiveX container (Microsoft Office SpreadSheet 11) Filtering

Post by sania »

you are not true
need filter before print
filtering with keyword WHERE in your RecordSource
maybe use LIKE too
SamiCAN42
Posts: 7
Joined: Fri Dec 02, 2022 12:25 pm

Re: WinCC RT Prof - ActiveX container (Microsoft Office SpreadSheet 11) Filtering

Post by SamiCAN42 »

After printing, I need to filter. As seen in Annex-1, I can pull my relevant values from SQL. I can also filter, but I cannot give a specific date range. (Annex-2)

Annex-1 : https://hizliresim.com/qqgdhix
Annex-1.1 : https://hizliresim.com/nqgopc0

Annex-2 : https://hizliresim.com/jwbg320

When the Vbscript in the picture ANNEX-2 runs, it filters for ID number 5 in the ID column. But I want to filter within a certain date range in the DATE branch. If I can enter a date range in the code MyFilteredCol_1.Criteria.Add "5" in the picture in ANNEX-2, my problem will be solved.
sania
Site Admin
Posts: 1386
Joined: Sat Aug 13, 2005 6:15 am

Re: WinCC RT Prof - ActiveX container (Microsoft Office SpreadSheet 11) Filtering

Post by sania »

Code: Select all

WHERE Date_Time BETWEEN '2023-01-03' AND '2013-01-09'
or if have separate start & end time point

Code: Select all

WHERE From_date <= '2013-01-03' AND To_date >= '2013-01-09'