Find and replace words in a website

bsharath

Author

Commented: 2012-12-27

Attached screenshot on how the admin screen edit will be placed

This link has partial code given by robert_schutt
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Q_27976953.html
Edit.png

Robert SchuttSoftware Engineer

Trusted Expert

Commented: 2012-12-27

Well, I thought I'd give it a shot after all. Having some problems: it works when I step through the code but when I just run it, the switch to the plain body text is not made and sometimes it just hangs (but the Silent setting seems to have solved that).

Possibly the reason could be some javascript errors that flash past in the status bar during the different clicks and submits. Not sure what's happening but it could be interfering with the javascript/DOM access. It's getting late here so I will paste my code so far, it's using the same references as before.

Option Explicit

Const C_URL = "http://www.**************.com"
Const C_URL1 = C_URL & "/user"

Const C_USER = "EE-Test"
Const C_PASS = "EE-Test"

Dim objIE As InternetExplorer

Sub DoProcess()
    Set objIE = New InternetExplorer
    objIE.Visible = True
    objIE.Silent = True ' try to solve a problem with a hanging IE in state READYSTATE_INTERACTIVE (alert?)

    Dim h As HTMLDocument
    Dim f As HTMLFormElement
    Dim fe As HTMLFormElement
    Dim fsb As HTMLInputButtonElement

    ' login
    objIE.navigate C_URL1
    DoWait
    Set h = objIE.document
    Set f = h.forms("user-login")
    If Not f Is Nothing Then
        For Each fe In f.elements
            If fe.Name = "name" Then fe.innerText = C_USER
            If fe.Name = "pass" Then fe.innerText = C_PASS
        Next
        f.submit
        DoWait
        Set f = Nothing
    End If
    Set h = Nothing

    Dim rw As Integer ' row
    rw = 2
    ' now edit questions
    While ActiveSheet.Cells(rw, 1) <> ""
        Debug.Print "row: " & rw
        objIE.navigate ActiveSheet.Cells(rw, 1)
        DoWait
        Set h = objIE.document
        Dim objA_Edit As HTMLAnchorElement
        For Each objA_Edit In h.getElementsByTagName("A")
            If LCase(Trim(objA_Edit.innerText)) = "edit" Then
                objA_Edit.Click
                Exit For
            End If
        Next
        DoWait
        Set h = objIE.document
        h.getElementById("switch_edit-comment").Click
        DoWait
        Set f = h.forms("comment-form")
        Set fsb = Nothing
        For Each fe In f.elements
            Dim feName As String, feID As String
            feName = ""
            feID = ""
            On Error Resume Next
            feName = fe.Name
            feID = fe.ID
            If Err.Number <> 0 Then Err.Clear
            On Error GoTo 0
            Debug.Print "*Name:" & feName & "*ID:" & feID & "*"
            If feName = "comment" Then
                Dim strComment
                strComment = Replace(fe.innerText, ActiveSheet.Cells(rw, 2), ActiveSheet.Cells(rw, 3), , , vbTextCompare)
                While ActiveSheet.Cells(rw + 1, 1) = ActiveSheet.Cells(rw, 1) ' same url, proceed with next replace
                    rw = rw + 1
                    strComment = Replace(strComment, ActiveSheet.Cells(rw, 2), ActiveSheet.Cells(rw, 3), , , vbTextCompare)
                Wend
                fe.innerText = strComment
            End If
            If feID = "edit-submit" Then Set fsb = fe ' Save button
        Next
        If fsb Is Nothing Then
            Debug.Print "* form submit"
            f.submit
        Else
            Debug.Print "* Save click"
            fsb.Click
        End If
        DoWait
        Set fsb = Nothing
        Set f = Nothing
        Set h = Nothing
        rw = rw + 1
    Wend
    ' done
    objIE.Quit
    Set objIE = Nothing
End Sub

Sub DoWait()
    While objIE.Busy Or objIE.readyState <> READYSTATE_COMPLETE
        DoEvents
    Wend
End Sub

Open in new window

bsharath

Author

Commented: 2012-12-27

