Private Sub CommandButton1_Click()
Dim i As Long, x, xx, s As String, strFile As String, j As Long, k As Long, Z
Application.DisplayAlerts = False
strFile = Application.GetOpenFilename("Text Files,*.txt")
If strFile = "False" Then Exit Sub
s = CreateObject("scripting.filesystemobject").getfile(strFile).openastextstream.readall
x = Split(s, vbCrLf)
With Sheets("Sheet2")
.Range("A2").Resize(UBound(x), 1).Value = Application.Transpose(x)
.Range("A1").Resize(UBound(x) + 1).AutoFilter 1, "*_*", xlOr, ""
.Range("A2").Resize(UBound(x)).SpecialCells(12).Delete
.Range("A1").Resize(UBound(x) + 1).AutoFilter
Z = .Range("A1").CurrentRegion.Offset(1)
.Cells.ClearContents
End With
With Sheets("Sheet3")
For k = 1 To 12
.Cells(1, k).Value = Split(Z(k, 1), ":")(0)
Next k
End With
ReDim xx(0 To ((UBound(Z) - 1) / 12) - 1, 1 To 12)
j = 1
For i = 0 To ((UBound(Z) - 1) / 12) - 1
xx(i, 1) = Trim(Split(Z(i + j, 1), ":")(1))
xx(i, 2) = Trim(Split(Z(i + j + 1, 1), ":")(1))
xx(i, 3) = Trim(Split(Z(i + j + 2, 1), ":")(1))
xx(i, 4) = Trim(Split(Z(i + j + 3, 1), ":")(1))
xx(i, 5) = Trim(Split(Z(i + j + 4, 1), ":")(1))
xx(i, 6) = Trim(Split(Z(i + j + 5, 1), ":")(1))
xx(i, 7) = Trim(Split(Z(i + j + 6, 1), ":")(1))
xx(i, 8) = Trim(Split(Z(i + j + 7, 1), ":")(1))
xx(i, 9) = Trim(Split(Z(i + j + 8, 1), ":")(1))
xx(i, 10) = Trim(Split(Z(i + j + 9, 1), ":")(1))
xx(i, 11) = Trim(Split(Z(i + j + 10, 1), ":")(1))
xx(i, 12) = Trim(Split(Z(i + j + 11, 1), ":")(1))
j = j + 11
Next i
With Sheets("Sheet3")
.Cells(2, 1).Resize((UBound(Z) - 1) / 12, 12).Value = xx
.Columns.AutoFit
.Select
End With
MsgBox "Data has been imported.", vbInformation
Application.DisplayAlerts = True
End Sub
Bookmarks