With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Add "Log Files", "*.log", 1
If .Show = -1 Then
FullPath = .SelectedItems.Item(1) 'selected text file full path
End If
End With
If FullPath = "" Then Exit Sub 'if Cancel pressed, the code stops
textFileLocation = Left(FullPath, InStrRev(FullPath, "\") - 1)
fileName = Dir(textFileLocation & "\*.log") 'first text file name
fileDate = Format(FileDateTime(textFileLocation), "mm/dd/yyyy")
If fileName "" Then
Do While fileName "" 'loop since there still are not processed text files
'Get File Name
sFullFilename = Right(fileName, Len(fileName) - InStrRev(fileName, "\"))
sFileName = Left(sFullFilename, (InStr(sFullFilename, ".") - 1))
'place the content of the text file in an array (split by VbCrLf):
arrTxt = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(textFileLocation & "\" & fileName, 1).ReadAll, vbCrLf)
lastR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row 'the row where to paste the array content
'drop the transposed array content:
ws.Range("A" & IIf(lastR = 1, lastR, lastR + 1)).Resize(UBound(arrTxt) + 1, 1).Value = Application.Transpose(arrTxt)
'apply TextToColumns to whole returned data:
ws.Columns(1).TextToColumns Destination:=ws.Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(43, 1), Array(70, 1)), TrailingMinusNumbers:=True