Import txt, csv, xls files into excel using vba

I've been trying to open text files into excel using vba. I want to access any text, csv, xls file and import it fast by clicking one button (located on the top of "Dust Raw Data" and "VOC Raw Data" worksheets - see attached. I generated a vba code, but the issue is that when I run the code (press the import button - on the Dust Raw and VOC Raw sheets), the values containing numbers do not register and they are assumed as text instead of numbers. Additionally, I want to extract the time only from the VOC Raw Data sheet (using the PID txt file- see attached) to the "VOC table" sheet which must be in the format of hh:mm AM/PM, but instead; excel gives me as m/d/yyyy h:mm. Excel cannot understand what I`m trying to achieve so it copies the whole thing as Ex: 05/01/23 06:05:55 instead of 6:05 AM. Please let me know if I need to add more to my code and if so, what lines. TIA!!

(Note: I`m aware of power query, but I want to use vba in this particular case)

The VBA Code:

Sub ImportText3()

    Dim ThisLine As Variant
    Dim fileFilterPattern As String
    Dim n As Long
    
    ' Just look at text files
    'fileFilterPattern = "Text Files *.txt,*.txt"
    fileFilterPattern = "Text Files (*.txt; *.csv),*.txt;*.csv"
    ThisLine = Application.GetOpenFilename(fileFilterPattern)
    
    If ThisLine = False Then
        MsgBox "No file selected."
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    
    Open ThisLine For Input As #1
    ' Look at each line of the file
    Do Until EOF(1)
        Line Input #1, ThisLine
        ' If the line contains something
        If Len(ThisLine) > 0 Then
            ' Replace any commas with tabs (ASCII character 9)
            ThisLine = Replace(ThisLine, ",", Chr(9))
            ' Divide string into an array by tab
            ThisLine = Split(ThisLine, Chr(9))
            ' Copy array to a resized range
            ActiveCell.Offset(n, 0).Resize(1, UBound(ThisLine) + 1).Value = ThisLine
        End If
        ' Point to next cell below
        n = n + 1
    Loop
    
    ' Done so close the file for input
    Close #1

    Application.ScreenUpdating = True

End Sub