0

First of all. I'm a dummy in programming.

I was trying to use JavaScript and VBA in Excel to automate some IE actions to go to some page and download Excel files from that page, as I need to do this everyday.

  1. The excel file to be downloaded does not have a link.

  2. The VBA script did well in auto login, but when the IE goes to a second page ( different URL ), it always pops up a run time error 70 -- permission denied. I tried to modified the security in IE options but not working.

  3. I use JavaScript to do the auto login and it worked well too, but again after the IE goes to a diff page, it seems stop working.

  4. So I suspect that it might be that my script did not grab the new html document when url changes.

VBA:

Sub vbadownload()

Dim objIE As SHDocVw.InternetExplorer ' internet controls
Dim htmlDoc As MSHTML.HTMLDocument ' html object lib
Dim htmlInput As MSHTML.HTMLInputElement
Dim htmlDiv As MSHTML.HTMLDivElement
Dim htmlColl As MSHTML.IHTMLElementCollection    

Set objIE = New SHDocVw.InternetExplorer
''''''''''' first log in
With objIE
.Navigate "mainpage.com" ' log in page
.Visible = 1
Do While .READYSTATE <> 4: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:01"))

        'set user name and password
        Set htmlDoc = .Document
        Set htmlColl = htmlDoc.getElementsByTagName("INPUT")
        Do While htmlDoc.READYSTATE <> "complete": DoEvents: Loop
            For Each htmlInput In htmlColl
                If htmlInput.Name = "username" Then
                    htmlInput.Value = "xxxx"  'username
                Else
                    If htmlInput.Name = "password" Then
                        htmlInput.Value = "xxxx" 'password
                    End If
                End If
            Next htmlInput

        'click login
        Set htmlDoc = .Document
        Set htmlColl = htmlDoc.getElementsByTagName("input")
        Do While htmlDoc.READYSTATE <> "complete": DoEvents: Loop
            For Each htmlInput In htmlColl
                If Trim(htmlInput.Type) = "submit" Then
                     htmlInput.Click 'click
                     Exit For
                 End If
            Next htmlInput

' now it goes to second page after submit
' and I want click some button on second page, it says permission denied.
' so how do i make below codes work on current page without typing new url. ( as the url      actually does not change, I guess because it's js webpage? I don't know)

 Set htmlDoc = .Document
        Set htmlColl = htmlDoc.getElementsByTagName("div")
        Do While htmlDoc.READYSTATE <> "complete": DoEvents: Loop
            For Each htmlDiv In htmlColl
                If Trim(htmlDiv.ID) = "123" Then
                     htmlDiv.Click 'click
                     Exit For
                 End If
            Next htmlDiv
 End With

JavaScript

var ie = new ActiveXObject("InternetExplorer.Application");
ie.visible=true;
ie.navigate("www.sample.com");

while(ie.busy)(WScript.sleep(100));

var doc1 = ie.document;
var window1 = doc1.window;
var form1 = doc1.forms[0];

form1.username.value="xxx";
form1.password.value="xxxx";
form1.submit();


/* again, from here, it goes to second page, and it stops working.
* how do I get/parse current html document? */


var div1 = doc1.getElementsByTagName("div");


for (var i=0; i<div1.length; ++i)
{

if (div1[i].className="fff") 
 {div1[i].click()}
}

1 Answer 1

1

Every time you refresh the page (by submitting a form or using .navigate) you need to repeat your "sleep" loop so that you wait for the page to complete loading, and then grab a new reference to the document object. After you navigate it's a new page, so you can't just continue to use the "old" document reference.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.