Thanks Robert for the effort

2 references were added Excel VBA project: "Microsoft HTML Object Library" and "Microsoft Internet Controls".

On an IE * and IE 9 system login and then it goes to the page and then it goes to this page

http://www.******.com/admin/build/views/edit/Dropdown?destination=node%2F11640#views-tab-block_1

Not sure why it reaches into this

bsharath

Author

Commented: 2012-12-27

It does work actually i tried from a superuser account which has more powers on the site and Edit is available in the side panels as well and guess there was something going wrong with it.

Now i tried with a normal admin and it works.

Few things i noticed

1. If i find "5.Those" and want to replace with "5. Those" a space only it does not work

2. Can i have a success and failure mentioned in excel?

3. One question can have 1 or more answers and it goes to the edit mode for the first one only and if the find and replace is on the 2nd one then it does nothing

4. Can't we find before we enter the edit more? If not then we need to get into every comment in the page and find and replace.

5. I had the old comment "group>click start>control panel>network connection" and new as
"group > Click Start > Control Panel > Network Connection"

The script went to edit mode but did not change

6. One comment can have one selling misspelled 2 times as well so we need to check the connect for next if available.

Robert SchuttSoftware Engineer

Trusted Expert

Commented: 2012-12-28

1. if you could put a breakpoint on the first time strComment is set and step through, you can see with a watch if the right variables are being replaced.

2. I guess, in combination with the next points you should define what constitutes a failure

3. this is what I asked for specifically in the previous question and you posted a screenshot of 1 edit button... But it should possible to go back to the question page and hit the next Edit button until all are done although risky if there are 2 edit buttons for 1 comment, but they should point to the same link so can be skipped the second time. So just to be sure: you want all the replaces done in all the comments?

4. I'll have another look at the source, it should be clear what the content of each comment is and determine which ones need to be changed.

5. same as 1, might have something to do with html encoding?

