Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

Scroll through Internet Explorer Dialogue Box Using Excel VB Macro

I am constructing a macro that will help me enter variable addresses when buying on Amazon. Earlier today I got help moving from the Shopping Cart to Checkout (see attached Excel example and http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25020949.html). I am now unfortunately stuck on the next step. Amazon asks for my password. I use Roboform which on seeing the prompt fills in the info but I still need to acknowledge the prompt by hitting "Yes" in the Credential Manager Dialogue Box; either that or simply hit Enter. I would like help getting the macro to do that automatically rather than having me do it manually. Thank you.
Worker-2.xls
Amazon-Signin.jpg
0
bpfsr
Asked:
bpfsr
  • 6
  • 4
1 Solution
 
Surone1Commented:
you may want to try "sendkeys()"
0
 
bpfsrAuthor Commented:
the sendkeys seems like the way to go but I cannot get it to work two times in a row! I entered it as

SendKeys ("yes")

The first time I ran it it inserted another yes into the parenthesis leaving

SendKeys("yes"yes)

and it actually moved on to the next page. The next time it didn't work...
0
 
bpfsrAuthor Commented:
Might I need to put a pause before the send keys or some code to make sure the credential manager is loaded before the macro executes the sendkeys?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TomSchreinerCommented:
Replace all of your current code with the following and set the two references that I mentioned to you in the earlier post or download the attached.  Edit the assignments to the email and password constants at the top of the module.  You may want to consider checking into Amazon's API.  I am fairly certain that they offer an API.
 

Option Explicit

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Const EmailAddress As String = "YourEmailAddress"
Private Const PassWord As String = "YourPassword"

Sub GetWebPageInfo()
    Dim ws1 As Worksheet, ToCheckoutImg As HTMLImg
    Dim IE As WebBrowser, iedoc As HTMLDocument, iebody As String
    Dim PWForm As HTMLFormElement, Body As HTMLBody
     
    'Application.DisplayAlerts = False
    'Application.ScreenUpdating = False
                 
    Set IE = Nothing
    Set IE = New InternetExplorer
     
    Set ws1 = ActiveWorkbook.Sheets("Sheet1")
    
    IE.Visible = True
    NavigateWithTimeOut IE, "http://www.amazon.com/gp/cart/view.html/ref=gno_cart", 30, READYSTATE_COMPLETE, iedoc, "complete"
    Set ToCheckoutImg = GWFO(iedoc, "getElementsByName", VbMethod, 30, "proceedToCheckout")(0)
    ToCheckoutImg.Click
    'assign "EVENT" to the URL argument if the navigation is caused by by a user event
    NavigateWithTimeOut IE, "EVENT", 30, READYSTATE_COMPLETE, iedoc, "complete"
    Set Body = GWFO(iedoc, "getElementsByTagName", VbMethod, 30, "BODY")(0)
    
    'check for sign in form
    If InStr(Body.innerText, "I am a returning customer,") Then
        Set PWForm = GWFO(iedoc, "getElementsByName", VbMethod, 30, "signin")(0)
        PWForm("emailLabel").Value = EmailAddress
        PWForm("radioOldCustomer").Checked = True
        PWForm("userPassword").Value = PassWord
        PWForm.submit
    End If
    
    Stop
    'Application.DisplayAlerts = True
    'Application.ScreenUpdating = True
 
End Sub

Private Function ValidateElement(Object) As Object


End Function

Private Function NavigateWithTimeOut(IE As WebBrowser, URL As String, TimeOutSeconds As Integer, _
    MinimumState As tagREADYSTATE, Optional GetDoc, Optional DocMinimumState As String) As Integer

    Dim TimeOutTime As Date, Doc As HTMLDocument
    
    NavigateWithTimeOut = -1
    
    If URL <> "EVENT" Then IE.navigate URL
    TimeOutTime = DateAdd("s", TimeOutSeconds, Now)
    
    Do Until IE.readyState = MinimumState And Not IE.Busy
        DoEvents
        If Now > TimeOutTime Then
            NavigateWithTimeOut = 1
            Exit Function
        End If
    Loop
    
    If Not IsMissing(GetDoc) Then
        Set Doc = IE.document
        If DocMinimumState = "" Then DocMinimumState = "complete"
        Do Until Doc.readyState = DocMinimumState
            DoEvents
            If Now > TimeOutTime Then
                NavigateWithTimeOut = 3
                Exit Function
            End If
        Loop
    End If
    
    Set GetDoc = Doc
    NavigateWithTimeOut = 0
    Sleep 100
