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

    VB:
    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 
    
    
    Formatting tags added by mark007

Posting Permissions

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