Importing a text file to an array in a macro
Importing a text file to an array in  a macro
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Importing a text file to an array in a macro

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Dalew
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Dale

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    Dalew
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    Dalew
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    That will do it. Thanks all and good night.

    Dale

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com