End Function
 
'GenericWaitForObject
Private Function GWFO(Object As Object, ProcName As String, CallType As VbCallType, _
    TimeOutSeconds As Integer, Optional Args) As Object
     
    Dim Res As Object, TimeOutTime As Date
    TimeOutTime = DateAdd("s", TimeOutSeconds, Now)
    
    On Error Resume Next
    If ProcName = "" Then
        Do Until Not Res Is Nothing
            Set Res = Object
            DoEvents
        Loop
    Else
        Do Until Not Res Is Nothing
            Set Res = CallByName(Object, ProcName, CallType, Args)
            DoEvents
        Loop
    End If
    
    Set GWFO = Res
End Function

Open in new window

Worker-2-2-.xls
0
 
bpfsrAuthor Commented:
That is GREAT! Thanks. I have tried Amazon API and it does not cover this type of buying. So just moving forward the sequence goes on - believe it or not - for six subsequent pages. I am looking at your code and thinking the key lies in:

    If InStr(Body.innerText, "I am a returning customer,") Then
        Set PWForm = GWFO(iedoc, "getElementsByName", VbMethod, 30, "signin")(0)
        PWForm("emailLabel").Value = EmailAddress
        PWForm("radioOldCustomer").Checked = True
        PWForm("userPassword").Value = PassWord
        PWForm.submit
    End If
   
Would you be kind enough to give a bried explanation of this section so I don't have to keep bothering you for each subsequent page? I understand the "If InStr..." identifies the page I'm trying to figure out the next part. The page after this asks to select an account and then has the accounts listed by radio button. So I tried

    NavigateWithTimeOut IE, "EVENT", 30, READYSTATE_COMPLETE, iedoc, "complete"
    Set Body = GWFO(iedoc, "getElementsByTagName", VbMethod, 30, "BODY")(0)
   
    If InStr(Body.innerText, "Select an account") Then
        Set PWForm = GWFO(iedoc, "getElementsByName", VbMethod, 30, "continue")(0)
        PWForm("radio_A344WU69QFNST0").Checked = True
        PWForm.submit
    End If
 
As the next piece of code but I am getting the error run-time error '91' - object variable or with block variable not set. Thanks again for all the help.
0
 
bpfsrAuthor Commented:
Sorry to be clear the error comes on the line:

PWForm("radio_A344WU69QFNST0").Checked = True
0
 
TomSchreinerCommented:
I saw your reply but do not have time to give you a thorough answer right now.  I won't forget about you and will try and get back later today.  Have a nice day.  Tom
0
 
TomSchreinerCommented:
Chances are, "radio_A344WU69QFNST0" is a name that is dynamically created each time the page is viewed.  If this is the case, you will have to reference the element in one of a handful of other ways.  I will have to duplicate your navigation to really offer any specific help.
If it is not, you are referencing it wrong.  I will need to see the actual page or at least the source HTML.
0
 
bpfsrAuthor Commented:
Tom,
Here is the source code for one just created
Account-choice.txt
0
 
TomSchreinerCommented:
Set Body = GWFO(iedoc, "getElementsByTagName", VbMethod, 30, "BODY")(0)
getElementsByTagName is a method that the HTMLDocument object exposes.  It returns a collection of children elements with whatever tagname you pass as an argument.  The HTML document object actually has a "body" property but methods are more dependable because of the nature of HTML.  HTML is not an exact art by any stretch.  Since the getElementsByTagName method returns a collection, we will have to provide an index or key to grab the item of our choice.   There is only one body element so we know that it will be the first (and only in this case) child returned.  Hence the (0)
If InStr(Body.innerText, "Select an account,") Then
You know what this means.
FormElement = GWFO(iedoc, "getElementByTagId", VbMethod, 30, "ibSelectionForm")

ibSelectionForm is the ID of this form.  Yet another method used here is the getElementByTagId.  Note the singular.  This does not return a collection but a single item.  Why?  Because a web page cannot contains elements with duplicate ID's.  So we are looking for a specific form with this id
 
With GWFO(FormElement, "getElementsByTagName", VbMethod, 30, "input")


 The form element also exposes the getElementsByTagName method.  There are three child input elements within the parent form.  The first two are the radio buttons.  The third is the submit input img.
 .Item(0).Checked = True 'Use my personal account
 .Item(1).Checked = True 'Use my Book It Corp account
SelectAccountForm.submit
       
the FormElement also exposes the submit method which is self explanatory.  I could not test this code because I do not have these various accounts.
Have a good day.  Tom

Sub GetWebPageInfo()
    Dim ws1 As Worksheet, ToCheckoutImg As HTMLImg
    Dim IE As WebBrowser, iedoc As HTMLDocument, iebody As String
    Dim FormElement As HTMLFormElement, Body As HTMLBody
     
    'Application.DisplayAlerts = False
    'Application.ScreenUpdating = False
                 
    Set IE = Nothing
    Set IE = New InternetExplorer
     
    Set ws1 = ActiveWorkbook.Sheets("Sheet1")
    
    IE.Visible = True
    NavigateWithTimeOut IE, "http://www.amazon.com/gp/cart/view.html/ref=gno_cart", 30, READYSTATE_COMPLETE, iedoc, "complete"
    Set ToCheckoutImg = GWFO(iedoc, "getElementsByName", VbMethod, 30, "proceedToCheckout")(0)
    ToCheckoutImg.Click
    'assign "EVENT" to the URL argument if the navigation is caused by by a user event
    NavigateWithTimeOut IE, "EVENT", 30, READYSTATE_COMPLETE, iedoc, "complete"
    Set Body = GWFO(iedoc, "getElementsByTagName", VbMethod, 30, "BODY")(0)
    
    'check for sign in form
    If InStr(Body.innerText, "I am a returning customer,") Then
        Set FormElement = GWFO(iedoc, "getElementsByName", VbMethod, 30, "signin")(0)
        FormElement("emailLabel").Value = EmailAddress
        FormElement("radioOldCustomer").Checked = True
        FormElement("userPassword").Value = PassWord
        FormElement.submit
        NavigateWithTimeOut IE, "EVENT", 30, READYSTATE_COMPLETE, iedoc, "complete"
    End If
    
    Set Body = GWFO(iedoc, "getElementsByTagName", VbMethod, 30, "BODY")(0)
    If InStr(Body.innerText, "Select an account,") Then
        FormElement = GWFO(iedoc, "getElementByTagId", VbMethod, 30, "ibSelectionForm")
        With GWFO(FormElement, "getElementsByTagName", VbMethod, 30, "input")
'            .Item(0).Checked = True 'Use my personal account
            .Item(1).Checked = True 'Use my Book It Corp account
        End With
        SelectAccountForm.submit
        NavigateWithTimeOut IE, "EVENT", 30, READYSTATE_COMPLETE, iedoc, "complete"
    End If
    
    Stop
    'Application.DisplayAlerts = True
    'Application.ScreenUpdating = True
 
End Sub

Open in new window

Worker-2-2.-0106.1918-.xls
0
 
bpfsrAuthor Commented:
Hello,
I did not forget this, I was trying furiously to figure this issue out for myself but I have given up! I am getting an error message "Compile Error: Variable Not Defined" on the line:

SelectAccountForm.submit

I can see "SelectAccountForm" is the wrong variable to sumit to get the page through; I have just not been able to figure out which variable it should be.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now