Error Checking in Excel
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,239
    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

Some videos you may like

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
  •