Save Txt file to a one dimensional array

Rather than re-invent the wheel, see: https://stackoverflow.com/questions/...n%20an%20array.

Or Google: excel vba to read entire text file into an array for more suggestions.

Getting an error on line

Range("A1").Resize(UBound(Arr) + 1, 1).Value = Application.Transpose(Arr)

that states: Run -time error '1004' " Application -defined or object -defined error

Also, Not sure this is what I am looking for. I want to have each line as it's own "row" in the array. So Arr(0) would equal the first line of text, Arr(1) would equal the second line of text and so on.

Provide a sample workbook.

Sub Test()

 Dim FSO As Object, MyFile As Object
 Dim FileName As String, Arr As Variant

 FileName = "Z:\xxxxxxx" ' change this to your text file full name
 Set FSO = CreateObject("Scripting.FileSystemObject")
 Set MyFile = FSO.OpenTextFile(FileName, 1)
 Arr = Split(MyFile.ReadAll, vbNewLine) ' Arr is zero-based array


 'For test
 'Fill column A from this Array Arr

 Range("A1").Resize(UBound(Arr) + 1, 1).Value = Application.Transpose(Arr)

End Sub

Please provide a copy of the text file to be imported.

Try using longlong for 64 bit

Sub perhab()
 Dim a,i as longLong,j as longlong,c as long,k,n as long
 a = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile("C:\your Path").ReadAll, vbLf)
 Redim b (1 to rows.count,1 to 400)
 c = 1
 For i = 0 to Ubound(a)-1
   If j > rows.count-2 then
     j = 1 : c = c + 1
   End if
   j = j + 1 : k = j : n = application.Max(k,n)
   b(k,c) = a(i)
 Next i
 [A1].resize(n,c).value = b
  Erase b
End sub

Subroutines to read a complete/entire text file

Change the source file and location as required.

Try this:

Option Explicit

' Subroutines to read a complete/entire text file into:
' 1. A variant variable and then split it
' 2. A cell on a worksheet
'
' Both use the ReadTextFile User Defined Function
'

Sub Test_ReadTextFile_Into_Variable()

Dim awf As WorksheetFunction: Set awf = Application.WorksheetFunction
Dim vInput, vArray

' change file name and location as required
vInput = ReadTextFile("C:\Test\General Toy Sale 26-05-2022.csv")
vArray = Split(vInput, Chr(10))

Range("A1").Resize(UBound(vArray)) = _
    awf.Transpose(vArray)
With Range("A1")
    .ColumnWidth = 120
    .EntireColumn.Rows.AutoFit
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlTop
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With

End Sub

Sub Test_ReadTextFile_Into_Cell()

With Range("A1")
    ' change file name and location as required
    .Value = ReadTextFile("C:\Test\General Toy Sale 26-05-2022.csv")
    .ColumnWidth = 120
    .EntireColumn.Rows.AutoFit
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlTop
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With

End Sub


' Uncredited as, unfortunately, I have no idea where I got this routine.
' I found it in a solution for rizmomin back in 2014 

' Sometimes you just want to read in a complete text file
' from your PC without muddling through all the records
' (i.e. text lines) one by one. When you open a text file
' in binary mode, such things like EOL won't get special
' attention and you can read in the text as one big chunk.
'
' This function takes the path and name of a text file as
' input and returns the text of this file as a string:

' Reads a text file and returns the text as string
' i_FileName contains full name (i.e. with path) of the file to be read

Public Function ReadTextFile(ByVal i_FileName As String) As String

Dim myFNo As Integer  'file number to open file
Dim myText As String  'string where text gets read into

'only open file if it can be found
If Dir(i_FileName, vbNormal) <> "" Then
    'obtain the next available file number
    myFNo = FreeFile
    'open file in binary mode
    Open i_FileName For Binary As #myFNo
    'initialize string to receive text with
    'as many spaces as the file has bytes
    myText = Space(LOF(myFNo))
    'read everything at once
    Get #myFNo, , myText
    'close file
    Close #myFNo
    'return text
    ReadTextFile = myText
End If
End Function

Sample workbook and test file to play with. Also contains a query to load the test file.

Found the source for the function.

LIVEJOURNAL
VBA_CORNER
Dated 24 August 2009

https://vba-corner.livejournal.com/5311.html

First sub is doing the same as my suggestions. But the transpose can handle around 36k records or less…
Since here are over a million records, you can’t load it in one column at all.