[VB6.0] Spread에 Serial, DB연결

스프레드와 DB연결 참고

https://d-footprint.tistory.com/79?category=1023510

[VB 6.0] 스프레드와 DB 연결하기

 

Serial포트를 통해 Spread에 데이터 뿌려주기 참고

https://d-footprint.tistory.com/110?category=1023510 

[VB6.0] Serial포트를 통해 Spread에 데이터 뿌려주기

 

 

 

 

모듈

Option Explicit

Public Declare Function GetPrivateProfileString Lib "kernel32" Alias "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As String, ByVal nSize As Long, ByVal lpFileName As String) As Long
Public Declare Function WritePrivateProfileString Lib "kernel32" Alias "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As Long
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

' File: MSCOMM.BAS
'
' Copyright (C) 1996 Microsoft Technologies.
' All rights reserved.
'
'----------------------------------------------------------
'--- MSComm event constants
Public Const MSCOMM_EV_SEND = 1     ' There are SThreshold number of characters in the transmit buffer.
Public Const MSCOMM_EV_RECEIVE = 2  ' Received RThreshold # of chars.
Public Const MSCOMM_EV_CTS = 3      ' Change in the CTS line.
Public Const MSCOMM_EV_DSR = 4      ' Change in the DSR line.
Public Const MSCOMM_EV_CD = 5       ' Change in the CD line.
Public Const MSCOMM_EV_RING = 6     ' Change in the Ring Indicator.
Public Const MSCOMM_EV_EOF = 7      ' An EOF character was found in the input stream.
                            

'--- MSComm error code constants
Public Const MSCOMM_ER_BREAK = 1001     ' A Break was received.
Public Const MSCOMM_ER_CTSTO = 1002     ' CTS Timeout.
Public Const MSCOMM_ER_DSRTO = 1003     ' DSR Timeout.
Public Const MSCOMM_ER_FRAME = 1004     ' Framing Error.
Public Const MSCOMM_ER_OVERRUN = 1006   ' Data Lost.
Public Const MSCOMM_ER_CDTO = 1007      ' CD (RLSD) Timeout.
Public Const MSCOMM_ER_RXOVER = 1008    ' Receive buffer overflow.
Public Const MSCOMM_ER_RXPARITY = 1009  ' Parity Error.
Public Const MSCOMM_ER_TXFULL = 1010    ' Transmit buffer full.
Public Const MSCOMM_ER_DCB = 1011       ' Unexpected error retrieving DCB]


Public Function LeftH(ByVal anystr As String, ByVal nPos As Integer) As String
    LeftH = StrConv(LeftB(StrConv(anystr, vbFromUnicode), nPos), vbUnicode)
End Function


Public Sub ExecuteDB(ByVal rsIP As String, ByVal rsCatalog As String, ByVal rsUser As String, ByVal rsPwd As String, ByVal rsqlStr As String, ByRef iCnt As Integer)
    On Error GoTo err
    
    Dim AdoCn As New ADODB.Connection
    
    '상단에 프로젝트의 참조를 들어가서 Microsoft ActiveX Data Objects 2.8 Library를 체크 그래야 데이터 베이스에 접근 가능
    
    With AdoCn
        .ConnectionTimeout = 3
        
        'DB연결(모듈은 모든 폼에서 동일하게 적용하기 때문에 특정 ip를 입력할 순 없다)
        .Open "Provider=sqloledb;Data Source=" & rsIP & ";Database=" & rsCatalog & ";UID=" & rsUser & ";PWD=" & rsPwd
        
        .Execute rsqlStr, iCnt
    
        .Close             '연결 종료
    End With
err:
    If err <> 0 Then
        MsgBox ("오류:" & err.Description)
    End If
End Sub


Public Sub GetDBData(ByVal rsIP As String, ByVal rsCatalog As String, ByVal rsUser As String, ByVal rsPwd As String, ByVal rsqlStr As String, ByRef rsRet)        '데이터베이스의 값을 가져와서 스프레드에 뿌리기
    On Error GoTo err
    
    Dim MyConnObj As New ADODB.Connection
    Dim myRectSet As New ADODB.Recordset
    Dim i As Integer
    
    '상단에 프로젝트의 참조를 들어가서 Microsoft ActiveX Data Objects 2.8 Library를 체크 그래야 데이터 베이스에 접급 가능
    MyConnObj.Open "Provider=sqloledb;Data Source=" & rsIP & ";Database=" & rsCatalog & ";UID=" & rsUser & ";PWD=" & rsPwd
    
    myRectSet.Open rsqlStr, MyConnObj        '쿼리 날리기
    
    If myRectSet.EOF = True Then
    Else
        ' 첫번째: ?,  2번째 -1: 모든 데이터? , 3번째 Chr(124): 각 열을 Chr(124)로 구분해줌, 4번째 Chr(3): 각 행을 Chr(3)으로 구분
        rsRet = myRectSet.GetString(adClipString, -1, Chr(124), Chr(3))
    End If

