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

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:


  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

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

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