Solved

Using VBA to fill a web form not working

Posted on 2014-10-04
5
416 Views
Last Modified: 2014-10-20
I have spent two days on this.  Time to ask for help.  I am placing orders on Overstock.  The following code gives different errors when I try to select from two drop-down boxes.
I item is live, so you can try it on your end.



Enum READYSTATE
    READYSTATE_UNINITIALIZED = 0
    READYSTATE_LOADING = 1
    READYSTATE_LOADED = 2
    READYSTATE_INTERACTIVE = 3
    READYSTATE_COMPLETE = 4
End Enum

Sub Connect()
'to refer to the running copy of Internet Explorer
Dim ie As InternetExplorer
'to refer to the HTML document returned
Dim html As HTMLDocument
'open Internet Explorer in memory, and go to website
Set ie = New InternetExplorer
ie.Visible = True
ie.navigate "http://www.overstock.com/Bedding-Bath/Aloha-Girls-Multicolor-Printed-Cotton-Pieced-Quilt-Set/5036546/product.html?keywords=12915080&searchtype=Header"
'Wait until IE is done loading page
Do While ie.READYSTATE <> READYSTATE_COMPLETE
DoEvents
Loop

varTemp = Split("12915080-6882393", "-")
ItemNum = varTemp(0)
OptionNum = varTemp(1)
ProductNum = "5036546"
ElementID = "addid" & ProductNum
ie.document.getElementById(ElementID).Value = OptionNum

ElementID = "addqty" & ProductNum
ie.document.getElementById(ElementID).Value = 4

Stop
        ie.navigate "http://www.yahoo.com"
        Do While ie.Busy: DoEvents: Loop
        Do Until ie.READYSTATE = READYSTATE_COMPLETE: DoEvents: Loop

ie.Quit
Set ie = Nothing


End Sub

Open in new window

0
Comment
Question by:rrhandle8
  • 2
  • 2
5 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40361478
I tweaked the code and got no errors, but also no real results aside from the Overstock and Yahoo windows opening:
Sub Connect()
    'to refer to the running copy of Internet Explorer
    Dim ie As Object
    
    'to refer to the HTML document returned
    Dim html As Object
    
    'open Internet Explorer in memory, and go to website
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
    ie.navigate "http://www.overstock.com/Bedding-Bath/Aloha-Girls-Multicolor-Printed-Cotton-Pieced-Quilt-Set/5036546/product.html?keywords=12915080&searchtype=Header"
    
    'Wait until IE is done loading page
    Do While ie.READYSTATE <> READYSTATE_COMPLETE
        DoEvents
    Loop
    
    varTemp = Split("12915080-6882393", "-")
    ItemNum = varTemp(0)
    OptionNum = varTemp(1)
    ProductNum = "5036546"
    ElementID = "addid" & ProductNum
    ie.document.getElementById(ElementID).Value = OptionNum
    
    ElementID = "addqty" & ProductNum
    ie.document.getElementById(ElementID).Value = 4
    Stop
    
    ie.navigate "http://www.yahoo.com"
    Do While ie.Busy: DoEvents: Loop
    Do Until ie.READYSTATE = READYSTATE_COMPLETE: DoEvents: Loop

    ie.Quit
    Set ie = Nothing
End Sub

Open in new window


-Glenn
0
 

Author Comment

by:rrhandle8
ID: 40361490
Glenn,
I tried your code.  It has the same problem I had before: The qty does not get set.  Put a stop right before the yahoo link.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40361555
There must be another method that will allow the quantity field to update.  If one manually selects the same option ("Twin"), the quantity field updates dynamically. In the code, it does not.  However, I'm at a loss as to what method you can use to make this happen.
0
 

Author Comment

by:rrhandle8
ID: 40361561
Me too, Glenn.  This is a tough one.
0
 
LVL 12

Accepted Solution

by:
James Elliott earned 500 total points
ID: 40363320
You'll need to debug the JS in the website and trace the function call & parameters. You can then mimic this in your VBA.

Below is a similar piece of work I did to automate a client website. Nothing particularly relevant, but it might expose some methods you could use. For example firing an 'on change' event, simulating a click, or executing a script.

Option Explicit

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Public Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hWnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Public Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As Long) As Long
Public Declare Function BringWindowToTop Lib "user32" (ByVal hWnd As Long) As Long
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Declare Function apiShowWindow Lib "user32" Alias "ShowWindow" (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long
Public Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
Public Declare Function SendMessageByString Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As String) As Long
Public Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" (ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long
 
Public Const BM_CLICK = &HF5

Public Sub IE_Automation(lst As Object)
    Dim IE As SHDocVw.InternetExplorer
    Dim sReportName As String
    Dim bReady As Boolean
    Dim i As Integer, j As Integer
    Dim oCollection As Variant
    Dim hwndIE As Long
    Dim downloadcomplete As Boolean
    Dim FileDownloadHandle As Long, OpenButtonHandle As Long, IePopupBarHandle As Long
    Dim FileDownloadClassicPopup As Boolean

