Hi,

Am Tue, 1 Jan 2019 16:28:36 -0800 (PST) schrieb :

It gave a compile error

here's another suggestion:

Sub TransposeText()
Dim myFile As String, myStr As String
Dim varData As Variant, varTmp As Variant, varOut() As Variant
Dim varHeader As Variant
Dim i As Long, j As Long, n As Long
Dim objFSO As Object

varHeader = Array("Location", "Store ID", "Stock ID", "Address", "Xroad
ID", "ID")
Set objFSO = CreateObject("Scripting.FileSystemObject")

myFile = Application.GetOpenFilename
If myFile = "" Then Exit Sub

myStr = objFSO.opentextfile(myFile).readall
varData = Split(myStr, "Location = ")
n = 1
With Application
For i = 1 To UBound(varData)
varTmp = Split(varData(i), " = ")
For j = LBound(varTmp) To UBound(varTmp) - 1
ReDim Preserve varOut(1 To 6, 1 To n)
varOut(j + 1, n) = .Clean(Replace(Replace(varTmp(j), varHeader(j + 1), ""), """", ""))
Next
varOut(6, n) = .Clean(varTmp(5))
n = n + 1
Next
End With

With Sheets("Sheet1")
With .Range("A1").Resize(1, UBound(varHeader) + 1)
.Value = varHeader
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
.Range("A2").Resize(UBound(varOut, 2), 6) = Application.Transpose(varOut)
.Range("A:F").EntireColumn.AutoFit
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016