Unable to reset HTML document after navigating to page in VBA

Question

I'm using VBA to navigate IE and am having trouble resetting the document object so that I can navigate through multiple pages. I used the answer to this question to help create the original script, but am running in to a very similar problem that poster ran in to. Unfortunately, the answer listed does not help or guide me to a solution.

I'm using the following code to pull the page and navigate.

Sub UpdatesalesNotes()

Dim ie As Object, ieDoc As HTMLDocument

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = True

strHTML = ActiveSheet.Range("A31").Value

ie.navigate strHTML

'wait for browser
While ie.ReadyState <> READYSTATE_COMPLETE
    DoEvents
Wend


Set ieDoc = ie.Document

Call ClickButton(ieDoc, "New Service Note")

'wait for browser
While ie.ReadyState <> READYSTATE_COMPLETE
    DoEvents
Wend


ieDoc.getElementById("00Nj0000009FpF9").Value = "Placeholder Text"


Call ClickButton(ieDoc, "Save")

End Sub

The element I'm interacting with on the second page is a text box. The function called (which works fine) is listed below.

Function ClickButton(ieDoc As HTMLDocument, ByVal strButtonName As String)
Dim objInputs As Object, ele As Variant
Set objInputs = ieDoc.getElementsByTagName("input")

For Each ele In objInputs
    If ele.Title = strButtonName Then
        ele.Click
    End If
Next
End Function

How do I reset ieDoc to equal the current page so that I am able to interact with the text box on the new page? Inserting the following before interacting with the text box does not work (and I have no idea why).

set ieDoc = ie.document

Thank you very much for any help you're able to offer!

Just `Set ieDoc = ie.Document` again after you load a new page. — Comintern, Nov 02 '16 at 19:59
@Comintern Unfortunately this does not work. I should have included that I have already tried this. — Bryce Draper, Nov 02 '16 at 20:04
are you positive that the textbox **always** has this id on the new page? `00Nj0000009FpF9`. Also try `Dim ieDoc as Object` in both places and see what happens. — Scott Holtzman, Nov 02 '16 at 21:03
@ScottHoltzman Yes, the textbox does always have the id. The code to edit it works when I navigate directly to the page that contains it - it just doesn't work if I navigate to that page from another. I tried `Dim ieDoc as Object` instead of as HTMLdocument in both the sub and function but this did not change anything. — Bryce Draper, Nov 02 '16 at 21:13
sorry i can't be more help, i did something very similar and had it working very smooth, but unfortunately i am no longer with that project :( — Scott Holtzman, Nov 02 '16 at 21:31
No worries @ScottHoltzman . I really appreciate you trying! :) — Bryce Draper, Nov 02 '16 at 21:40
Could you post the URL where you navigate? In variable `strHTML`. — Daniel Dušek, Nov 02 '16 at 23:45
@dee It is an internal salesforce page.Unfortunately, it's no use to anyone without login credentials. :( — Bryce Draper, Nov 03 '16 at 14:01
Hmm so look in the java script of the button `New Service Note` and check the URL where the browser navigates. Use this URL with your `IE.navigate` then. — Daniel Dušek, Nov 03 '16 at 14:17
@dee Unfortunately, that URL changes depending on which project is accessed through the `strHTML` variable, which is why I have it click the button (which always has the same name) instead of navigate directly to the webpage. I seem to have created a perfect storm of a problem, huh? :P. Thank you very much for all your help. — Bryce Draper, Nov 03 '16 at 15:11

score 1 · Answer 1 · answered Nov 03 '16 at 21:11

@dee You have gone to great lengths to answer this question. Thank you so much!

Your answer wasn't what solved it for me, but it is the reason I was able to solve it. You mentioned that it could be a frame which made us (a coworker and I) realize that IE may be finishing a process prior to a frame finishing loading.

The fix was as simple as adding

Application.wait DateAdd("s", 5, Now)

after

While ie.busy Or ie.ReadyState<> Readystate_complete: DoEvents: Wend

The last bit of the code now reads

While ie.Busy Or ie.ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend

Application.Wait DateAdd("s", 5, Now)

iedoc.getElementById("00Nj0000009FpF9").Value = "Place holder Text"

Call ClickButton(ieDoc, "Save")

It is an inelegant solution, but it does the job.

Thank you again for your help. I will be implementing a few of the other changes you made to make my code more explicit and more easily readable.

Daniel Dušek · Accepted Answer · 2016-11-03T16:32:07.843

I have created two pages where the first page contains the button and the second page contains the text box. Bellow the code which works fine, the text box is filled. So this simple example shows that it should work. Why is it not working with your internal page? No idea. Could it be that you have some IFrame there?

' Add reference to Microsoft Internet Controls (SHDocVw)
' Add reference to Microsoft HTML Object Library

Sub AddInfoFromIntranet()
    Dim ie As SHDocVw.InternetExplorer
    Dim doc As MSHTML.HTMLDocument
    Dim url As String

    url = "file:///c:/My Web Sites/main.html"
    Set ie = New SHDocVw.InternetExplorer
    ie.Visible = True
    ie.navigate url
    While ie.Busy Or ie.readyState <> READYSTATE_COMPLETE: DoEvents: Wend

    Dim newServiceNoteButton As MSHTML.HTMLInputElement
    Set doc = ie.document
    Set newServiceNoteButton = doc.querySelector("input[title='New Service Note']")

    If (Not newServiceNoteButton Is Nothing) Then
        newServiceNoteButton.Click
        While ie.Busy Or ie.readyState <> READYSTATE_COMPLETE: DoEvents: Wend
        Set doc = ie.document
        Dim targetTextBox As MSHTML.HTMLInputElement
        Set targetTextBox = doc.getElementById("00Nj0000009FpF9")
        If Not targetTextBox Is Nothing Then targetTextBox.Value = "Placeholder Text"
    End If
    ie.Quit
    Set ie = Nothing
End Sub

First page main.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<!-- saved from url=(0014)about:internet -->
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Untitled 1</title>
</head>
<body>
    <input type="button" title="New Service Note" value="Test" onclick="location.href='next.html';">
</body>
</html>

Second page next.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<!-- saved from url=(0014)about:internet -->
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Untitled 1</title>
</head>
<body>
    <input type="text" id="00Nj0000009FpF9">
</body>
</html>

Unable to reset HTML document after navigating to page in VBA

2 Answers2