lst.Clear

lst.AddItem "Please wait......"
lst.AddItem ""

    Set IE = New InternetExplorer
    
With IE

    '.Visible = True
    .Navigate "https://..."

While .Busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Sleep 250: Wend: Sleep 250

lst.AddItem "Entering login details..."
    
    .Document.getelementbyID("UserName").Value = "username"
    .Document.getelementbyID("Password4").Value = "password"
    .Document.getelementbyID("submit4").Click
    
While .Busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Sleep 250: Wend: Sleep 3500

    lst.AddItem "LOGIN Successful..."
    lst.AddItem "Setting report parameters..."
 
    .Document.frames(0).Document.getelementbyID("selReport").Value = "0155"

While .Busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Sleep 250: Wend: Sleep 250

    .Document.frames(0).Document.getelementbyID("selReport").fireevent "onchange"

 While .Busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Sleep 250: Wend: Sleep 2500

    .Document.frames(0).Document.getelementbyID("fromDate").Value = Format(Now() - 1, "dd/mm/yyyy")
    .Document.frames(0).Document.getelementbyID("toDate").Value = Format(Now() - 1, "dd/mm/yyyy")
    sReportName = "TVF" & Format(Now(), "ddmmyyyyhhmmss")
    .Document.frames(0).Document.getelementbyID("user_description").Value = sReportName
    .Document.frames(0).Document.getelementbyID("sendreqbutton").Click

While .Busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Sleep 250: Wend: Sleep 250

lst.AddItem "Waiting for report to generate..."

i = 1
    
    Do
    
        If .Document.frames(0).Document.getelementbyID("Desc" & i).Value = sReportName Then
        
            Set oCollection = .Document.frames(0).Document.getelementbyID("row" & i)
            
            If oCollection.Cells(5).innertext = "Completed" Then
                
                .Document.frames(0).Document.getelementbyID("Desc" & i).Click
                bReady = True
                Exit Do
            
            Else
                lst.AddItem "Not done yet. Checking again..."
                .Document.frames(0).Document.parentwindow.execscript ("reloadPage()")
                 While .Busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Sleep 250: Wend: Sleep 2500
                i = 0
            
            End If
            
        End If
        
        i = i + 1
    
    Loop Until bReady
    
    lst.AddItem "Report ready. Downloading..."
    
   Set oCollection = .Document.frames(0).Document.getelementsbyname("downloadtype")
   
    oCollection(0).Value = "2"
   
    Set oCollection = .Document.frames(0).Document.getelementsbyname("downloadbutton")
   
    oCollection(0).Click
    
    While .Busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Sleep 250: Wend: Sleep 2500

    hwndIE = IE.hWnd

    Do While downloadcomplete = False
             
            IePopupBarHandle = FindWindowEx(hwndIE, 0, "Frame Notification Bar", vbNullString)
            IePopupBarHandle = FindWindowEx(IePopupBarHandle, 0, "DirectUIHWND", vbNullString)
             
            If IePopupBarHandle <> 0 And IE.ReadyState = 4 Then
                downloadcomplete = True
            Else
                FileDownloadHandle = FindWindow("#32770", "File Download")
                If FileDownloadHandle <> 0 Then downloadcomplete = True: FileDownloadClassicPopup = True
            End If
            DoEvents
            Sleep 250
        Loop

        Unload frmMain

        If FileDownloadClassicPopup = True Then
             
            OpenButtonHandle = FindWindowEx(FileDownloadHandle, 0, "Button", "&Open")
            SetForegroundWindow (OpenButtonHandle)
            Sleep 1000
            SendMessage OpenButtonHandle, BM_CLICK, 0, 0
            DoEvents
        Else
            SetForegroundWindow (hwndIE)
            Sleep 1000
            SendKeys "%O", Wait:=True
            DoEvents
        End If

    Set IE = Nothing

    Application.StatusBar = False
    
    .Document.frames(0).Document.parentwindow.execscript ("setFooterCommand('signout'); closing=false;window.status='Logging Out - Please Wait.';top.actionFrame.location.href='/atom/logout.jsp?userid=p2EU3';")
    
    While .Busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Sleep 250: Wend: Sleep 2500
    
    .Quit
    
    
End With

End Sub

Open in new window

0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Internet is a big network which is formed by connecting multiple small networks.It is a platform for all the users which are connected to it.Internet act as platform in different fields. Such as: Internet  as a collaboration platform. Internet  as…
Citrix XenApp, Internet Explorer 11 set to Enterprise Mode and using central hosted sites.xml file.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now