Hey, Scripting Guy! How can I read just the last line of a text file?
— BM
— BM
Hey, BM. You know, if you’ve ever wondered how Hey, Scripting Guy! differs from other daily columns (like, say, Dear Abby), well, here’s one way. Suppose someone writes Dear Abby and says this: Post and wire fence.
Dear Abby:
My life is all messed up and I need to do something about it. How can I turn my life around and be happy again?
Desperate in Dayton
My life is all messed up and I need to do something about it. How can I turn my life around and be happy again?
Desperate in Dayton
Most likely Dear Abby would never give Desperate an answer like this:
Dear Desperate:
Well, you can’t. Sorry.
Well, you can’t. Sorry.
So how do you think the Scripting Guys will answer your question about reading just the last line of a text file? You got it:
Dear BM:
Well, you can’t. Sorry.
Well, you can’t. Sorry.
But wait, don’t go yet. It’s true that Dear Abby would never say, “You know, Desperate, I can’t help you become happy. But here’s a workaround that will make it look like you’re happy.” The Scripting Guys, however, have no such qualms. In other words, we can’t give you a script that reads just the last line of a text file. But here’s a script that will make it look like you read just the last line of a text file:
The problem we’ve run into here is that the FileSystemObject (the scripting object used to work with text files) knows only one direction: forward. It has to start at the beginning of a file, and it can only continue on towards the end of that file. You can’t specify an alternate starting location and you can’t read backwards (from the end to the beginning). In fact, you can’t even re-read a file: if you reach the end there’s no way to start all over except by closing and re-opening the file. That’s why workarounds are pretty much par for the course when it comes to text files.
The workaround here is that we actually do read the entire script from beginning to end. However, we keep track of only the very last line we’ve read. When we reach the end of the file, we’ll have a variable that contains the value of the last line read; that will also be the last line in the file. When we echo back the value of that variable it will look like we read only the last line (especially because, if nothing else, the FileSystemObject is pretty fast). We didn’t – we actually read the entire file – but no one will ever know. It’ll be our little secret.
As for the code itself, we begin by defining a constant named ForReading and setting the value to 1; we’ll use this constant to tell the FileSystemObject that we want to open a file for reading. We then create an instance of the Scripting.FileSystemObject, and use the OpenTextFile method to open the file C:ScriptsTest.txt. That brings us to this block of code:
All we’re doing here is reading the file, line by line, until we reach the end (that is, until we’re at the end of the file stream). Each time we read in a line we replace the value of the variable strLine with the text we just read. For example, suppose our text file consists of three lines:
Inside our loop we read in line 1, and thus assign the value A to strLine. We loop around and read the second line, meaning the value B gets assigned to strLine. We loop around again and assign the value C to strLine. Because we’ve reached the end of the file strLine retains the value C, which just happens to be the last line in the file. We then close the file and echo the value of strLine. As far as anyone knows, all we did was read – and report back – the value of the last line in the file.
Yes, very sneaky.
Enemy within mods. Admittedly, there’s one potential problem with this script. Suppose you have a file that has several blank lines tacked on to the end. Our script will dutifully report back nothing (a blank value) as the last line in the text file. That’s what it should do: after all, the last line in the file is blank. But suppose this is a log file of some type and, for whatever reason, the application that creates this log always throws a few blank lines onto the end of the file. In that case what you’re probably really interested in is the last non-blank line in the file. Here’s a modified version of the script that uses the Len function to check the length of each line that’s read in. If the length is equal to 0, that means it’s a blank line, and the value is not stored in the variable strLine:
Because it affect our behavior, our attitudes towers work, the way we look at the world, etc. Cultural religion in business international.
Eat your heart out, Dear Abby!
I have several subfolders. In each there are text files. It is possible to group text files in one excel file in a such way that there will be one file per excel tab. I have designed code to do this task.
However, while looping through subfolders, macros saves data from the files in previous subfolders, but I want to save data from files that come from particular sub-folder. Would you be so kind to explain me where is my mistake?
Thank you!
EDIT
here is working code
End Sub
mr.M
mr.Mmr.M22033 gold badges1414 silver badges3131 bronze badges
1 Answer
If you want each subfolder's data to be in a separate workbook, then you need to move your
mr.Rebandmr.Rebandnew_workbook
definition inside your For Each obj_sub_folder
loop, and also close that workbook after saving: 2,42122 gold badges1111 silver badges1919 bronze badges
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Not the answer you're looking for? Browse other questions tagged excelvbaexcel-vba or ask your own question.
-->The ReadAllText method of the
My.Computer.FileSystem
object allows you to read from a text file. The file encoding can be specified if the contents of the file use an encoding such as ASCII or UTF-8.If you are reading from a file with extended characters, you will need to specify the file encoding.
Note
To read a file a single line of text at a time, use the OpenTextFileReader method of the
My.Computer.FileSystem
object. The OpenTextFileReader
method returns a StreamReader object. You can use the ReadLine method of the StreamReader
object to read a file one line at a time. You can test for the end of the file using the EndOfStream method of the StreamReader
object.To read from a text file
- Use the
ReadAllText
method of theMy.Computer.FileSystem
object to read the contents of a text file into a string, supplying the path. The following example reads the contents of test.txt into a string and then displays it in a message box.
To read from a text file that is encoded
- Use the
ReadAllText
method of theMy.Computer.FileSystem
object to read the contents of a text file into a string, supplying the path and file encoding type. The following example reads the contents of the UTF32 file test.txt into a string and then displays it in a message box.
Robust Programming
The following conditions may cause an exception:
- The path is not valid for one of the following reasons: it is a zero-length string, it contains only white space, it contains invalid characters, or it is a device path (ArgumentException).
- The path is not valid because it is
Nothing
(ArgumentNullException). - The file does not exist (FileNotFoundException).
- The file is in use by another process or an I/O error occurs (IOException).
- The path exceeds the system-defined maximum length (PathTooLongException).
- A file or directory name in the path contains a colon (:) or is in an invalid format (NotSupportedException).
- There is not enough memory to write the string to buffer (OutOfMemoryException).
- The user lacks necessary permissions to view the path (SecurityException).
Do not make decisions about the contents of the file based on the name of the file. For example, the file Form1.vb may not be a Visual Basic source file.
Verify all inputs before using the data in your application. The contents of the file may not be what is expected, and methods to read from the file may fail.
See also
I have a text file with 1000 data entries (only integers).There is one entry per line in the text file. I was wondering how to transfer that data into an array in VBA.
Thank you for taking the time to respond.
Vba Read Text File Delimited
Community♦
user5916405user5916405
2 Answers
FadiFadi2,67322 gold badges1212 silver badges3535 bronze badges
Just save the path to your text file into a variable called FilePath and run this code block.
The Val function turns the string into an number value and then vba casts it to an integer for you.Afterwards, you have 100 int values in an array.The code will stop once the file is compete or the array has 1000 values in it.
CoveGeekCoveGeek
Not the answer you're looking for? Browse other questions tagged vbaexcel-vbaexcel or ask your own question.
-->The
My.Computer.FileSystem
object provides methods to open a TextReader and a TextWriter. These methods, OpenTextFileWriter
and OpenTextFileReader
, are advanced methods that do not appear in IntelliSense unless you select the All tab.To read a line from a file with a text reader
- Use the
OpenTextFileReader
method to open the TextReader, specifying the file. This example opens the file namedtestfile.txt
, reads a line from it, and displays the line in a message box.
Robust Programming
The file that is read must be a text file.
Do not make decisions about the contents of the file based on the name of the file. For example, the file Form1.vb may not be a Visual Basic source file.
Verify all inputs before using the data in your application. The contents of the file may not be what is expected, and methods to read from the file may fail.
.NET Framework Security
To read from a file, your assembly requires a privilege level granted by the FileIOPermission class. If you are running in a partial-trust context, the code might throw an exception due to insufficient privileges. For more information, see Code Access Security Basics. The user also needs access to the file. For more information, see ACL Technology Overview.