Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2005
    Posts
    6

    Unanswered: Text parsing and import into excel

    I've got a 3rd party logging application which logs a number of modbus address into a .csv file every 10 seconds. There are 10 colums in this .csv file each one has a interger number in it. Every 10 seconds a new row is added. What I need to do is have a button next to a cell in excel which when clicked will look at the last line in the text file, what ever row number that may be and get a particular integer value say colum 2 and place it in the cell. I've found examples of code to open the text file and inport the whole lot but I'm not sure how to just look at the last line and then choose a particular colum by counting the commas. Can some one please help. I'm not a VB person but a PLC engineer.

  2. #2
    Join Date
    Jul 2005
    Posts
    6
    After a lot of mucking about I came up with the following solution. Didn't really know what i was really doing but it seems to work ok.

    Public Function Snapshot(Column As Integer) As Integer
    On Error GoTo ErrorHandler ' Enable error-handling routine.
    Dim filename As String
    Dim InputData
    Dim Startcommapos
    Dim Endcommapos
    Dim Startcommacount
    Dim Datalength

    'Calculate today log file file name from PCs date
    filename = Date
    filename = Mid(filename, 4, 2) & Mid(filename, 1, 2) & Mid(filename, 9, 2)

    Open "C:\LOG FILES\" & filename & ".log" For Input As #1 ' Open file.
    Do While Not EOF(1) ' Check for end of file.
    Line Input #1, InputData ' Read line of data.
    Loop
    Close #1 ' Close file.
    'MsgBox (InputData)
    If InputData = "" Then
    Else
    Do While Startcommacount <> Column ' Column chooes which analogue reading to snapshot see above
    Startcommapos = Startcommapos + 1
    Startcommapos = InStr(Startcommapos, InputData, ",", 1)
    Startcommacount = Startcommacount + 1
    Loop
    Startcommapos = Startcommapos + 1
    Endcommapos = InStr(Startcommapos, InputData, ",", 1)
    Datalength = Endcommapos - Startcommapos
    Snapshot = (Mid(InputData, Startcommapos, Datalength))
    End If

    Exit Function ' Exit to avoid handler.
    ErrorHandler: ' Error-handling routine.
    MsgBox (" An error occured taking a snap shot of the log data. Please check logging program is logging correctly and try again")
    Exit Function ' Resume execution at same line
    ' that caused the error.
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •