İmport txt files in excel with VBA codes

[sy43fb]

I wanna import txt files in excel with VBA. I want to import text one by one column.
İf in text file
abcd 123 x+-
32 7 k 698755 then import like below
A B C D E F G H I J K L M N
1 a b c d 1 2 3 x + -
2 3 2 7 k 6 9 8 7 6 5 5

I need to codes. Can anybody help me?

 

[sy43fb]

I make macro record it seems so

Sub Makro1()
'
' Makro1 Makro
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;D:\2019-2020 Mat deneme takip\abc.TXT", Destination:=Range("$A$1"))
.CommandType = 0
.Name = "abc"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 932
.TextFileStartRow = 1
.TextFileParseType = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 17, 22, 4, 15, 10, 2, 2, 5, _
3, 12, 10, 2, 3, 2, 3, 7, 4, 3, 2, 7, 13, 21, 7, 5, 11)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

How can I edit for 300 column.
İt seems very difficult to make for 300 columns.

 

[Legacy 456155]

If your text files are very large, you may want to load them into a variant array and then dump the array into a properly dimensioned range.
Chances are though that this will do well enough to get you started.

Sub CallFromHere()
    Application.ScreenUpdating = False
    ImportSomeTextCharByChar [a1], Application.GetOpenFilename() 'where [a1] = top/left cell
    Application.ScreenUpdating = True
End Sub

Sub ImportSomeTextCharByChar(r As Range, fileName As String)
    Dim s() As String, x As Long, y As Long, l As Long, c() As String
    With CreateObject("Scripting.FileSystemObject").OpenTextFile(fileName)
        s() = Split(.ReadAll, vbCrLf)
        .Close
    End With
   
    For x = 0 To UBound(s)
        s(x) = Replace(s(x), " ", "")
        l = Len(s(x)) - 1
        If l > -1 Then
            ReDim c(0, l)
            For y = 0 To l
                c(0, y) = Mid(s(x), y + 1, 1)
            Next
            Range(r.Offset(x), r.Offset(x, l)) = c()
        End If
    Next
End Sub

 

[sy43fb]

Thanks for your response. İt seems good but I have a problem.
Null characters not import in this way.
12 3 import in excel 12 3
A3 and A4 is empty

 

[Legacy 456155]

I don't understand and I have no way of knowing what characters are in your file. A null character to me is chr(0). I don't know what character is between "12" and "3".
Add this line of code and see if it fixes it.

Sub CallFromHere()
    Application.ScreenUpdating = False
    ImportSomeTextCharByChar [a1], Application.GetOpenFilename() 'where [a1] = top/left cell
    Application.ScreenUpdating = True
End Sub

Sub ImportSomeTextCharByChar(r As Range, fileName As String)
    Dim s() As String, x As Long, y As Long, l As Long, c() As String
    With CreateObject("Scripting.FileSystemObject").OpenTextFile(fileName)
        s() = Split(.ReadAll, vbCrLf)
        .Close
    End With
   
    For x = 0 To UBound(s)
        s(x) = WorksheetFunction.Clean(s(x))
        s(x) = Replace(s(x), " ", "")
        l = Len(s(x)) - 1
        If l > -1 Then
            ReDim c(0, l)
            For y = 0 To l
                c(0, y) = Mid(s(x), y + 1, 1)
            Next
            Range(r.Offset(x), r.Offset(x, l)) = c()
        End If
    Next
End Sub

see: s(x) = WorksheetFunction.Clean(s(x))

 

[sy43fb]

Thanks for codes.
s(x) = WorksheetFunction.Clean(s(x))
syntax error
but I can't find it.

 

[Legacy 456155]

It's in my second post about 1/2 way down in the code example. The function should remove troubling characters.

 

[sy43fb]

thanks for your interest.
I attached pictures.
your codes result is page1.gif
I wanna make like page2.gif.
txtscreen like txtscreen.gif
Thanks again. Sorry for bothering you.

 

[Legacy 456155]

abcd 123 x+-
32 7 k 698755 then import like below
A B C D E F G H I J K L M N
1 a b c d 1 2 3 x + -
2 3 2 7 k 6 9 8 7 6 5 5

I'm just following your instructions!
Your initial post led me to believe that we were gonna ignore spaces.
So remove the following line of code:

s(x) = Replace(s(x), " ", "")

Let me know if that works for you.

 

[sy43fb]

İt is exactly ı want .
Thank you very much.