''    If myRectSet.RecordCount <> 0 Then   '셀렉트해서 값이 있으면
''        Do While Not myRectSet.EOF '목록 끝까지 불러오기
''            rsRet = rsRet & myRectSet.Fields(0) & Chr(124)
''            rsRet = rsRet & myRectSet.Fields(1) & Chr(124)
''            rsRet = rsRet & myRectSet.Fields(2) & Chr(124)
''            rsRet = rsRet & myRectSet.Fields(3) & Chr(124)
''            rsRet = rsRet & myRectSet.Fields(4) & Chr(124) & Chr(3)
''
''            myRectSet.MoveNext  '값 하나 받고 다음으로 넘어가는거
''        Loop
''    End If

    MyConnObj.Close             '연결 종료
    
err:
    If err <> 0 Then
        MsgBox ("오류:" & err.Description)
    End If
End Sub

 

 

---

 

 

Form_Load

Private Sub Form_Load()
    Dim sBuf$
    Dim RetVal$
    Dim a$
    Dim rsPort$
    Dim rsBaudRate$
    Dim rsParity$
    Dim rsDataBit$
    Dim rsStopBIt$

    spread.MaxRows = 0
    
    
    ''  COMM연결(.ini를 통한 포트 연결)
    With MSComm1
        sBuf = String(255, 0)
        RetVal = GetPrivateProfileString("MSCOMM", "Port", "", sBuf, 255, App.Path & "\Test.ini")
        
        If RetVal = 0 Then
        Else
            a = LeftH(sBuf, RetVal)
        End If
        
        rsPort = a
        
        
        sBuf = String(255, 0)
        RetVal = GetPrivateProfileString("MSCOMM", "BaudRate", "", sBuf, 255, App.Path & "\Test.ini")
        
        If RetVal = 0 Then
        Else
            a = LeftH(sBuf, RetVal)
        End If
        
        rsBaudRate = a
        
        
        sBuf = String(255, 0)
        RetVal = GetPrivateProfileString("MSCOMM", "Parity", "", sBuf, 255, App.Path & "\Test.ini")
        
        If RetVal = 0 Then
        Else
            a = LeftH(sBuf, RetVal)
        End If
        
        rsParity$ = a
              
              
        sBuf = String(255, 0)
        RetVal = GetPrivateProfileString("MSCOMM", "DataBit", "", sBuf, 255, App.Path & "\Test.ini")
        
        If RetVal = 0 Then
        Else
            a = LeftH(sBuf, RetVal)
        End If
        
        rsDataBit$ = a
        
        
        sBuf = String(255, 0)
        RetVal = GetPrivateProfileString("MSCOMM", "StopBIt", "", sBuf, 255, App.Path & "\Test.ini")
        
        If RetVal = 0 Then
        Else
            a = LeftH(sBuf, RetVal)
        End If
        
        rsStopBIt = a
              
              
        .CommPort = rsPort
        .Settings = rsBaudRate & "," & rsParity & "," & rsDataBit & "," & rsStopBIt
        .RTSEnable = True
        .RThreshold = 1
        .PortOpen = True
        
    End With
        
End Sub

 

프로젝트가 있는 폴더 안에 Test.ini 파일을 만들어 넣어줌

Test.ini파일은 Port연결에 사용

<Test.ini 내용>

[MSCOMM]
Port=1
BaudRate=9600
Parity=n
DataBit=8
StopBIt=1

 

MSComm(COMM Vitual Machine과 연결)

