Importing a text file to an array in a macro

[dale3290]

How can I open multiple files and place the contents in an array rather then to just place it in a worksheet.

Dale

[Damon Ostrander]

Hi Dale,

To do this involves direct I/O in VBA. I won't attempt to provide an example because of not knowing your file formats, but you can read up on this capability in the VBA helps. Look at Open, Close, Input #, Line Input #, and EOF statements in VBA. You will find that it is easy to read CSV formats, but also non-CSV text formats and binary formats, and even Random Access files if the records are fixed length.

[dale3290]

Damon:

Thanks for your reply. I have come up with the following which reads the file ok, but I need to put it in an array and not in a single variable. The data consists of 537 lines in each file. I need to put each line into a new element in the array. Any further thoughts.


Dim TextArray
Sheets("Test").Select
Open "C:QP2-SSG-NAICSSGC.SSG" For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextArray ' Read line into variable.
Debug.Print TextArray ' Print to the Immediate window.
Loop
Close #1 ' Close file.

End Sub

[Ivan F Moala]

Try this amended code;

Sub ReadtextIntoArray()

'Damon:



'Thanks for your reply.

'I have come up with the following which reads the file ok,

'but I need to put it in an array and not in a single variable.

'The data consists of 537 lines in each file.

'I need to put each line into a new element in the array.

'Any further thoughts.





Dim TextArray()

Dim x As Double



Sheets("Test").Select

Open "C:QP2-SSG-NAICSSGC.SSG" For Input As #1 ' Open file.



Do While Not EOF(1)             ' Loop until end of file.

    ReDim Preserve TextArray(x) ' Preserve the Array

    Line Input #1, TextArray(x) ' Read line into variable.

    x = x + 1                   ' increment array count

Loop



Close #1                        ' Close file.



For x = 0 To UBound(TextArray())

    MsgBox TextArray(x)         'Test to see if we have the variables

Next

End Sub

_________________
Kind Regards,
Ivan F Moala
http://www.gwds.co.nz/excel_files.html - Under Constru

[ This Message was edited by: Ivan F Moala on 2002-04-19 19:56 ]

[dale3290]

That will do it. Thanks all and good night.

Dale