Written by Allen Wyatt (last updated March 11, 2021)
This tip applies to Word 2007, 2010, 2013, and 2016
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.
Note:
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.
Learning Made Easy! Quickly teach yourself how to format, publish, and share your content using Word 2013. With Step by Step, you set the pace, building and practicing the skills you need, just when you need them! Check out Microsoft Word 2013 Step by Step today!
Word allows you to create custom document properties that stay with a document and can be inserted through the use of ...
Discover MoreYou can add custom properties to a document to help with all sorts of file management tasks. If you want to copy these ...
Discover MoreYour macros can easily add information to the end of an existing text file. This is done by opening the target file in ...
Discover MoreFREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-05-09 04:48:08
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.
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.
Visit the WordTips channel on YouTube
FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2023 Sharon Parq Associates, Inc.
Comments