Replace text in file is not replacing string correctly

Question

With below code I'm trying to add a anchor tag/hyperlink to a number in html file. Though I can see correct values in Local Window, same are not updated in file. Don't know what's wrong.

Sub HyperlinkPRs() '''https://stackoverflow.com/questions/10434335/text-file-in-vba-open-find-replace-saveas-close-file

Dim rng As Range
Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As String
Dim var As String

lr = Worksheets("RawData").Cells(Rows.Count, 7).End(xlUp).Row
Set rng = Sheets("RawData").Range("G2:G" & lr)

' Edit as needed
sFileName = ThisWorkbook.Path & "\" & "data.html"

iFileNum = FreeFile
Open sFileName For Input As iFileNum

Do Until EOF(iFileNum)
    Line Input #iFileNum, sBuf
    sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum
   ''' Replace code
    For i = 1 To lr
        pr = Sheets("RawData").Range("G" & i).Value
        link = "<a href=""" & _
            "www.xyz.com/cgi-binr.pl?entry=" & _
            pr & _
            """>" & _
            pr & _
            "</a>" & "</td>"
        sTemp = Replace(sTemp, pr & "</td>", link)
    Next

iFileNum = FreeFile
Open sFileName For Output As iFileNum
Print #iFileNum, sTemp
Close iFileNum

End Sub

Values from local window (correct):

pr: 9525027
link = <a href="www.xyz.com/cgi-binr.pl?entry=9525027">9525027</a></td>

Replaced with:

 9525027<a href="www.xyz.com/cgi-binr.pl?entry="></a></td>

Input in text file:

  <td class=xl6516703 style='border-top:none;border-left:none'>&nbsp;</td>
  <td class=xl6516703 style='border-top:none;border-left:none'>&nbsp;</td>
  <td class=xl6516703 style='border-top:none;border-left:none'>&nbsp;</td>
  <td class=xl6516703 style='border-top:none;border-left:none'>&nbsp;</td>
  <td class=xl6516703 style='border-top:none;border-left:none'>14</td>
  <td class=xl6516703 style='border-top:none;border-left:none'>24</td>
  <td class=xl7616703 style='border-top:none;border-left:none'>9525027</td>

Output:

  <td class=xl6516703 style='border-top:none;border-left:none'>&nbsp;<a 
href="www.xyz.com_pr.pl?entry="></a></td>
  <td class=xl6516703 style='border-top:none;border-left:none'>&nbsp;<a href="www.xyz.com_pr.pl?entry="></a></td>
  <td class=xl6516703 style='border-top:none;border-left:none'>&nbsp;<a href="www.xyz.com_pr.pl?entry="></a></td>
  <td class=xl6516703 style='border-top:none;border-left:none'>&nbsp;<a href="www.xyz.com_pr.pl?entry="></a></td>
  <td class=xl6516703 style='border-top:none;border-left:none'>14<a href="www.xyz.com_pr.pl?entry="></a></td>
  <td class=xl6516703 style='border-top:none;border-left:none'>24<a href="www.xyz.com_pr.pl?entry="></a></td>
  <td class=xl7616703 style='border-top:none;border-left:none'>9525027<a href="www.xyz.com_pr.pl?entry="></a></td>

Your code works fine for me with no changes. The only thing I can think of is that you're not handling the case in which the `Replace` statement doesn't find the string `pr & ""` within the input. Alternatively (as a temporary debug check), output the results to a different file name so you can compare the original and updated results. — PeterT, Jul 16 '19 at 17:02
@PeterT, how can I handle it? Please find updated result. Also how to output results to a different file? — Experimenter, Jul 16 '19 at 17:58

score 1 · Accepted Answer · answered Jul 16 '19 at 18:42

Here is the (barely) modified routine you posted. Using your input data sample, I produced what I think is your desired output to a temporary file. Notice the check to determine if the old tag sub-string exists in the HTML. Also, my input data consisteed only of the three numeric tags. I did not modify the tags with  </td>.

Sub HyperlinkPRs()
    Dim rng As Range
    Dim sBuf As String
    Dim sTemp As String
    Dim iFileNum As Integer
    Dim sFileName As String
    Dim var As String

    lr = Worksheets("RawData").Cells(Rows.Count, 7).End(xlUp).Row
    Set rng = Sheets("RawData").Range("G2:G" & lr)

    ' Edit as needed
    'sFileName = ThisWorkbook.Path & "\" & "data.html"
    sFileName = "C:\Temp\test1.html"

    iFileNum = FreeFile
    Open sFileName For Input As iFileNum

    Do Until EOF(iFileNum)
        Line Input #iFileNum, sBuf
        sTemp = sTemp & sBuf & vbCrLf
    Loop
    Close iFileNum

    Dim oldTag As String
    Dim newLink As String
    For i = 1 To lr
        pr = Sheets("RawData").Range("G" & i).Value
        oldTag = pr & "</td>"
        If InStr(1, sTemp, oldTag) > 0 Then
            newLink = "<a href=""" & _
                      "www.xyz.com/cgi-binr.pl?entry=" & _
                      pr & _
                      """>" & _
                      pr & _
                      "</a>" & "</td>"
            sTemp = Replace(sTemp, oldTag, newLink)
            Debug.Print "replaced " & oldTag
        End If
    Next

    sFileName = "C:\Temp\test1out.html"   'temp file for debug
    iFileNum = FreeFile
    Open sFileName For Output As iFileNum
    Print #iFileNum, sTemp
    Close iFileNum
End Sub

Input data (C:\Temp\test1.html):

<td class=xl6516703 style='border-top:none;border-left:none'>&nbsp;</td>
  <td class=xl6516703 style='border-top:none;border-left:none'>&nbsp;</td>
  <td class=xl6516703 style='border-top:none;border-left:none'>&nbsp;</td>
  <td class=xl6516703 style='border-top:none;border-left:none'>&nbsp;</td>
  <td class=xl6516703 style='border-top:none;border-left:none'>14</td>
  <td class=xl6516703 style='border-top:none;border-left:none'>24</td>
  <td class=xl7616703 style='border-top:none;border-left:none'>9525027</td>

Output data (C:\Temp\test1out.html) produced by code:

<td class=xl6516703 style='border-top:none;border-left:none'>&nbsp;</td>
  <td class=xl6516703 style='border-top:none;border-left:none'>&nbsp;</td>
  <td class=xl6516703 style='border-top:none;border-left:none'>&nbsp;</td>
  <td class=xl6516703 style='border-top:none;border-left:none'>&nbsp;</td>
  <td class=xl6516703 style='border-top:none;border-left:none'><a href="www.xyz.com/cgi-binr.pl?entry=14">14</a></td>
  <td class=xl6516703 style='border-top:none;border-left:none'><a href="www.xyz.com/cgi-binr.pl?entry=24">24</a></td>
  <td class=xl7616703 style='border-top:none;border-left:none'><a href="www.xyz.com/cgi-binr.pl?entry=9525027">9525027</a></td>

Replace text in file is not replacing string correctly

1 Answers1