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
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.
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).
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?
This link has partial code given by robert_schutt
https://www.experts-exchange.com/questions/27976953/Post-questions-from-excel-to-my-site.html