Macro for importing multiple .txt files into multiple workshhets

[Amstrad]

Hi all

I found the following macro that allows you to import .txt files into multiple worksheets in excel:

Sub marble()
Dim z  As Long, e As Long
Dim f As String, g As String
Sheets("Sheet1").Select
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.txt")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
g = Left(Sheets("Sheet1").Cells(e, 1), Len(Sheets("Sheet1").Cells(e, 1)) - 4)
Worksheets.Add
ActiveSheet.Name = g
With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;" & Sheets("Sheet1").Cells(1, 2) & Sheets("Sheet1").Cells(e, 1), _
        Destination:=Sheets(g).Range("A1"))
        .Name = "deep"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .Refresh BackgroundQuery:=False
    End With
    
    
Next e
MsgBox "collating is complete."
End Sub

The only issue is that when it imports it lumps all the data from the txt file together into column A with no spaces instead of having separate columns.

How can I fix this?

Many thanks

 

[AlphaFrog]

You probably want to use the Text to Columns feature in Excel. If you do a web search for Excel Text to Columns tutorial, you'll find a lot of help.

 

[Amstrad]

worked a treat. thanks.