I have a text file with 1000 data entries (only integers).There is one entry per line in the text file. I was wondering how to transfer that data into an array in VBA.
Thank you for taking the time to respond.
-
Possible duplicate of How to fetch text file into array in VBAVictor K– Victor K2018-04-19 16:08:37 +00:00Commented Apr 19, 2018 at 16:08
Add a comment
|
2 Answers
Also we can do this without looping:
Sub Test()
Dim FSO As Object, MyFile As Object
Dim FileName As String, Arr As Variant
FileName = "C:\Test\Test.txt" ' 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
Comments
Just save the path to your text file into a variable called FilePath and run this code block.
Dim arInt(1 to 1000) as Integer
Dim intCount as Integer
Set objFSO = CreateObject("Scripting.FileSystemObject")
With = objFSO.OpenTextFile(FilePath, ForReading)
intCount = 1
Do While .EOF = False AND intCount < 1001
arInt(intCount) = Val(.readline)
intCount = intCount + 1
Loop
The Val function turns the string into an number value and then vba casts it to an integer for you. Afterwards, you have 100 int values in an array. The code will stop once the file is compete or the array has 1000 values in it.
