If it is, we use this line of code to grab all the characters except the two at the very end: strFile = Left(strFile, intLength – 2) We can then check to see if strEnd is equal to vbCrLf. That’s what we do here: strEnd = Right(strFile, 2) Thus we use the Right function to grab the two characters at the very end of the string and store these in the variable strEnd.
As we noted earlier, this means checking to see if the last two characters in the file consist of a carriage return character and a linefeed character. So far so good, right? Now we need to determine whether or not the existing file has a carriage return-linefeed on the end. This value – which we’ll use in a minute – is stored in the variable intLength. Next we use the Len function to determine the length of the variable strFile (that is, how many characters are in the string). We’re done reading the file so we might as well close it.) Because we have to read the file we had to open it for reading if we later need to make changes to the file we’ll have to reopen it for writing. (Why? Well, the FileSystemObject allows you to open a file for reading or for writing. After all that we immediately close the text file. We then use the ReadAll method to read in the entire contents of the text file and store that information in a variable named strFile. We create an instance of the FileSystemObject and use the OpenTextFile method to open the file C:\Scripts\Test.txt. We start off by defining a pair of constants – ForReading and ForWriting – that we’ll use when we work with the text file. Set objFile = objFSO.OpenTextFile(“C:\scripts\test.txt”, ForWriting) Set objFile = objFSO.OpenTextFile(“C:\scripts\test.txt”, ForReading) Set objFSO = CreateObject(“Scripting.FileSystemObject”) Got all that? Here’s a script that carries out all those tasks: Const ForReading = 1 If they don’t, then we won’t do anything at all in that case there’s no reason to do anything. If those last two characters are equal to vbCrLf, we’ll chop the two characters off the string and save the new value (minus the carriage return-linefeed) back to the original text file. We’re going to check and see if the last characters have an ASCII value of 13 and an ASCII value of 10 an easy way to do that is to see if they are equal to the VBScript constant vbCrLf (which happens to be a carriage return-linefeed). Although we could probably get by with stripping away just the linefeed character we’re going to do things nice and neat and get rid of both characters. Why the last two characters? Well, the carriage return-linefeed actually consists of two separate characters: the carriage return (with an ASCII value of 13) and the linefeed (with an ASCII value of 10). We’re going to grab the last two characters stored in that variable. We’re going to open the file and read the contents of that file into a variable named strFile. So how are we going to get rid of that pesky little carriage return-linefeed? Here’s how: You might not see anything on the screen, but it’s there and it’s creating problems: That’s because there’s a carriage return-linefeed immediately following the period (as if someone typed in the first line and then pressed ENTER). Note that the cursor does not appear immediately after the period in line 1 instead, the cursor appears at the beginning of line 2. We opened the file and then pressed Ctrl+End to position the cursor at the end of the file. What does all that mean? Well, consider the simple little text file below.
Unfortunately, though, the text file invariably has a carriage return-linefeed at the end, and that carriage return-linefeed is causing problems when you try passing the contents of the file as a query. From the rest of your email you note that you’re using the FileSystemObject to read a text file, then using the contents of that text file as a query in another application. Hey, Scripting Guy! How can I remove the last carriage return-linefeed in a text file?