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

Updated July 20, 2017

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.

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

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."

Type the following to declare your variables:

Dim txtFileNum As Integer

Dim data Array(5) As String

Dim xLoop As Integer

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.

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

For xLoop = 1 To 4

Next xLoop

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

For xLoop = 1 To 4

Next xLoop

Type the following to close the text file:

Close #txtFileNum

Press "F5" to run your subroutine.

Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author

Jaime Avelar is a professional writer whose programming articles appear on various websites. He has been a software programmer since 2000. Avelar holds a Master of Science in information systems from the University of Texas at Arlington.