Private Sub MSComm1_OnComm()
    Dim sTemp As String
    
    '' COMM Virtual Machine에서 전송받은 데이터를 sTemp에 저장
    sTemp = MSComm1.Input

    '' Data함수를 통해 Spread에 뿌려줄 계산식 가져옴
    '' sTemp를 매개변수로 사용 > Data에서는 이게 sVal로 사용됨(Private Sub Data(ByVal sVal As String와 같이 사용하였음)
    Call Data(sTemp)
    
    Select Case MSComm1.CommEvent '장비내의 이벤트가 발생할 시 결과에 따라 각 케이스로 움직인다. 이때, 이벤트 결과값을 확인할수 있는 방법은 모듈안에 있다.
       ' Events
        Case MSCOMM_EV_SEND     ' There are SThreshold number of
                                ' character in the transmit buffer.
        Case MSCOMM_EV_RECEIVE  ' Received RThreshold # of chars.
            
        Case MSCOMM_EV_CTS      'j
        Case MSCOMM_EV_DSR      ' Change in the DSR line.
        Case MSCOMM_EV_CD       ' Change in the CD line.
        Case MSCOMM_EV_RING     ' Change in the Ring Indicator.
        ' Errors
        Case MSCOMM_ER_BREAK    ' A Break was received.
        ' Code to handle a BREAK goes here, and so on.
        Case MSCOMM_ER_CTSTO    ' CTS Timeout.
        Case MSCOMM_ER_DSRTO    ' DSR Timeout.
        Case MSCOMM_ER_FRAME    ' Framing Error.
        Case MSCOMM_ER_OVERRUN  ' Data Lost.
        Case MSCOMM_ER_CDTO     ' CD (RLSD) Timeout.
        Case MSCOMM_ER_RXOVER   ' Receive buffer overflow.
        Case MSCOMM_ER_RXPARITY ' Parity Error.
        Case MSCOMM_ER_TXFULL   ' Transmit buffer full.
    End Select
End Sub

 

 

Data 함수(MSComm으로 데이터를 가져갈 역할)

'' Serial Port로 받아온 데이터를 스프레드에 뿌려주는 역할
'' sVal은 파라미터를 통해 MSComm1_OnComm()에 있는 sTemp으로부터 데이터를 받아오는 역할
Private Sub Data(ByVal sVal As String)

''   On Error GoTo err

    Dim sprTest() As String
    Dim sprPN As String
    Dim sprRack As String
    Dim sprPos As String
    Dim sprWSN As String
    Dim sprResult As String
    Dim vPN, vPN2
    Dim vPos
    Dim iRow As Integer
    Dim jRow As Integer
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim TodayDate As Date


    '첫번째 = STX(1char)
    '두번째 = R(1char)
    '세번째 = Workstation Number(2char)
    '네번째 = Patient ID Number(15char)
    '다섯번째 = Rack.no(2char)
    '여섯번째 = Position(2char)
    '일곱번째 = Cycle/Bayer(2char)
    '여덟번째 = Result(4char)
    '아홉번째 = Checksum(1char)
    '열번째 = ETX(1char)
    
    sprPN = Mid(sVal, 5, 15)
    
    With spread
        
        'STX가 Q일때
        If Mid(sVal, 2, 1) = "Q" Then
            sprPN = Mid(sVal, 5, 15)
            sprWSN = Mid(sVal, 3, 2)
            sprResult = Mid(sVal, 20, 4)
            TodayDate = Now()
            
            '' SearchCol(열 지정, 구할 값의 시작 행, 끝 행, 찾을 값, ?)를 통해 찾을 값의 행 번호를 구함
            iRow = .SearchCol(.GetColFromID("PN"), 0, .MaxRows, sprPN, SearchFlagsNone)
            '' vPN은 String값이 아님!
            '' PN열의 iRow행의 값을 vPN에 저장
            Call .GetText(.GetColFromID("PN"), iRow, vPN)
            
            
            '' 전송받은 데이터가 중복일 경우 입력되지 않도록 설정
''            If sprPN = vPN Then
''                .MaxRows = .MaxRows
''                Call .SetText(.GetColFromID("Seq"), iRow, "")
''                Call .SetText(.GetColFromID("PN"), iRow, sprPN)
''                Call .SetText(.GetColFromID("Rack"), iRow, "")
''                Call .SetText(.GetColFromID("Pos"), iRow, "")
''                Call .SetText(.GetColFromID("WSN"), iRow, sprWSN)
''                Call .SetText(.GetColFromID("Result"), iRow, sprResult)
''                Call .SetText(.GetColFromID("Date"), iRow, "")
''            '' 중복이 아닐경우 입력
''            Else
                .MaxRows = .MaxRows + 1
                
                Call .SetText(.GetColFromID("Seq"), .MaxRows, "")
                Call .SetText(.GetColFromID("PN"), .MaxRows, sprPN)
                Call .SetText(.GetColFromID("Rack"), .MaxRows, "")
                Call .SetText(.GetColFromID("Pos"), .MaxRows, "")
                Call .SetText(.GetColFromID("WSN"), .MaxRows, sprWSN)
                Call .SetText(.GetColFromID("Result"), .MaxRows, sprResult)
                '' 날짜 시간은 Format형식으로 입력해야 출력됨
                Call .SetText(.GetColFromID("Date"), .MaxRows, Format(TodayDate, "YYYY-MM-DD HH:NN:SS"))
''            End If
        
        Dim a As String
        a = sprPN
            
        'STX가 T일때
        ElseIf Mid(sVal, 2, 1) = "T" Then
        
            sprPN = Mid(sVal, 5, 15)
            sprWSN = Mid(sVal, 3, 2)
            sprResult = Mid(sVal, 20, 4)
            
            iRow = .SearchCol(.GetColFromID("PN"), 0, .MaxRows, sprPN, SearchFlagsNone)
            ' vPN은 String값이 아님!
            Call .GetText(.GetColFromID("PN"), iRow, vPN)
            
            If sprPN = vPN Then
                .MaxRows = .MaxRows
                
                Call .SetText(.GetColFromID("PN"), iRow, sprPN)
                Call .SetText(.GetColFromID("Rack"), iRow, "")
                Call .SetText(.GetColFromID("Pos"), iRow, "")
                Call .SetText(.GetColFromID("WSN"), iRow, sprWSN)
                Call .SetText(.GetColFromID("Result"), iRow, sprResult)
            Else
                .MaxRows = .MaxRows + 1
            
                Call .SetText(.GetColFromID("PN"), .MaxRows, sprPN)
                Call .SetText(.GetColFromID("Rack"), .MaxRows, "")
                Call .SetText(.GetColFromID("Pos"), .MaxRows, "")
                Call .SetText(.GetColFromID("WSN"), .MaxRows, sprWSN)
                Call .SetText(.GetColFromID("Result"), .MaxRows, sprResult)
            End If
        
        'STX가 R일때
        Else
        
            sprPN = Mid(sVal, 5, 15)
            sprRack = Mid(sVal, 20, 2)
            sprPos = Mid(sVal, 23, 2)
            sprWSN = Mid(sVal, 3, 2)
            sprResult = Mid(sVal, 26, 4)
             
            
            '' searchCol (열에서 같은 값을 찾아주는 명령어)
            For j = 0 To .MaxRows

                j = .SearchCol(.GetColFromID("PN"), 0, .MaxRows, sprPN, SearchFlagsNone)

                Call .GetText(.GetColFromID("PN"), j, vPN2)
                Call .GetText(.GetColFromID("Pos"), j, vPos)

                    If sprPN = vPN2 Then

                    .MaxRows = .MaxRows

                        If vPos = "" Then
                            Call .SetText(.GetColFromID("PN"), j, sprPN)
                            Call .SetText(.GetColFromID("Rack"), j, sprRack)
                            Call .SetText(.GetColFromID("Pos"), j, sprPos)
                            Call .SetText(.GetColFromID("WSN"), j, sprWSN)
                            Call .SetText(.GetColFromID("Result"), j, sprResult)
                        Else
                            For k = 0 To .MaxRows
                
                                Call .GetText(.GetColFromID("PN"), k, vPN2)
                                Call .GetText(.GetColFromID("Pos"), k, vPos)
                                            
                                If sprPN = vPN2 And vPos = "" Then
                                    Call .SetText(.GetColFromID("PN"), k, sprPN)
                                    Call .SetText(.GetColFromID("Rack"), k, sprRack)
                                    Call .SetText(.GetColFromID("Pos"), k, sprPos)
                                    Call .SetText(.GetColFromID("WSN"), k, sprWSN)
                                    Call .SetText(.GetColFromID("Result"), k, sprResult)
                                    
                                    Exit For
                                End If

                            Next k
                        End If
                    End If
            Exit For
            Next j
            
        End If
    
        
        '' Seq 입력
        i = .MaxRows
        
        i = i + 1
        Call .SetText(.GetColFromID("Seq"), i, i)
            
    End With

'err:
'    If err <> 0 Then
'        Call msg(err.Description)
'    End If
    
End Sub

sVal : 매개변수, 매개변수를 통해 MSCOmm1_OnComm()에서 sTemp를 통해 데이터를 가져옴

STX : 시리얼 통신 언어, 통신 시작을 의미

  STX가 Q일 경우 : 중복으로 Spread에 입력되도록 설정(IF주석을 지우면 중복으로 입력되지 않도록 설정 가능)

  STX가 R일 경우 : Q가 입력되어 있어야만 데이터를 받아 올 수 있도록 설정

  STX: R데이터는 같은 PN(PatientNumber)의  Q데이터에 덮어씌움

      - 위에서 부터 차례대로 R데이터를 받아옴 

 

ex_

데이터 전송 순서(①-④, ②-⑤, ③-⑥ 같은 PN)

① > ② > ① > ① > ② > ① > ④ > ④④④⑥⑥ 

 

 

DB서버에 저장

Private Sub Command1_Click()
    On Error GoTo err
    
    Dim sSql As String
    Dim iCnt As Integer
    Dim i As Integer
    Dim aSeq, aPN, aRack, aPos, aWSN, aResult, aDate
    Dim sSeq, sPN, sRack, sPos, sWSN, sResult, sDate As String
    
    
    With spread
        For i = 1 To .MaxRows
        
            .Col = .GetColFromID("Seq")
            .Row = i
            
            Call .GetText(.GetColFromID("Seq"), i, aSeq)
            Call .GetText(.GetColFromID("PN"), i, aPN)
            Call .GetText(.GetColFromID("Rack"), i, aRack)
            Call .GetText(.GetColFromID("Pos"), i, aPos)
            Call .GetText(.GetColFromID("WSN"), i, aWSN)
            Call .GetText(.GetColFromID("Result"), i, aResult)
            Call .GetText(.GetColFromID("Date"), i, aDate)
            
            sSeq = Trim(aSeq)
            sPN = Trim(aPN)
            sRack = Trim(aRack)
            sPos = Trim(aPos)
            sWSN = Trim(aWSN)
            sResult = Trim(aResult)
            sDate = Trim(aDate)
        
            sSql = "INSERT INTO Patient(Sequence, PatientNumber, Rack, Position, WorkStationNumber, Result, Date)" _
                   & "values('" & sSeq & "', '" & sPN & "', '" & sRack & "', '" & sPos & "', '" & sWSN & "', '" & sResult & "', '" & sDate & "')"
            
            Call ExecuteDB("127.0.0.1,1433", "ACKTEST", "sa", "ackif", sSql, iCnt)
            
       Next
    End With
    
err:
    If err <> 0 Then
        Call MsgBox(err.Description)
    End If
End Sub

 

 

DB서버에서 데이터 가져오기

Private Sub Command2_Click()
    Dim sRet$
    Dim sSql$
    Dim i As Integer
    Dim j As Integer
    Dim aTest() As String
    Dim aTest2() As String
    Dim sInput As String

    '' DB데이터 가져오기
    sSql = "SELECT * FROM Patient"
    
    Call GetDBData("127.0.0.1,1433", "ACKTEST", "sa", "ackif", sSql, sRet)
    
    aTest() = Split(sRet, Chr(3))
    
    With spread
        For i = 0 To UBound(aTest) - 1
            If aTest(i) <> "" Then
            
                aTest2() = Split(aTest(i), Chr(124))
                
                spread.MaxRows = spread.MaxRows + 1
                
                Call .SetText(.GetColFromID("Seq"), .MaxRows, aTest2(0))
                Call .SetText(.GetColFromID("PN"), .MaxRows, aTest2(1))
                Call .SetText(.GetColFromID("Rack"), .MaxRows, aTest2(2))
                Call .SetText(.GetColFromID("Pos"), .MaxRows, aTest2(3))
                Call .SetText(.GetColFromID("WSN"), .MaxRows, aTest2(4))
                Call .SetText(.GetColFromID("Result"), .MaxRows, aTest2(5))
                Call .SetText(.GetColFromID("Date"), .MaxRows, aTest2(6))
            End If
        Next i
    End With
End Sub

 

 

Spread 전체 데이터 삭제

Private Sub Command3_Click()
    Dim sSql As String
    Dim i As Integer
    Dim vTmp
    Dim sSeq As String
    
    With spread
        For i = 1 To .MaxRows
            Call .GetText(.GetColFromID("Seq"), i, vTmp)
            sSeq = Trim(vTmp)
            
            .Row = i
            .Action = 5
            
            .MaxRows = .MaxRows - 1
            
        Next i
    End With
End Sub

 

 

선택 데이터 삭제

Private Sub Command4_Click()
    Dim sSql As String
    Dim i As Integer
    Dim vTmp
    Dim sSeq As String
    
    With spread
        For i = 1 To .MaxRows
            .Col = .GetColFromID("ChkBox")
            .Row = i
            
            If .Text = "1" Then
                Call .GetText(.GetColFromID("Seq"), i, vTmp)
                sSeq = Trim(vTmp)
                
                .Row = i
                .Action = 5
                
                i = i - 1
                
                .MaxRows = .MaxRows - 1
            End If
        Next i
    End With
    
End Sub

 

선택 날짜 데이터 가져오기

※ DB에 저장하는 날짜 형식이 YYYY-MM-DD HH-NN-SS 이라 날짜만 입력해서 검색을 할려고 

Date1 & * 와 같이 *를 넣어줬는데 오류가 뜸( * 없이 날짜만 넣어줘도 검색 잘 됨)

Private Sub Command5_Click()
    Dim sRet$
    Dim sSql$
    Dim i As Integer
    Dim j As Integer
    Dim aTest() As String
    Dim aTest2() As String
    Dim sInput As String
    Dim Date1 As Date
    Dim Date2 As Date
     
    Date1 = Text1.Text
    Date2 = Text2.Text

    '' DB데이터 가져오기
    sSql = "SELECT * FROM Patient WHERE Date BETWEEN '" & Date1 & "' AND '" & Date2 & "'"
    
    Call GetDBData("127.0.0.1,1433", "ACKTEST", "sa", "ackif", sSql, sRet)
    
    aTest() = Split(sRet, Chr(3))
    
    With spread
        For i = 0 To UBound(aTest) - 1
            If aTest(i) <> "" Then
            
                aTest2() = Split(aTest(i), Chr(124))
                
                spread.MaxRows = spread.MaxRows + 1
                
                Call .SetText(.GetColFromID("Seq"), .MaxRows, aTest2(0))
                Call .SetText(.GetColFromID("PN"), .MaxRows, aTest2(1))
                Call .SetText(.GetColFromID("Rack"), .MaxRows, aTest2(2))
                Call .SetText(.GetColFromID("Pos"), .MaxRows, aTest2(3))
                Call .SetText(.GetColFromID("WSN"), .MaxRows, aTest2(4))
                Call .SetText(.GetColFromID("Result"), .MaxRows, aTest2(5))
                Call .SetText(.GetColFromID("Date"), .MaxRows, aTest2(6))
            End If
        Next i
    End With
    
End Sub

 

원하는 문자 검색

Private Sub Command6_Click()
    Dim sRet$
    Dim sSql$
    Dim Temp$
    Dim i As Integer
    Dim aTest() As String
    Dim aTest2() As String
    
    Temp = Text3.Text
    
    sSql = "SELECT * FROM Patient WHERE PatientNumber LIKE '%" & Temp & "%' "
    
    Call GetDBData("127.0.0.1,1433", "ACKTEST", "sa", "ackif", sSql, sRet)
    
    aTest() = Split(sRet, Chr(3))
    
    With spread
        For i = 0 To UBound(aTest) - 1
            If aTest(i) <> "" Then
            
                aTest2() = Split(aTest(i), Chr(124))
                
                spread.MaxRows = spread.MaxRows + 1
                
                Call .SetText(.GetColFromID("Seq"), .MaxRows, aTest2(0))
                Call .SetText(.GetColFromID("PN"), .MaxRows, aTest2(1))
                Call .SetText(.GetColFromID("Rack"), .MaxRows, aTest2(2))
                Call .SetText(.GetColFromID("Pos"), .MaxRows, aTest2(3))
                Call .SetText(.GetColFromID("WSN"), .MaxRows, aTest2(4))
                Call .SetText(.GetColFromID("Result"), .MaxRows, aTest2(5))
                Call .SetText(.GetColFromID("Date"), .MaxRows, aTest2(6))
            End If
        Next i
    End With
    
End Sub