Replace specific or last line of text file from Excel VBA

[frateg8r]

I've been able to modify some code from another forum to accomplish 1 of 2 parts of my task: namely, to change the date in both the first AND last lines of a text file. The code successfully replaces the first line of the text file with the desired string, including today's date.

The problem comes with replacing the last line; I've tried several methods of accomplishing this, including attempting to replace the last 20 characters in the text file, but after several hours of experimentation have met with no success. I understand that parsing text files is not necessarily simple in Windows, and it seems that finding the last line is much more difficult than finding the first one. (I've also tried what I thought was a relatively logical reversal of the functional code to identify the first line, but that did not work.)
[Note: the date in the existing line varies, and so cannot be called specifically as part of the string; I understand that wildcard searches of text files cannot be used via VBA, so using such to identify the last line is out.]

Can anyone offer some guidance for me on this? I had wanted to solve this challenge on my own, but after several hours, I'm simply out of time and need to move on. ANY help is appreciated.

Here's the code I have:

'http://www.excelbanter.com/showthread.php?t=427791
Dim vSz As Variant, vFilename As Variant
Dim strFirstLineNew As String, strFirstLineOld As String, sFileText As String

'Get the file contents
vFilename = "C:\Users\Bob\Desktop\30587AMSQUERY.in"
sFileText = ReadTextFileContents(CStr(vFilename))

'Parse the first line from the file
For Each vSz In Split(sFileText, vbCrLf)
If Not vSz = Empty Then strFirstLineOld = vSz: Exit For
Next vSz

'Replace the first line with new headings
strFirstLineNew = "A1001ABCTESTME" & Format(DateSerial(Year(Date), Month(Date), Day(Date)), "MMDDYY") & "     CQ"
sFileText = Replace(sFileText, strFirstLineOld, strFirstLineNew)
WriteTextFileContents sFileText, CStr(vFilename), False

Also, I'm curious to know what the original script writer means by using "vSz" as a variant. I get the use of the small 'v', but wonder what 'Sz' refers to - it does not seem obvious to me. Does it have a predefined meaning within VBA, since it is only declared as a variant? I don't see how VBA would know that it equals a line of text (unless the vbCrLf in Split(FileText, vbCrLf) acts as a delimiter, thereby declaring that each string between delimiters constitutes a line/string?)

 

[frateg8r]

I'm delighted to report I was, after all, able to resolve this challenge on my own. I opted to replace the last 6 characters of the text file instead of the last line, and after a bit of tweaking some sample code, SUCCESS! Thank you to those who may have had a solution for me but did not post, because it left me to keep trying!

Here's the final script that includes both the first line replacement, and the last 6 characters replacement.

Sub GenerateACEQueries()
'Script sourced from
'http://www.excelbanter.com/showthread.php?t=427791
'http://www.ozgrid.com/forum/showthread.php?t=181679

    Dim strFileNum As String
    Dim rCell As Range

'Declarations for parsing first line in text file (opening date)
    Dim vSz As Variant, vFilename As Variant
    Dim strFirstLineNew As String, strFirstLineOld As String, sFileText As String

'Declarations for parsing last 6 characters (closing date)
    Dim Arr() As String
    Dim SrchString As String
    Dim StrVal As String
    Dim strLastDateNew As String
    
'Passing selected file numbers to script
For Each rCell In Selection.Cells
    strFileNum = rCell.Value

'Get the file contents
    vFilename = "C:\Users\Bob\Desktop\" & strFileNum & "AMSQUERY.in"
    sFileText = ReadTextFileContents(CStr(vFilename))

'Parse the first line from the file
    For Each vSz In Split(sFileText, vbCrLf)
    If Not vSz = Empty Then strFirstLineOld = vSz: Exit For
    Next vSz

'Replace the first line with new headings
    strFirstLineNew = "A1001BDUTESTME" & Format(DateSerial(Year(Date), Month(Date), Day(Date)), "MMDDYY") & "     CQ"
    sFileText = Replace(sFileText, strFirstLineOld, strFirstLineNew)

'Parse the closing date (last 6 characters)
    SrchString = "Z1001BDU      "
    Arr = Split(sFileText, SrchString)
    On Error Resume Next
    StrVal = Left(Arr(1), 6)

'Replace the closing date with the new date
    strLastDateNew = Format(DateSerial(Year(Date), Month(Date), Day(Date)), "MMDDYY")
    sFileText = Replace(sFileText, StrVal, strLastDateNew)
    
'Next line saves ACE Query text file with revisions
    WriteTextFileContents sFileText, CStr(vFilename), False

'-----This code copies the revised ACE Query to the outbound MQ folder
'Next line commented out during testing
'FileCopy "Z:\Documents\AMS Query Holding File\" & strFileNum & "AMSQUERY.in", "F:\MQ\IN\" & strFileNum & "AMSQUERY.in"
'Next line commented out during production
    FileCopy "C:\Users\Bob\Desktop\" & strFileNum & "AMSQUERY.in", "C:\Users\Bob\Documents\" & strFileNum & "AMSQUERY.in"

Next rCell

End Sub