Excel Hints

Results 1 to 3 of 3

Thread: Extract Substring, Ignore Substring for rest of txt, collect data while creating Data

  1. #1

    Extract Substring, Ignore Substring for rest of txt, collect data while creating Data

    Ok this is a pretty complicated question. I am given a txt file "Data.txt". It has a lot of these lines

    [GPS: Central time,lat,dir,lon,dir,vel,cog,trk]
    135921.00,3609.75843,N,09008.86165,W,N,,29
    [GPS: Central time,lat,dir,lon,dir,vel,cog,trk]
    235921.00,3809.79843,S,09008.86165,W,N,.01,29

    What I'm wondering is how to extract each of the "Central Time" "lat" "dir" etc. from the file's first line(posting them to designated cells). Then ignoring them for the rest of the file while still extracting each of the "135921.00" "3609.75843" "N" etc (posting them into the cells below each other under the Titles "Central Time" "lat" etc). I keep getting confused as to which variables I'm using or if I'm even using them correctly. Any help would be appreciated

  2. #2
    I am posting this reply cause I re-read and I realized I wasn't specific as to what I needed.

    I just need to know how to extract the data from the first string then disregard it for the rest of the file. Then Read in every other even line for data. I can open the file for input and also post results into the spreadsheet. I'm just not to sure how to do the string parsing.

  3. #3

    Code So Far

    [VBA]
    Private Sub CommandButton1_Click()
    'Variables
    Dim Fn As String
    Dim ColumnCT As String
    Dim StringArray As String

    Fn = FreeFile()
    Open TextBox1.Text For Input As Fn
    Sheet1.UsedRange.Clear

    'Making Column Titles
    Dim Il As Integer
    Dim Loc As Integer
    Dim Leng As Integer
    Dim ColmnTtl As String
    Il = 0
    While Il < 50
    Il = Il + 1
    Loc = InStr(Fn, ",")
    Loc -Il = Leng
    ColmnTit = Mid(Fn, Loc, Leng)
    Wend

    Sheets("sheet1").Range("H1").Value = ColmnTtl

    'Figuring out the data acquiring
    Dim Ic As Integer
    Ic = -1
    Do Until Ic = 100
    Ic = Ic + 1
    If IsOdd(Ic) Then
    'INSERT EQUATION TO PULL OUT THE DATA FROM EVERY OTHER STRING
    End If
    Loop
    Ic = Ic + 1
    End Sub


    Private Sub TextBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    Dim vaFiles As Variant 'Open up browser for Txt File
    vaFiles = Application.GetOpenFilename _
    (FileFilter:="Text File (*.txt),*.txt", _
    Title:="Open File(s)", MultiSelect:=False)
    TextBox1.Text = vaFiles 'Display file location in txt box
    End Sub

    Private Sub UserForm_Activate()
    Dim FSO As Object
    Dim oApp As Object
    Dim Fname As Variant
    Dim FileNameFolder As Variant
    Dim DefPath As String
    Dim DP As Variant

    Fname = Application.GetOpenFilename(FileFilter:="Zip Files (*.zip), *.zip", _
    MultiSelect:=False)
    If Fname = False Then
    'Do nothing
    Else
    'Destination folder
    DefPath = "C:\Lab\Test\"
    If Right(DefPath, 1) <> "\" Then
    DefPath = DefPath & "\"
    End If

    FileNameFolder = DefPath
    DP = DefPath

    'Extract the files into the Destination folder
    Set oApp = CreateObject("Shell.Application")
    oApp.Namespace(FileNameFolder).CopyHere oApp.Namespace(Fname).items

    MsgBox "You find the files here: " & FileNameFolder

    On Error Resume Next
    Set FSO = CreateObject("scripting.filesystemobject")
    FSO.deletefolder Environ("Temp") & "\Temporary Directory*", True
    End If
    End Sub
    [/VBA]

Posting Permissions

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