Importing a text file to an array in a macro

dale3290

New Member
Joined
Apr 16, 2002
Messages
7
How can I open multiple files and place the contents in an array rather then to just place it in a worksheet.

Dale
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
 
Upvote 0
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
 
Upvote 0
Try this amended code;<pre/>
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</pre>



_________________
Kind Regards,<font size=+2><font color="red"> I<font color="blue">van<font color="red"> F M</font color="blue">oala</font></font></font><A HREF= "http://www.gwds.co.nz/"><font color="green">http://www.gwds.co.nz/excel_files.html - Under Constru
This message was edited by Ivan F Moala on 2002-04-19 19:56
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top