[RESOLVED] A Simple One

[paul999]

I'm not too good at this VBA stuff so I'd appreciate some 'gentle' help.

I have a database made up of .htm pages stored on my computer. They are a part of a complete database of information that enables me to edit them using html in the source code. At present the only way I can do this is to open the page in IE. right click and get up the source code and then enter my comments in html.

I would like to write something in VBA that enables me to open these 'web pages' in Notepad and then add some html. Then save and close it so that the next time I open it in IE the page has been changed as I need.

Can anyone tell me how to open the source code of a web page in Notepad from VBA?

Thanks in advance

-Paul

[RobDog888]

One way would be to shell out notepad with the html file as the parameter to open with.

Shell "C:\Windows\Notepad.exe C:\Test.html", vbNormalFocus

[paul999]

RobDog888 - Yes that worked perfectly, thank you. I know I said it was 'A simple One' but I didn't realise it would be that simple.

My next problem is to find a specific piece of html on the open .txt page, highlight it, paste some new html from the clipboard in its place and then save the .txt.

I've looked in Excel help for 'Find' but I keep getting directed to the If...then...Else help page when I request the FindNext info.

As for the rest of the stuff I need, I cannot find anything on them at all. Maybe I'm looking in the wrong place.

Any chance of helping me out please?

Many thanks

Regards

-Paul

[mikeyc1204]

Doing that with the open notepad window won't be the easiest task. It shouldn't be too tough with File IO though. Where do you plan on inputting the old and new HTML code? A userform? Is this running from MS Access? I can whip up a sample in a bit.

[RobDog888]

Yes, its easier to parse the htm file and replace the code you desire and save the file.

[mikeyc1204]

A little lenghty but easy to use.

Paste this in a module. Sample usage is in Sub Usage() at the bottom.

Sub ReplaceHTML(SourceFile As String, sText As String, rText As String)
    
    Dim TargetFile As String, tLine As String, tString As String
    Dim p As Integer, i As Long, F1 As Integer, F2 As Integer
    
    TargetFile = "tmpfile.tmp"
    If Dir(SourceFile) = "" Then Exit Sub
    If Dir(TargetFile) <> "" Then
        On Error Resume Next
        Kill TargetFile
        On Error GoTo 0
        If Dir(TargetFile) <> "" Then
            MsgBox TargetFile & " already open, close and delete / rename the file and try again.", vbCritical
            Exit Sub
        End If
    End If
    F1 = FreeFile
    Open SourceFile For Input As F1
    F2 = FreeFile
    Open TargetFile For Output As F2
    i = 1
    While Not EOF(F1)
        Line Input #F1, tLine
        If sText <> "" Then
            ReplaceTextInString tLine, sText, rText
        End If
        Print #F2, tLine
        i = i + 1
    Wend
    Close F1
    Close F2
    Kill SourceFile
    Name TargetFile As SourceFile
End Sub
 
Private Sub ReplaceTextInString(SourceString As String, SearchString As String, ReplaceString As String)
    Dim p As Integer, NewString As String
    Do
        p = InStr(p + 1, UCase(SourceString), UCase(SearchString))
        If p > 0 Then
            NewString = ""
            If p > 1 Then NewString = Mid(SourceString, 1, p - 1)
            NewString = NewString + ReplaceString
            NewString = NewString + Mid(SourceString, p + Len(SearchString), Len(SourceString))
            p = p + Len(ReplaceString) - 1
            SourceString = NewString
        End If
        If p >= Len(NewString) Then p = 0
    Loop Until p = 0
End Sub
 
Sub Usage()
    'This example will replace all instances of <h2> with <h1>
    ReplaceHTML "c:\tmp\test.html", "<h2>", "<h1>"
End Sub

[paul999]

mikeyc1204 - I'm amazed at the effort you have put in help me with this - thank you. However, the problem is, he code you have put up totally confuses me and I have no idea what to do with it or if I need to in put my own file names, and if so, where.

Any chance you can use a bit more of your time and give me some clues as to where to go now.

Many thanks

-Paul

[mikeyc1204]

This is the only part of the code you need to change. Tailor it to where your html documents are and what tags or info you want to change.

Sub Usage()
    'This will replace all instances of <h2> with <h1>
    ReplaceHTML "c:\Test.html", "<h2>", "<h1>"
End Sub

Just paste the rest of the code into any module. Once you paste that big block in, you can use the above line in any other procedure you already have set up.

If you want to make 3 changes, say change a copyright line, a bold tag to an italic tag, or a link location, you can just call the ReplaceHTML sub 3 times like so:

Sub Example()
    ReplaceHTML "c:\Test.html", "copyright 2005", "copyright 2006"
    ReplaceHTML "c:\Test.html", "b>", "i>"
    ReplaceHTML "c:\Test.html", "http://www.yahoo.com", "http://my.yahoo.com"
