Consulting

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

    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
    Last edited by Aussiebear; 04-23-2023 at 07:04 PM. Reason: Adjusted the code tags

Posting Permissions

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