Macro for replacing text in a htm document, the text to change is different every time

[Durtug]

Hi,
I am new to using macros, and have searched the net through google, and this forum without result
I have this problem, and no idea how to make the needed macro.

Im my excel worksheet (excel 2010) I have one cell that changes every day (number)
I want this number to open my htm document and replace the same number in a string in the htm and save/close this.

An example:
My htm document is located at C:/ and named XX.htm
The number I want from excel is in cell A1 in sheet1, and the worksheet is located in D:/ named yy.xlsx

And the text(number) I want to replace is in the following string in the htm document, in this string it is 72, next day it can be 30:
src="Bilder/72.png"

Does anyone of you great people have a solution for this problem?
Or is it not doable?

Kind regards.

 

[Rick Rothstein]

This macro should do what you want...

Sub ChangeSourceBilderPNG()
  Dim X As Long, FileNum As Long, TotalFile As String, Src() As String
  FileNum = FreeFile
  Open "c:\xx.htm" For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  Src = Split(TotalFile, "src=""Bilder/", , vbTextCompare)
  For X = 1 To UBound(Src)
    Src(X) = Sheets("Sheet1").Range("A1").Value & Mid(Src(X), InStr(Src(X), "."))
  Next
  FileNum = FreeFile
  Open "c:\xx.htm" For Output As #FileNum
    Print #FileNum, Join(Src, "src=""Bilder/")
  Close #FileNum
End Sub

Note: The code uses the path and filename you posted (see red text), but they look "simplified", so change them as needed.

 

[Durtug]

This macro should do what you want...

Sub ChangeSourceBilderPNG()
  Dim X As Long, FileNum As Long, TotalFile As String, Src() As String
  FileNum = FreeFile
  Open "c:\xx.htm" For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  Src = Split(TotalFile, "src=""Bilder/", , vbTextCompare)
  For X = 1 To UBound(Src)
    Src(X) = Sheets("Sheet1").Range("A1").Value & Mid(Src(X), InStr(Src(X), "."))
  Next
  FileNum = FreeFile
  Open "c:\xx.htm" For Output As #FileNum
    Print #FileNum, Join(Src, "src=""Bilder/")
  Close #FileNum
End Sub

Note: The code uses the path and filename you posted (see red text), but they look "simplified", so change them as needed.

Thanks for a fast reply.
Unfortunately it does not work.

If I keep the htm file in the same folder as the excel file, is it enough to do it like this:
<code>
Open "xx.htm" For Binary As #FileNum
</code>

I suspect that the problems is in the Src, so I add all the text in my htm and hope you may see what I have to do.

Yesterday I just added the short text src="Bilder/72.png" from the middle of this document.

Now my macro looks like this:
<code>
Sub ChangeSourceBilderPNG()
Dim X As Long, FileNum As Long, TotalFile As String, Src() As String
FileNum = FreeFile
Open "sg.htm" For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Src = Split(TotalFile, "<a href="http://somedomain.net/sites/Rapporter/Shared%20Documents/test.pdf" target="blank"><img src="Bilder/72.png" border="0"></a><br><br><font style="font-family:Verdana" color="#3c3c3c" size="1,8"> Servicegrad er fra 00-21 dagen igår.<br>Med unntak av Lørdager</font>
", , vbTextCompare)
For X = 1 To UBound(Src)
Src(X) = Sheets("RapportPM").Range("N27").Value & Mid(Src(X), InStr(Src(X), "."))
Next
FileNum = FreeFile
Open "sg.htm" For Output As #FileNum
Print #FileNum, Join(Src, "<a href="http://somedomain.net/sites/Rapporter/Shared Documents/test.pdf" target="blank"><img src="Bilder/72.png" border="0"></a><br><br><font style="font-family:Verdana" color="#3c3c3c" size="1,8"> Servicegrad er fra 00-21 dagen igår.<br>Med unntak av Lørdager</font>
")
Close #FileNum
End Sub

</code>
Because I get a error message if I us the whole text from htm:
The yellow is the place it marks when the errror pops up.

 

[Rick Rothstein]

The only thing you were supposed to change was the text I had highlighted in red, nothing else... you changed the text I set up as the delimiter for the Split function. Try using the code exactly as I posted it except change the red text to the path and filename for your htm file. Note, though, that this code will physically change your original file, so you might want to make a copy of it before running the macro

 

[Durtug]

Thanks again.
That was the thing I tried first, but it didn't work either.
Now I did it again, but just changed the highlighted text.
But now I get this errormessage in Visual Basic:
Run-time errror '75':
Path/file access errror

Does this have something to do with the space in Shared documents?
The path is on our sharpoint area, and not in a regular folder.
My macro looks like this now:
<code>
Sub ChangeSourceBilderPNG()
Dim X As Long, FileNum As Long, TotalFile As String, Src() As String
FileNum = FreeFile
Open "http://team.erf01.net/sites/3870/Shared Documents/Rapporter/Dagsrapport/Rapporter/sg.htm" For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Src = Split(TotalFile, "src=""Bilder/", , vbTextCompare)
For X = 1 To UBound(Src)
Src(X) = Sheets("Sheet1").Range("A1").Value & Mid(Src(X), InStr(Src(X), "."))
Next
FileNum = FreeFile
Open "http://team.erf01.net/sites/3870/Shared Documents/Rapporter/Dagsrapport/Rapporter/sg.htm" For Output As #FileNum
Print #FileNum, Join(Src, "src=""Bilder/")
Close #FileNum
End Sub
</code>

 

[Rick Rothstein]

Thanks again.
That was the thing I tried first, but it didn't work either.
Now I did it again, but just changed the highlighted text.
But now I get this errormessage in Visual Basic:
Run-time errror '75':
Path/file access errror

Does this have something to do with the space in Shared documents?
The path is on our sharpoint area, and not in a regular folder.
My macro looks like this now:
<code>
Sub ChangeSourceBilderPNG()
Dim X As Long, FileNum As Long, TotalFile As String, Src() As String
FileNum = FreeFile
Open "http://team.erf01.net/sites/3870/Shared Documents/Rapporter/Dagsrapport/Rapporter/sg.htm" For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
Src = Split(TotalFile, "src=""Bilder/", , vbTextCompare)
For X = 1 To UBound(Src)
Src(X) = Sheets("Sheet1").Range("A1").Value & Mid(Src(X), InStr(Src(X), "."))
Next
FileNum = FreeFile
Open "http://team.erf01.net/sites/3870/Shared Documents/Rapporter/Dagsrapport/Rapporter/sg.htm" For Output As #FileNum
Print #FileNum, Join(Src, "src=""Bilder/")
Close #FileNum
End Sub
</code>

I do not have access to a network (been retired for some 12 years now), so I have no way to test it, but my guess is that the Open statement does not know how to deal with "http://" part. Can you copy the file to a local drive, run my code against it and then copy it back to your server? That should work since it worked for me from my local drive when I tested the code during development of it.

 

[Durtug]

I will try that in about one hour.

 

[Durtug]

I have now tried it, and it worked like a charm
Thanks for the great help.

Do you have any suggestion on the http: problem?

 

[Durtug]

I solved the problem with just remove http: and change all regular slashes with backslashesh (since it is a network).
I am truly greatful for all your fast and great help with my problem :D
Thank you very much, once again.