End Sub

[paul999]

Thanks for that mikeyc1204. Now I understand a bit more.

OK, the problem that I seem to be coming up against now is that when I ran the code in an Excel VBA module (I presume that is OK) it didn't seem to go anywhere. So I ran it in break mode with a break in the first Sub routine at the line that goes

TargetFile = "tmpfile.tmp"

This resulted in the me being able to see that the variable 'TargetFile' had talen on the value which was equal to the path for the .html file I was testing it on. However, the code ignored the value and when it hit

If Dir(SourceFile) = "" Then Exit Sub

we exited the sub!

I'd appreciate any ideas on this.

Regards

-Paul

[mikeyc1204]

When you test it, how are you starting the code?

The value of TargetFile should always be "tmpfile.tmp". If it is taking on the value of your html file, then something was edited incorrectly. The line should stay 'TargetFile = "tmpfile.tmp"'.

The line where you indicated processing ended,
If Dir(SourceFile) = "" Then Exit Sub
implies the the procedure can't find your HTML file.

If you called the procedure like so:
ReplaceHTML "c:\Test.html", "copyright 2005", "copyright 2006"
make sure that c:\Test.html is a valid name and location for your file. Was it misspelled?

One other thing it could be... are you trying to run ReplaceHTML directly from the code window? If so, the procedure will be called with empty strings for arguments. This will also cause the procedure to bail out at the 'If Dir(SourceFile) = "" Then Exit Sub' line. The procedure has to be called with the 3 string arguments for it to work.

With all the code pasted exactly as it is listed in my first post, try calling ReplaceHTML with the 3 args
ReplaceHTML "c:\whatever.html", "<H1>", "<H3>"
make sure the filename is spelled correctly and it should work.

If all else fails, attach the Excel sheet to your post, and I'll see what's going wrong.

Hopefully it'll work for you this time.

[paul999]

mikeyc1204 - Perfect, absolutely bloody perfect. It was the simplest of mistakes. The file is a .htm and I had it down a .html and once I corrected the file extension it worked like a charm - thank you.

Now all I have to do is to suss out how to pass a variable to a file name and I might be on the road to success.

If I've got a file name of

C:\Documents and Settings\Paul\Desktop\New Folder\1.htm

and I want the "1" to be a value that is passed from cell values on a worksheet, can you give me an idea of the format I would need to use.

Thanking you in advance again.

Regards

-Paul

[mikeyc1204]

If your 1 is in cell A1, you would can call ReplaceHTML in a few ways. Any of these will work:

Sub Example()
    dim htmlFile as string
    
    htmlFile = Range("A1").Value
    htmlFile = "C:\Documents and Settings\Paul\Desktop\New Folder\" & htmlFile & ".htm"
    ReplaceHTML htmlfile, "<tag1>", "<tag2>"
End Sub

Sub Example()
    dim htmlFile as string
    
    htmlFile = Cells(1, 1).Value
    htmlFile = "C:\Documents and Settings\Paul\Desktop\New Folder\" & htmlFile & ".htm"
    ReplaceHTML htmlfile, "<tag1>", "<tag2>"
End Sub

or cut the middle man..

Sub Example()
    ReplaceHTML "C:\Documents and Settings\Paul\Desktop\New Folder\" & Range("A1").value & ".htm", "<tag1>", "<tag2>"
End Sub

If you want to edit 100 html files, all listed in column A starting at Row 1, call like this

Sub Example()
    Dim i as integer
    Dim htmlFile as string
    
    For i = 1 to Range("A1").End(xlDown).Row
        htmlFile = Range("A" & i).Value
        htmlFile = "C:\Documents and Settings\Paul\Desktop\New Folder\" & htmlFile & ".htm"
        ReplaceHTML htmlfile, "<tag1>", "<tag2>"
    Next i
End Sub

[paul999]

Hi mikeyc1204

I've had a play with the above and it runs like clockwork and I think even I can put together something out of all you hard work.

I really cannot thank you enough for the help you have given and if you continue to give as much help to other newbies as yoo have to me, you will be an asset to this forum, I am sure.

My only complaint with the procedure is that the forum only allows me to add one message to your 'Reputation' whereas, if I have the opportunity, I'd have added a lot more - hence this post.

Thank you, once again, and I'll post again tomorrow once I have put this lot together and made it run.

Regards

-Paul

[mikeyc1204]

Glad to be of help sir

[paul999]

mikeyc1204 - Well its taken me a couple of days to get things working how I it to but I'm there now and last night I was able the update over 500 races in about 2 hours that would have taken me about 2 weeks full time before.

So, frrm that, I'm sure you will realise just how much of a help you have been to me. There is no way I could have written the code that you supplied. The reason I can say that with complete surety is that I still cannot work out how it works!!

Anyway, thanks again and the next time I get stuck I hope we run into each other.

Regards

-Paul