How to Write Visual Basic Array Data to a Text File in Excel

Written by jaime avelar
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Write Visual Basic Array Data to a Text File in Excel
Write Visual Basic to save a data array to a text file. (0101000100111 image by chrisharvey from Fotolia.com)

Microsoft Visual Basic for Applications (VBA) was developed by Microsoft and it's commonly used to automate routine tasks within Microsoft Office applications. A data array in programming is a variable that can have multiple elements to hold information. An array is useful when you need to save 100 data points--instead of creating 100 different variables you can save the data points in one array variable. In a few steps you can save information from Excel to an array and write it to a text file.

Skill level:
Easy

Other People Are Reading

Instructions

  1. 1

    Start Microsoft Office Excel and type "1" in "A1." Type "2" in "A2," "3" in "A3" and "4" in "A4."

  2. 2

    Select the "Developer" tab and click "Visual Basic." Right-click "Microsoft Excel Objects" and select "Insert," then click "Module."

    Type "Private Sub saveDataArray()" to create a subroutine and press "Enter."

  3. 3

    Type the following to declare your variables:

    Dim txtFileNum As Integer

    Dim data Array(5) As String

    Dim xLoop As Integer

  4. 4

    Type the following to create a new text file:

    Open "C:\myTextFile.txt" For Append As #txtFileNum

    Replace "C:\myTextFile.txt" with the file name of your choice.

  5. 5

    Type the following to loop through the data in Excel and save it to your array:

    For xLoop = 1 To 4

        Range("A" & xLoop).Select
    
       data Array(xLoop) = Range("A" & xLoop).Value
    

    Next xLoop

  6. 6

    Type the following to loop through the array and save the data in your text file:

    For xLoop = 1 To 4

        Print #txtFileNum, data Array(xLoop)
    

    Next xLoop

  7. 7

    Type the following to close the text file:

    Close #txtFileNum

    Press "F5" to run your subroutine.

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.