Excel Macro VBA coding

[DaveEricsson]

Hi All,
Please, help on VBA coding.


I just have a log file .txt format and need to extract using Excel like the above snapshot format.

 

[EFANYoutube]

Hi Whats is the file delimited with?
comma, tab ?

 

[DaveEricsson]

Hi Whats is the file delimited with?
comma, tab ?

Tab

 

[EFANYoutube]

Hi Dave

Be careful with the below code as your txt file can be inconsistant which will cause errors
Make sure there are no trailing tabs or anything
This will dump to Sheet1(VBA Name)

Sub WriteCSVtoExcel()

    Dim arr

    arr = FileToArray(GetFilePath)

    For r = 1 To UBound(arr) - 1
        For c = 1 To UBound(arr, 2)
            Sheet1.Cells(r, c) = arr(r, c)
        Next c
    Next r

End Sub



Function FileToArray(fpath) As Variant

    Dim txt As String, arr, d, r, c, rv(), u

    'read in the entire file
    With CreateObject("scripting.filesystemobject").opentextfile(fpath)
        txt = .readall()
        .Close
    End With

    arr = Split(txt, vbCrLf) 'split lines to an array

    u = UBound(Split(arr(0), vbTab)) 'assume all lines have same # of fields
    ReDim rv(1 To UBound(arr) + 1, 1 To u + 1) 'size the output array

    'fill the output array
    For r = 0 To UBound(arr)
        d = Split(arr(r), vbTab)
        If arr(r) = "" Then Exit For
        For c = 0 To u
            rv(r + 1, c + 1) = d(c)
        Next c
    Next r

    FileToArray = rv

End Function



Function GetFilePath()

Dim fd As Office.FileDialog
Dim strFile As String
 
Set fd = Application.FileDialog(msoFileDialogFilePicker)
 
With fd
 
    .Filters.Clear
    .Filters.Add "Text Files", "*.txt", 1
    .Title = "Choose a Text file"
    .AllowMultiSelect = False
 
    '.InitialFileName = "C:\VBA Folder"
 
    If .Show = True Then
 
        GetFilePath = .SelectedItems(1)
        'Debug.Print GetFilePath
    End If
 
End With

End Function

 

[DaveEricsson]

But, the output is empty.

 

[DaveEricsson]

But, the output is empty.

But, the output is empty.

Log file fetched from the system is like here in below and need to convert excel format using macro program

 

[GWteB]

@EFANYoutube, in case a line does not contain a TAB at all, your approach will fail.
@DaveEricsson, the code below might be of some help.

Sub Plot_CSV()

    Dim oWs     As Worksheet
    Dim sFile   As String

    sFile = PickFile
    If Len(sFile) > 0 Then
        Set oWs = ThisWorkbook.Worksheets.Add
        With oWs.QueryTables.Add(Connection:="TEXT;" & sFile, Destination:=oWs.Range("$A$1"))
            .AdjustColumnWidth = True
            .TextFilePlatform = 850
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTabDelimiter = True
            .Refresh BackgroundQuery:=False
        End With
    End If
End Sub

Function PickFile() As String
    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Clear
        .Filters.Add "Text Files", "*.txt", 1
        .Title = "Choose a Text file"
        .AllowMultiSelect = False
        If .Show Then
            PickFile = .SelectedItems(1)
        End If
    End With
End Function