Code is written in the EXCEL macro command, VB development, part of the code is as follows:

Dim sPro, sDsn sSer sCon, sSql
Dim conn, oRs, oCom
Dim DSNName
Dim As Integer I
Dim sStart, sStop As String

Sub get_wincc_data ()
Dim M_Row, M_Col M_Hour, m_i As Integer
Dim M_S As String
Dim M, N, Key As Integer
Dim Min As Long
Dim Max As Long
Dim TT As Integer

SDsn="CC_FS_13_11_20_10_29_04R" 'database address

SPro="Provider=WinCCOLEDBProvider. 1;"
SDsn="Catalog=" & amp; SDsn & amp; ";"
SSer="Data source=OS3 \ WinCC" 'local IP
SCon=sPro & amp; SDsn & amp; SSer
Set the conn=CreateObject (" ADODB. Connection ")
Conn. The ConnectionString=sCon
Conn. CursorLocation=3
Conn. Open
The Set oRs=CreateObject (" ADODB. You ")
Mand Set oCom=CreateObject (" ADODB.Com ")
OCom.Com mandType=1
The Set oCom. ActiveConnection=conn



'
query and check timeSStart=Year (DTPicker1. Value) & amp; "-" & amp; The Month (DTPicker1. Value) & amp; "-" & amp; Day (DTPicker1 Value) & amp; "07:30:00
"
M_S=Year (DTPicker1. Value) & amp; "-" & amp; The Month (DTPicker1. Value) & amp; "-" & amp; Day (DTPicker1 Value) & amp; "08:30:00
"SStop=DateAdd (" h ", 24, CDate (M_S))


SStart=DateAdd (" h "- 8, CDate (sStart))
SStop=DateAdd (" h "- 8, CDate (sStop))


SSql="Tag: R, (' 1 hour \ PS4/B123. FQ1500_REAL '), '" & amp; SStart & amp; "', '" & amp; SStop & amp; "' order by a datetime" modify to read file variable
OCom.Com mandText sSql=
The Set of oRs=oCom. Execute

'populate the data
M_Row=6 '
in the changesM_Col=3 '
in the changes
TT=0

If (oRs) EOF) Then 'user table data to fill in the start bit of judgment is not null
For M_Hour=6 To 30
Sheet1. Cells (M_Row + M_Hour - 6, M_Col)=0
Next M_Hour
The Else
ORs. MoveFirst 'pointer to highest

Sheet1. Cells (M_Row + M_Hour - 8, M_Col)=oRs. Fields. (2) the Value

End the If


End the If


ORs. Close
The Set of oRs=Nothing

CodePudding user response:

VBA can step through, you can step by step F8
Behold a execution result is not correct, and then to analyze the reason,

CodePudding user response:

M_S=Year (DTPicker1. Value) & amp; "-" & amp; The Month (DTPicker1. Value) & amp; "-" & amp; Day (DTPicker1 Value) & amp; "08:30:00
"Written in such a line:
M_S=Format $(DTPicker1. Value, "yyyy - m - d") & amp; "08:30:00
"

CodePudding user response:

You can try to use Wincc script functions, the need of numerical into Excel, don't know that I can solve the problem,