6. that should already be ok, Replace() does all occurrences by default (I thought, but I'll check)...

bsharath

Author

Commented: 2012-12-28

ok thanks

Robert SchuttSoftware Engineer

Trusted Expert

Commented: 2012-12-29

Hi, I was expecting some answers from you as well but in the mean time I investigated the source of the page as I could see it and made a new version:

Option Explicit

Const C_URL = "http://www.**************.com"
Const C_URL1 = C_URL & "/user"

Const C_USER = "EE-Test"
Const C_PASS = "EE-Test"

Dim objIE As InternetExplorer

Sub DoProcess()
    Set objIE = New InternetExplorer
    objIE.Visible = True
    objIE.Silent = True ' try to solve a problem with a hanging IE in state READYSTATE_INTERACTIVE (alert?)

    Dim h As HTMLDocument
    Dim f As HTMLFormElement
    Dim fe As HTMLFormElement
    Dim fsb As HTMLInputButtonElement

    ' login
    objIE.navigate C_URL1
    DoWait
    Set h = objIE.document
    Set f = h.forms("user-login")
    If Not f Is Nothing Then
        For Each fe In f.elements
            If fe.Name = "name" Then fe.innerText = C_USER
            If fe.Name = "pass" Then fe.innerText = C_PASS
        Next
        f.submit
        DoWait
        Set f = Nothing
    End If
    Set h = Nothing

    Dim rw As Integer ' row
    rw = 2
    ' now edit questions
    While ActiveSheet.Cells(rw, 1) <> ""
        Debug.Print "row: " & rw & " - url: " & ActiveSheet.Cells(rw, 1)
        objIE.navigate ActiveSheet.Cells(rw, 1)
        DoWait
        Set h = objIE.document
        Dim strCommentId As String
        strCommentId = ""
        Dim dv As HTMLDivElement
        For Each dv In h.getElementsByTagName("DIV")
            If dv.className = "submitted" Then
                strCommentId = Trim(Replace(Mid(dv.innerText, InStr(dv.innerText, "Comment Id: ") + 12), vbCrLf, ""))
                Debug.Print "Found comment id: " & strCommentId
                Dim dv2 As HTMLDivElement, blnTextFound As Boolean
                blnTextFound = False
                For Each dv2 In dv.parentElement.parentElement.getElementsByTagName("DIV")
                    If InStr(dv2.className, "content") > 0 Then
                        blnTextFound = InStr(1, dv2.innerText, ActiveSheet.Cells(rw, 2), vbTextCompare) > 0
                        Exit For
                    End If
                Next
                If blnTextFound Then
                    Dim objA_Edit As HTMLAnchorElement
                    Dim blnEditLinkFound As Boolean
                    blnEditLinkFound = False
                    For Each objA_Edit In dv.parentElement.parentElement.getElementsByTagName("A")
                        If LCase(Trim(objA_Edit.innerText)) = "edit" Then
                            blnEditLinkFound = True
                            objA_Edit.Click
                            Exit For
                        End If
                    Next
                    If blnEditLinkFound Then
                        DoWait
                        Set h = objIE.document
                        h.getElementById("switch_edit-comment").Click
                        DoWait
                        Set f = h.forms("comment-form")
                        Set fsb = Nothing
                        For Each fe In f.elements
                            Dim feName As String, feID As String
                            feName = ""
                            feID = ""
                            On Error Resume Next
                            feName = fe.Name
                            feID = fe.ID
                            If Err.Number <> 0 Then Err.Clear
                            On Error GoTo 0
                            Debug.Print "*Name:" & feName & "*ID:" & feID & "*"
                            If feName = "comment" Then
                                Dim strComment
                                strComment = Replace(fe.innerText, ActiveSheet.Cells(rw, 2), ActiveSheet.Cells(rw, 3), , , vbTextCompare)
                                'While ActiveSheet.Cells(rw + 1, 1) = ActiveSheet.Cells(rw, 1) ' same url, proceed with next replace
                                '    rw = rw + 1
                                '    strComment = Replace(strComment, ActiveSheet.Cells(rw, 2), ActiveSheet.Cells(rw, 3), , , vbTextCompare)
                                'Wend
                                fe.innerText = strComment
                            End If
                            If feID = "edit-submit" Then Set fsb = fe ' Save button
                        Next
                        If fsb Is Nothing Then
                            Debug.Print "* form submit"
                            f.submit
                        Else
                            Debug.Print "* Save click"
                            fsb.Click
                        End If
                        DoWait
                    Else
                        Debug.Print "edit link not found"
                    End If
                    Set fsb = Nothing
                    Set f = Nothing
                Else
                    Debug.Print "text '" & ActiveSheet.Cells(rw, 2) & "' not found in comment " & strCommentId
                End If
            End If
        Next
        Set h = Nothing
        rw = rw + 1
    Wend
    ' done
    objIE.Quit
    Set objIE = Nothing
End Sub

Sub DoWait()
    While objIE.Busy Or objIE.readyState <> READYSTATE_COMPLETE
        DoEvents
    Wend
End Sub

Open in new window

I also checked point 6 and it works ok, all instances are replaced.

bsharath

Author

Commented: 2012-12-30

Sorry i did not realize i had to answer few points

I checked now the script is too slow not sure its my system issue as i removed Ie9 and reverted to Ie8

While objIE.Busy Or objIE.readyState <> READYSTATE_COMPLETE

Automation error system call failed

Robert SchuttSoftware Engineer

Trusted Expert

Commented: 2012-12-30

Sorry i did not realize i had to answer few points

Ok please re-read that post (with the 6 points answering your 6 points) when we get past the other issues here.

removed Ie9 and reverted to Ie8

Wow, that's drastic. Temporarily setting IE9 to simulate IE8 when needed wasn't working right after all then?

Automation error system call failed

Ok, being slow and giving errors are different things. You had the slowness on another question as well. It may still be that there is a problem with the site. It may also be that your browser settings are different (for example show javascript errors), but I was hoping that would be solved by the Silent setting in the code on line 14 above (maybe I understood it wrong but without that when a popup occurs the objIE.readyState = READYSTATE_INTERACTIVE so the loop never ends).

So the problem at the moment is I can't test this because it's working fine for me, either because you're feeding it different data or because our systems are too different (version of OS, office, IE etc).

bsharath

Author

Commented: 2012-12-30

Is their any way to make the code do this on firefox or Chrome?

Robert SchuttSoftware Engineer

Trusted Expert

Commented: 2012-12-30

Never done that, if they have a COM interface it should be possible. Does this mean you don't want to get into this with IE further and are not gonna address the issues I raised?

bsharath

Author

Commented: 2012-12-30

I am fine with IE just that at my end it did not work was thinking of a work around
The site has caching issues which the developer is fixing

Will wait for few days for that to be fixed and check if its fine
Thank you

Robert SchuttSoftware Engineer

Trusted Expert

Commented: 2013-01-08

Any luck?

bsharath

Author

Commented: 2013-01-09

Thanks for getting back
Site after a server migration is still lagging with performance
When i check now before we login the script moves to the page and breaks

Can we check if already logged in then do not login again?

if you can make this change so i can test

Robert SchuttSoftware Engineer

Trusted Expert

Commented: 2013-01-09

That is already part of the code I posted before. If you put a breakpoint on line 26, f should be equal to Nothing and that means the form is not on the page, and the reason for that should be that the previous session is still valid. If you get different results, it would help if you can debug-step through the code and use 'Add Watch' to provide some more information on what is going wrong. Like I said before (I think, might have been a different question), the fact that you are using a different user means I can't test it the way you see the page.

bsharath

Author

Commented: 2013-01-09

New progress

1. if the user is not logged in first time i click manually login it gets in and does the first row changes successfully

2. The 2nd change it gives permission denied error and this line is selected

If dv.className = "submitted" Then

Login is not an issue for now as if already logged in its fine

2nd replacement gives and issue

bsharath

Author

Commented: 2013-01-09

I tried with 2 different URl's and the 2nd one fails with same error and first one changes the spelling successfully

bsharath

Author

Commented: 2013-01-09

With just 1 URl works like a charm
if their are 2 then i get the issue

Robert SchuttSoftware Engineer

Trusted Expert

Commented: 2013-01-09

I found a logic flaw: after editting, the html document reference that is used to loop through the div elements is no longer valid because it navigates to a different page of course. I will try to make an array with url's that have to be checked before leaving the overview page. I can now test better because there are 2 comments on the original test page...

bsharath

Author

Commented: 2013-01-09

Thanks
If needed you can create more comments
If possible a success and failure report

Robert SchuttSoftware Engineer

Trusted Expert

Commented: 2013-01-09

success and failure report

You mean something like: "replaced in 1 comment, not present in 1 comment, no edit link in 2 comments". Something like that could be added to column C.

For now, here is a new version for you to try:

Option Explicit

Const C_URL = "http://www.**************.com"
Const C_URL1 = C_URL & "/user"

Const C_USER = "EE-Test"
Const C_PASS = "EE-test" ' changed...!

Dim objIE As InternetExplorer

Sub DoProcess()
    Set objIE = New InternetExplorer
    objIE.Visible = True
    objIE.Silent = True ' try to solve a problem with a hanging IE in state READYSTATE_INTERACTIVE (alert?)

    Dim h As HTMLDocument
    Dim f As HTMLFormElement
    Dim fe As HTMLFormElement
    Dim fsb As HTMLInputButtonElement
    Dim arrEditPages() As String
    Dim intMaxForArrayEditPages As Integer

    ' login
    objIE.navigate C_URL1
    DoWait
    Set h = objIE.document
    Set f = h.forms("user-login")
    If Not f Is Nothing Then
        For Each fe In f.elements
            If fe.Name = "name" Then fe.innerText = C_USER
            If fe.Name = "pass" Then fe.innerText = C_PASS
        Next
        f.submit
        DoWait
        Set f = Nothing
    End If
    Set h = Nothing

    Dim rw As Integer ' row
    rw = 2
    ' now edit questions
    While ActiveSheet.Cells(rw, 1) <> ""
        Debug.Print "row: " & rw & " - url: " & ActiveSheet.Cells(rw, 1)
        objIE.navigate ActiveSheet.Cells(rw, 1)
        DoWait
        Set h = objIE.document
        Dim strCommentId As String
        strCommentId = ""
        intMaxForArrayEditPages = -1 ' start new array of comment pages to be edited for this url
        Dim dv As HTMLDivElement
        For Each dv In h.getElementsByTagName("DIV")
            'Debug.Print dv.className
            If dv.className = "submitted" Then
                strCommentId = Trim(Replace(Mid(dv.innerText, InStr(dv.innerText, "Comment Id: ") + 12), vbCrLf, ""))
                Debug.Print "Found comment id: " & strCommentId
                Dim dv2 As HTMLDivElement, blnTextFound As Boolean
                blnTextFound = False
                For Each dv2 In dv.parentElement.parentElement.getElementsByTagName("DIV")
                    If InStr(dv2.className, "content") > 0 Then
                        blnTextFound = InStr(1, dv2.innerText, ActiveSheet.Cells(rw, 2), vbTextCompare) > 0
                        Exit For
                    End If
                Next
                If blnTextFound Then
                    Dim objA_Edit As HTMLAnchorElement
                    Dim blnEditLinkFound As Boolean
                    blnEditLinkFound = False
                    For Each objA_Edit In dv.parentElement.parentElement.getElementsByTagName("A")
                        If LCase(Trim(objA_Edit.innerText)) = "edit" Then
                            blnEditLinkFound = True
                            'objA_Edit.Click
                            intMaxForArrayEditPages = intMaxForArrayEditPages + 1
                            ReDim Preserve arrEditPages(intMaxForArrayEditPages)
                            arrEditPages(intMaxForArrayEditPages) = objA_Edit.href
                            Exit For
                        End If
                    Next
                    If Not blnEditLinkFound Then ' just FYI
                        Debug.Print "edit link not found"
                    End If
                Else
                    Debug.Print "text '" & ActiveSheet.Cells(rw, 2) & "' not found in comment " & strCommentId
                End If
            End If
        Next
        Dim intCounter
        For intCounter = 0 To intMaxForArrayEditPages
            objIE.navigate arrEditPages(intCounter)
            DoWait
            Set h = objIE.document
            h.getElementById("switch_edit-comment").Click
            DoWait
            Set f = h.forms("comment-form")
            Set fsb = Nothing
            For Each fe In f.elements
                Dim feName As String, feID As String
                feName = ""
                feID = ""
                On Error Resume Next
                feName = fe.Name
                feID = fe.ID
                If Err.Number <> 0 Then Err.Clear
                On Error GoTo 0
                Debug.Print "*Name:" & feName & "*ID:" & feID & "*"
                If feName = "comment" Then
                    Dim strComment
                    strComment = Replace(fe.innerText, ActiveSheet.Cells(rw, 2), ActiveSheet.Cells(rw, 3), , , vbTextCompare)
                    'While ActiveSheet.Cells(rw + 1, 1) = ActiveSheet.Cells(rw, 1) ' same url, proceed with next replace
                    '    rw = rw + 1
                    '    strComment = Replace(strComment, ActiveSheet.Cells(rw, 2), ActiveSheet.Cells(rw, 3), , , vbTextCompare)
                    'Wend
                    fe.innerText = strComment
                End If
                If feID = "edit-submit" Then Set fsb = fe ' Save button
            Next
            If fsb Is Nothing Then
                Debug.Print "* form submit"
                f.submit
            Else
                Debug.Print "* Save click"
                fsb.Click
            End If
            DoWait
        Next
        Set fsb = Nothing
        Set f = Nothing
        Set h = Nothing
        rw = rw + 1
    Wend
    ' done
    objIE.Quit
    Set objIE = Nothing
End Sub

Sub DoWait()
    While objIE.Busy Or objIE.readyState <> READYSTATE_COMPLETE
        DoEvents
    Wend
End Sub

Open in new window

bsharath

Author

Commented: 2013-01-09

Perfect

Yes in Column "D" if i can get success if replaced and failure if not
Failure can be few issues page not found or words not found if we can distinguish it

If their are 2 spelling mistakes with a comment it works but say in the first comment i have one change and 2nd comment another change then the 2nd one fails

when i run the macro 2nd time it does the changes

Once this is sorted i will post a new question thats related with a few more changed like if the spelling issue is in a question

Robert SchuttSoftware Engineer

Trusted Expert

Commented: 2013-01-09

Strange, I thought I tested both situations. Could you post an example of a few lines of data to reproduce that (under the EE-test account) please? I used the original test data and some added that seemed logical (like "gennus" -> "genius" on the last comment).

By the way, did you notice the password change? Could this have something to do with the problems Brian had on another question with logging in, or was that something different altogether?

bsharath

Author

Commented: 2013-01-09

The other post is a different issue

yes the password was changed and is set right in the code

I check now from my system 2 and has the same issue

Attached file i am using
Sample-file--1-.xls

Robert SchuttSoftware Engineer

Trusted Expert

Commented: 2013-01-10

I just tested it from your file (with the line "fe.innerText = strComment" commented out to preserve the testing situation) and the debug window of the VBA editor shows:

row: 2 - url: http://tinyurl.com/d8a6ywt
Found comment id: 108510
text 'tanks' not found in comment 108510
Found comment id: 117802
Found comment id: 119321
text 'tanks' not found in comment 119321
*Name:comment*ID:edit-comment*
*Name:preview*ID:edit-preview*
*Name:form_build_id*ID:form-de0481b204e6c90205d4372ed172e11d*
*Name:form_token*ID:edit-form-token*
*Name:form_id*ID:edit-comment-form*
*Name:*ID:*
*Name:files[upload]*ID:edit-upload*
*Name:attach*ID:edit-attach*
*Name:op*ID:edit-submit*
* Save click
row: 3 - url: http://tinyurl.com/d8a6ywt
Found comment id: 108510
text 'Slution' not found in comment 108510
Found comment id: 117802
text 'Slution' not found in comment 117802
Found comment id: 119321
*Name:comment*ID:edit-comment*
*Name:preview*ID:edit-preview*
*Name:form_build_id*ID:form-b06b3410a2114968d6031ffd4ae220c6*
*Name:form_token*ID:edit-form-token*
*Name:form_id*ID:edit-comment-form*
*Name:*ID:*
*Name:files[upload]*ID:edit-upload*
*Name:attach*ID:edit-attach*
*Name:op*ID:edit-submit*
* Save click

Open in new window

So this works fine for me. By "the same issue" I assume you still mean the "Permission denied"? If so, that can only happen I think when the connection between the variables and the browser window is disrupted. So before I had made an error in the logic that made that happen but with the current code I feel it's only possible if you interact with the browser window yourself like refreshing the page or clicking a link. Could you test it with "objIE.Visible = False" instead of True?

bsharath

Author

Commented: 2013-01-10

1. Can you give me a log way as you showed above so we know where its failing

2. I tried silent as well. One is successful and others fail and its so different time to time

After 3 page checks it gives a permission error and the logged in user is logged off

Sometimes in the first stage it does not login

3. Can you change the script not to process the login method. So the first time i login and then the script take over?

Once this is done we elimenate the login issue as we login manually and with help of log we can see where its failing?

Robert SchuttSoftware Engineer

Trusted Expert

Commented: 2013-01-10

1. It's already in the existing code (Debug.Print ...), you can find that output in the Immediate window (press Control + G in the VBA editor to show it)

2. doesn't make sense but maybe we'll understand after you get to the logging

3. just comment out (or delete) lines 24 thru 37, but you will have to put a breakpoint after the next .Navigate call for example on line 46 (Set h = objIE.document) and then log in yourself if the page asks for it. For me it doesn't anymore (assuming the site uses a cookie?)

bsharath

Author

Commented: 2013-01-13

Thanks a lot
THis code worked as in my attachment on my Windows XP and IE8

So i am happy its working
thanks for the help

Find and replace words in a website

Premium Content

Premium Content

Explore our Suite of Services