Save Txt file to a one dimensional array

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.