Please Note: This article is written for users of the following Microsoft Word versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Word 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Word, click here: Getting Input from a Text File.

Getting Input from a Text File

by Allen Wyatt
(last updated May 6, 2017)

2

True to its BASIC roots, VBA allows you fetch input (information) from sequential files. This means you can open and read a sequential text file, loading the information from the file into string variables. The steps are simple. You only have to open the file, get the input, and then close the file. The following code is a common example of reading from a sequential file:

Dim Raw As String
Dim NumValues As Integer, J As Integer
Dim UserVals() As String

Open "MyFile.Dat" For Input As #1
Line Input #1, Raw
NumValues = Val(Raw)
ReDim UserVals(NumValues)

For J = 1 to NumValues
    Line Input #1, UserVals(J)
Next J
Close #1

You should note that the first line read from the text file (MyFile.Dat) is assumed to contain a value that indicates how many items are to be read in from the file. The Open statement is used to open the text file (MyFile.Dat) and assign it a file number, in this case the number 1. This file number is then subsequently used by various statements (such as Line Input and Close) to reference the file.

WordTips is your source for cost-effective Microsoft Word training. (Microsoft Word is the most popular word processing software in the world.) This tip (10477) applies to Microsoft Word 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Word here: Getting Input from a Text File.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Setting the AutoRecover Directory

Excel, by default, periodically writes information to AutoRecover files that can help protect your data in case Excel is ...

Discover More

Deleting a Power Management Plan

Windows allows you to create custom power management plans. When you no longer need a plan you previously defined, you can ...

Discover More

Adding Quotes

Adding quote marks is normally as simple as typing them from the keyboard. However, if you want to add quote marks around ...

Discover More

The First and Last Word on Word! Bestselling For Dummies author Dan Gookin puts his usual fun and friendly candor back to work to show you how to navigate Word 2013. Spend more time working and less time trying to figure it all out! Check out Word 2013 For Dummies today!

More WordTips (ribbon)

Document Shows as 'In Use' by Another User

Word tries to constantly track who is using various documents, in order to prevent two users from clashing in their edits to ...

Discover More

Last Document Saves Not Saved

Click the Save button and you expect your document to be saved, right? What if you later discover it wasn't really saved? ...

Discover More

Periodically Delete TMP Files

After using Word for a while, you may notice some "litter" of unused files on your hard drive. This tip explains how those ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 - 2?

2017-05-09 04:48:08

Alan Elston

Hi DPG,
I think EOF Function is often used in similar codes.
I expect Allen is looking in particular at a way to do this putting the data into an Array without having to RE Dim Preserver the Array each time in the Loop to adjust it size. ( Normally you would not have, as far as I know, a simple way of determining the number or records ( rows ) in a text file).
So Allen suggest you put this number as the first row in your text File. You then get that number first . You get that number as a string. But VBA is kind with strings that look like numbers and will usually accept it as a number as it probably would in this case.
Actually, Allen uses the val function so allows the number to be returned from like a first row in the text file of
"10 is the Number of rows I have here" – this would return 10
That number he then uses in the sizing of his Array.

Another alternative would be to use the EOF Function way in conjunction with adding the retrieved rows each time in the loop to a dictionary or a ListArray or collection object , etc. Then you can often get the final Array out from the collection object using some Method or Property
In the practice you would probably need to test speeds etc., too se if there are any advantages over just re sizing an Array in the Loop

This is just one way to use the EOF Function and get row information from a text file into an Array without having to re size the array each time in the Loop:

Sub ListArrayEOFWay() ' https://wordribbon.tips.net/T010477_Getting_Input_from_a_Text_File.html
Dim Raw As String
Dim UserVals() As Variant
Open "MyFile.txt" For Input As #1
With CreateObject("System.Collections.ArrayList")
Do While EOF(1) = False
Line Input #1, Raw
.Add Raw
Loop
Let UserVals() = .ToArray() ' The Method .ToArray writes all elements of the ArrayList into a 1-dimensional Array, ' http://www.snb-vba.eu/VBA_Arraylist_en.html#L_9.3
End With
Close #1
End Sub

Alan


2017-05-08 04:34:38

DPG

Would it be possible to use the EOF function? This would remove the need to specify how many items to read from the file.


This Site

Got a version of Word that uses the ribbon interface (Word 2007 or later)? This site is for you! If you use an earlier version of Word, visit our WordTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.