bsharath
asked on
Find and replace words in a website
Find and replace words in a website
I have an excel as attached and need help with an excel macro or a vbs that can open each URl and find and replace the words
More details in excel file
Sample-file.xls
I have an excel as attached and need help with an excel macro or a vbs that can open each URl and find and replace the words
More details in excel file
Sample-file.xls
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.
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
ASKER
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
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
ASKER
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.
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.
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)...
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)...
ASKER
ok thanks
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
I also checked point 6 and it works ok, all instances are replaced.
ASKER
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
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
Sorry i did not realize i had to answer few pointsOk 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 Ie8Wow, that's drastic. Temporarily setting IE9 to simulate IE8 when needed wasn't working right after all then?
Automation error system call failedOk, 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).
ASKER
Is their any way to make the code do this on firefox or Chrome?
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?
ASKER
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
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
Any luck?
ASKER
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
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
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.
ASKER
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
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
ASKER
I tried with 2 different URl's and the 2nd one fails with same error and first one changes the spelling successfully
ASKER
With just 1 URl works like a charm
if their are 2 then i get the issue
if their are 2 then i get the issue
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...
ASKER
Thanks
If needed you can create more comments
If possible a success and failure report
If needed you can create more comments
If possible a success and failure report
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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?
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?
ASKER
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
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
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
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?
ASKER
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?
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?
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?)
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?)
ASKER
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
THis code worked as in my attachment on my Windows XP and IE8
So i am happy its working
thanks for the help
ASKER
This link has partial code given by robert_schutt
https://www.experts-exchange.com/questions/27976953/Post-questions-from-excel-to-my-site.html
Edit.png