issue with replace function for a text file using VBA

[xerxes1]

I am trying to replace the the string "VALUE=" with "VALUE= (Excel cell data)" where it first appears after a certain position within a text file (this certain position being "SOURCE").

The issue I'm having is that when I specify the start position within the REPLACE function, the newly saved text file will have deleted all the text prior to start position. I would like to make the replacement and retain all the text preceding the start position.

Please see the code I have below (based on Excel VBA 2010):


Sub Main()
Dim mFleNm As String, Fle As Long, mInfo As String, mLongFle As Long
Dim mFileName As String
Dim row As Long
Dim start As Long
mFleNm = BrowseForFile("C:\Projects\Excel File")
Fle = FreeFile
mFileName = Right(mFleNm, Len(mFleNm) - InStrRev(mFleNm, "\"))
mFileName = Left(mFileName, Len(mFileName) - 4)
Open mFleNm For Input As #Fle
mLongFle = LOF(Fle)
mInfo = Input(mLongFle, #Fle)
Close (Fle)
For row = 7 To 10
start = InStr(mInfo, "SOURCE")
mInfo = Replace(mInfo, "VALUE=", "VALUE= " & ActiveSheet.Cells(row + 1, "C"), start, 1)
Fle = FreeFile
mFleNm = ActiveSheet.Cells(row + 1, "B") & ".txt"
Open mFleNm For Binary As #Fle
Put #Fle, , mInfo
Close #Fle
Next
End Sub

 

[CharlesChuckieCharles]

Replace(mInfo, "VALUE=", "VALUE= " & ActiveSheet.Cells(row + 1, "C"), start, 1)

start is also the name of the parameter ( see prompt text saying Start:=1)

try using MyStart as your variable name

MyStart = InStr(mInfo, "SOURCE")

Replace(mInfo, "VALUE=", "VALUE= " & ActiveSheet.Cells(row + 1, "C"), MyStart, 1)

 

[xerxes1]

Thanks for the prompt response.

I changed the variable name to MyStart throughout, but still the generated text files delete everything before "SOURCE".

 

[CharlesChuckieCharles]

This is from the bottom of the help page for Replace



Remarks
The return value of the Replace function is a string, with substitutions made, that begins at the position specified by start and and concludes at the end of the expression string. It is not a copy of the original string from start to finish.


So replace returns a string Starting at Start!!

Learn something New everyday