Microsoft Excel Webinar

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]
    [GPS: Central time,lat,dir,lon,dir,vel,cog,trk]

    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

    Private Sub CommandButton1_Click() 
        Dim Fn As String 
        Dim ColumnCT As String 
        Dim StringArray As String 
        Fn = FreeFile() 
        Open TextBox1.Text For Input As Fn 
         '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) 
        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 
            End If 
        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", _ 
        If Fname = False Then 
             'Do nothing
             '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