# Excel VBA automatic fill and submit web forms from excel data



## Nemat2313 (Jan 18, 2016)

Hi,

My below code works find with F8 key, but run key doesn't fill the web form. I tried so many time readyState function but still doesn't work. Could anyone knows whats the problem there.  

Please watch this video to better understand the problem.
https://youtu.be/zxL5GlhG0iA




```
Sub Sprint()
   Dim IE As Object
   Dim objelement As Object
   Dim c As Integer
   Dim LastRow, i, j As Integer
  
   Set IE = CreateObject("InternetExplorer.Application")
   
   With IE
      .Visible = True
      .navigate "https://website url here #"
      
      'wait until first page loads
      Do Until .readyState = 4
        DoEvents
      Loop
   
      On Error Resume Next

Set sht = ThisWorkbook.Worksheets("Data")
LastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row
For j = 4 To LastRow
       
    i = 112
             
         If IE.document.all.Item(i).innertext = "ÔÍÑ (ãîñ. ïîøëèíà)" Then
         IE.document.all.Item(i).Click
         
         End If
    
   IE.Visible = True
   While IE.Busy
   DoEvents  'wait until IE is done loading page.
   Wend
         
   'populate fields
   
   With IE.document
      'text boxes
      
      .all("fio").Value = sht.Cells(j, 1) 
      .all("contact").Value = sht.Cells(j, 2)
      .all("payer_address").Value = sht.Cells(j, 3) 
      .all("inn_from").Value = sht.Cells(j, 4) '"771562265931"
      .all("inn").Value = sht.Cells(j, 5) '"7726062105"
      .all("account").Value = sht.Cells(j, 6) '"45914000"
      .all("purpose").Value = sht.Cells(j, 7) 
      .all("comment").Value = sht.Cells(j, 8) '"02.04.2016"
      .all("sum").Value = sht.Cells(j, 10) '"1000"
      .all("get_total_sum").Click
      '.all("now_pay").Click
    
    End With
        Set IE = Nothing

    Next j
 End With
End Sub
```


----------



## B___P (Jan 18, 2016)

Try to change
      Do Until .readyState = 4
        DoEvents
      Loop

and

   While IE.Busy
   DoEvents  'wait until IE is done loading page.
   Wend


to

Do While IE.Busy Or IE.ReadyState <> 4: DoEvents: Loop

Bye


----------



## Ombir (Jan 18, 2016)

Try this:


```
Dim IE as Object
Sub Sprint()
   Dim objelement As Object
   Dim c As Integer
   Dim LastRow, i, j As Integer
  
   Set IE = CreateObject("InternetExplorer.Application")
   
   With IE
      .Visible = True
      .navigate "https://website url here #"
      
      'wait until first page loads
   Ieready
      On Error Resume Next

Set sht = ThisWorkbook.Worksheets("Data")
LastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row
For j = 4 To LastRow
       
    i = 112
             
         If IE.document.all.Item(i).innertext = "ÔÍÑ (ãîñ. ïîøëèíà)" Then
         IE.document.all.Item(i).Click
         
         End If
    
   IE.Visible = True
   
   Ieready
         
   'populate fields
   
   With IE.document
      'text boxes
      
      .all("fio").Value = sht.Cells(j, 1)
      .all("contact").Value = sht.Cells(j, 2)
      .all("payer_address").Value = sht.Cells(j, 3)
      .all("inn_from").Value = sht.Cells(j, 4) '"771562265931"
      .all("inn").Value = sht.Cells(j, 5) '"7726062105"
      .all("account").Value = sht.Cells(j, 6) '"45914000"
      .all("purpose").Value = sht.Cells(j, 7)
      .all("comment").Value = sht.Cells(j, 8) '"02.04.2016"
      .all("sum").Value = sht.Cells(j, 10) '"1000"
      .all("get_total_sum").Click
      '.all("now_pay").Click
    
    End With
        Set IE = Nothing

    Next j
 End With
End Sub
Private Sub Wait(ByVal wSec As Long)
    wSec = wSec + Timer
    Do While Timer < wSec
       DoEvents
    Loop
End Sub
Private Sub Ieready()
    Wait 5
    Do While IE.readyState <> 4
        Wait 5
    Loop
End Sub
```


----------



## Nemat2313 (Jan 19, 2016)

Thanks a lot. Code works as I want. Just I've changed Wait 5 into Wait 1 to increase speed. 
Do While IE.Busy Or IE.ReadyState <> 4: DoEvents: Loop doesn't fill forms. Thank you, too!


----------



## Nemat2313 (Jan 19, 2016)

Dear Ombir

One more question. After filling form appears web messages to accept payment by click OK button. What we should to add to click the button. In excel has has  Application.DisplayAlerts = False function. is there any function like this on the web?

Regards,
Nemat


----------



## Ombir (Jan 19, 2016)

I can only be able to tell after checking HTML code of the website you're navigating. What is URL name and what exactly you're trying to do ?


----------



## Nemat2313 (Jan 19, 2016)

Ombir. When above code riches .all("now_pay").click will appears popUP menu with OK button. I have to manually click to continue each time. Below link you can find print screen of web site. Website url could access only with our companies computers. I couldn't find to add attachments here. So I could send you HTML codes by mail.

1. Video link https://drive.google.com/open?id=0B8mEUFOrXM4taDlscm14QlVBUGFDRmMtNWZ6VXoxUjlaOWRV

2. Gif file https://drive.google.com/open?id=0B8mEUFOrXM4tV0piT1RKN2hSM0JZNGZpSlRJbWtEb1V0b1Zj


----------



## Ombir (Jan 19, 2016)

Your HTML code is not visible in Video. Check this link http://www.mrexcel.com/forum/excel-questions/426740-using-visual-basic-applications-close-internet-explorer-message-box-popup.html . This problem is similar to yours. Hope this helps !


----------



## Nemat2313 (Jan 19, 2016)

I tried so many internet links about web popup massage, that link also tried. I think i couldn't put that codes in right places or with correct names. Could you please show me where i have to write that codes. 
Tomorrow i will linked HTML codes for you from companies computer.


----------



## Nemat2313 (Jan 20, 2016)

Ombir Here is HTML code link https://drive.google.com/open?id=0B8mEUFOrXM4tTkNaSG9NVUlvNlhlbEhiUXZHaVlwZFBjd1U0
On the web page when click below link opens form, that form opens not a new Window, so that HTML code also not changes. 

```
ФНС (гос. пошлина)
```

ФНС (гос. пошлина)


Submit "Отправить" button HTML code:

<input class="submit submit_save" id="now_pay" value="Отправить" type="submit">

When confirm web message appears HTML code debugger:

```
if (confirm("Сумма платежа: "+$('#total_sum').val()+" руб.\nСумма, отправляемая поставщику: "+$('#sum').val()+" руб.\n\nПродолжить?")) {
                        return true;
                    }
                } else {
```
if (confirm("Сумма платежа: "+$('#total_sum').val()+" руб.\nСумма, отправляемая поставщику: "+$('#sum').val()+" руб.\n\nПродолжить?")) {
                        return true;
                    }
                } else {


----------



## Nemat2313 (Jan 18, 2016)

Hi,

My below code works find with F8 key, but run key doesn't fill the web form. I tried so many time readyState function but still doesn't work. Could anyone knows whats the problem there.  

Please watch this video to better understand the problem.
https://youtu.be/zxL5GlhG0iA




```
Sub Sprint()
   Dim IE As Object
   Dim objelement As Object
   Dim c As Integer
   Dim LastRow, i, j As Integer
  
   Set IE = CreateObject("InternetExplorer.Application")
   
   With IE
      .Visible = True
      .navigate "https://website url here #"
      
      'wait until first page loads
      Do Until .readyState = 4
        DoEvents
      Loop
   
      On Error Resume Next

Set sht = ThisWorkbook.Worksheets("Data")
LastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row
For j = 4 To LastRow
       
    i = 112
             
         If IE.document.all.Item(i).innertext = "ÔÍÑ (ãîñ. ïîøëèíà)" Then
         IE.document.all.Item(i).Click
         
         End If
    
   IE.Visible = True
   While IE.Busy
   DoEvents  'wait until IE is done loading page.
   Wend
         
   'populate fields
   
   With IE.document
      'text boxes
      
      .all("fio").Value = sht.Cells(j, 1) 
      .all("contact").Value = sht.Cells(j, 2)
      .all("payer_address").Value = sht.Cells(j, 3) 
      .all("inn_from").Value = sht.Cells(j, 4) '"771562265931"
      .all("inn").Value = sht.Cells(j, 5) '"7726062105"
      .all("account").Value = sht.Cells(j, 6) '"45914000"
      .all("purpose").Value = sht.Cells(j, 7) 
      .all("comment").Value = sht.Cells(j, 8) '"02.04.2016"
      .all("sum").Value = sht.Cells(j, 10) '"1000"
      .all("get_total_sum").Click
      '.all("now_pay").Click
    
    End With
        Set IE = Nothing

    Next j
 End With
End Sub
```


----------



## Ombir (Jan 20, 2016)

Nemat, HTML code shared by you is not complete. Install Firebug addon and *right click on Pop Up dialog box* and click inspect element with firebug. Share only dialogue box html code here. After that I can only able to figure out some solution !


----------



## Nemat2313 (Jan 20, 2016)

Ombir, here is HTML codes, I get them by firebug, as you said.

Main menu of form  https://drive.google.com/open?id=0B8mEUFOrXM4tbXFVaGRnLTAtVjZqR09sOWNFcnl0dWlfTkRB
Find full codes below:
Page1  https://drive.google.com/open?id=0B8mEUFOrXM4tN2JiV2ROa0daTk1SRWh2VE1EQk1LbDhndnVn
Page2  https://drive.google.com/open?id=0B8mEUFOrXM4tLTVtZVI0RmFnRXkzdUkwSU1DTU5uNkVQaExF
Page3  https://drive.google.com/open?id=0B8mEUFOrXM4tSXI2WDE5MmMyc3B1c3BXS2I5cG85VXUtZ2hr
Page4  https://drive.google.com/open?id=0B8mEUFOrXM4tSndFSzY0d2RQenBJanNlaEd2WGlPbHVKdmlF
Page5  https://drive.google.com/open?id=0B8mEUFOrXM4tMDAwbkF2Ti0yVGE3YUF6dE82Vy03cl96Nmlz


----------



## Ombir (Jan 20, 2016)

Nemat, I asked HTML code of dialogue box where Ok and Cancel button are appearing not the page on which you're filling all details. *Right click on Pop Up dialog box when it appear and share that code here. *Further, Along with Popup Box HTML Code, If you can share link of any public site on which similar popup is coming then it would be of great help !


----------



## Nemat2313 (Jan 20, 2016)

Ombir, I think there isn't HTML code for Pop Up dialog box. Wacth below video to see what appears when I click Submit button and Ok button.

https://drive.google.com/open?id=0B8mEUFOrXM4tQ2VLMlo3T3J0OWdRMFgxZGZkS3hZYTNhdDdJ

There is highlights below code once when I click OK button this code 
<div class="igtranslator-activator-icon bounceIn" style="background-image: url("resource://jid1-dgnibwqga0sibw-at-jetpack/data/icons/home.png"); display: none; top: 409px; left: 1165px;" title="Click to Show Translation"></div>


----------



## Nemat2313 (Jan 20, 2016)

div class="igtranslator-activator-icon bounceIn" style="background-image: url("resource://jid1-dgnibwqga0sibw-at-jetpack/data/icons/home.png"); display: none; top: 436px; left: 1190px;" title="Click to Show Translation"></div


----------



## Nemat2313 (Jan 20, 2016)

Ombir, actually that popup dialog-box not a new windows a web page but is was java-script. That's way when popup appears nothing I could touch/click, besides OK/Cancel buttons.
I've found below link. Please find there is at end of the solution provided by Tim Williams. if it is similar to my problem, how i should change me code?
javascript - Handle Pop-Up While Navigating with IE - Stack Overflow


----------



## Ombir (Jan 20, 2016)

Nemat, Please visit this link Excel VBA controlling IE confirmation popup box and try to understand the process. If still you have some problems then wait for some expert to chime in here !


----------



## Nemat2313 (Jan 26, 2016)

Ombir, I've resolved the problem by using AutoIt program. AutoIt can press enter on the active windows. The disadvantages is it is not running background, because of AutoIt should press enter of active window. Any way it is awesome to easy job. Thank you very much for your help!

Here is the code for AutoIt program:

```
WinWait("Run", "", 1)
Send("{Enter}")
```


```
Dim IE As Object
Sub Sprint3()
   Dim objelement As Object
   Dim c As Integer
   Dim LastRow, i, j, m As Integer
   Dim UrlTochka As String

   Set IE = CreateObject("InternetExplorer.Application")
    
    Set sht = ThisWorkbook.Worksheets("Data")
    LastRow = sht.Cells(sht.Rows.Count, "D").End(xlUp).Row
    UrlTochka = "https://website.com#"

    For j = 4 To LastRow
        Select Case j
            Case 20
               ActiveWindow.SmallScroll Down:=18
            Case 40
               ActiveWindow.SmallScroll Down:=18
            Case 80
               ActiveWindow.SmallScroll Down:=18
            Case 120
               ActiveWindow.SmallScroll Down:=18
            Case 160
               ActiveWindow.SmallScroll Down:=18
            Case 200
               ActiveWindow.SmallScroll Down:=18
            Case 240
               ActiveWindow.SmallScroll Down:=18
            Case 280
               ActiveWindow.SmallScroll Down:=18
            Case 320
               ActiveWindow.SmallScroll Down:=18
            Case 360
               ActiveWindow.SmallScroll Down:=18
             End Select
    sht.Range(Cells(j, 1), Cells(j, 15)).Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 6750105
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With IE
                
      .Visible = True
      .navigate UrlTochka
      
      'wait until first page loads
   IEready
      On Error Resume Next
      
    i = 112
             
         If IE.document.all.Item(i).innertext = "text here on the site" Then
         IE.document.all.Item(i).Click
         
         End If
    
   IE.Visible = True
   
   IEready

   'populate fields
   
      If sht.Cells(j, 3) <> "" Then
      IE.document.all("inn_from").Value = sht.Cells(j, 3)
      End If
      
   With IE.document
      'text boxes
      .all("fio").Value = sht.Cells(j, 4)
      .all("contact").Value = "9117057773"
      .all("payer_address").Value = sht.Cells(j, 6) & " " & sht.Cells(j, 5)
      .all("inn").Value = sht.Cells(j, 9) '"7726062105"
      .all("account").Value = sht.Cells(j, 8) '"45914000"
      .all("purpose").Value = 'sht.Cells(j, 7)
      .all("comment").Value = Format(sht.Cells(j, 10).Value, "dd/mm/yyyy") '"02.04.2016"
      .all("sum").Value = sht.Cells(j, 11) '"1000"
      .all("get_total_sum").Click
      
    End With
      
     PressEnter
     IE.document.all("now_pay").Click
     IEready_Two
      
    sht.Range(Cells(j, 1), Cells(j, 15)).Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    sht.Cells(j, 14) = "Paid"
    End With
 Next j
  Set IE = Nothing
  m = LastRow - 3
  MsgBox m & " person has been paid!"
End Sub
Private Sub Wait(ByVal wSec As Long)
    wSec = wSec + Timer
    Do While Timer < wSec
       DoEvents
    Loop
End Sub
Private Sub IEready()
    Wait 1
    Do While IE.readyState <> 4
        Wait 1
    Loop
End Sub

Private Sub IEready_Two()
    Wait 5
    Do While IE.readyState <> 4
        Wait 5
    Loop
End Sub

Private Sub PressEnter()
Dim runscript
Dim FileName As String

FileName = ThisWorkbook.Path & "\PressEnter.au3"
runscript = Shell("C:\Program Files (x86)\AutoIt3\AutoIt3_x64.exe " & FileName)

End Sub
```


----------



## Ombir (Jan 27, 2016)

Glad to know that you resolved it by yourself !


----------



## mnoah (Jun 1, 2016)

I have been able to use Ombir's original adaptation to kind of meet my needs, but my problem is that the macro never ends.  It just keeps running, even though nothing is changing. 

I am using the code to loop through a range in my worksheet and put an SSN and DOB value on a webpage to check an eligibility, but it never gets past the first range (A2 and B2).  I want the code to then go to A3, B3, and so on.

```
Sub Sprint()   Dim objelement As Object
   Dim c As Integer
   Dim LastRow, i, j As Integer
   
  
  
   Set IE = CreateObject("InternetExplorer.Application")
   
   With IE
      .Visible = True
      .navigate "https://www.njmmis.com/mevs.aspx"
      
      'wait until first page loads
   Ieready
      On Error Resume Next


Set sht = ThisWorkbook.Worksheets("Data")
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).row
For j = 2 To LastRow
       
    i = 112
             
         If IE.document.all.Item(i).innertext = "ÔÍÑ (ãîñ. ïîøëèíà)" Then
         IE.document.all.Item(i).Click
         
         End If
    
   IE.Visible = True
   
   Ieready
         
   'populate fields
  
  
   With IE.document
    
      'text boxes
      .all("rblParams")(2).Checked = True
      .all("txtBeginDate").Value = "06/01/2016"
      .all("txtEndDate").Value = "06/30/2016"
      .all("txtSSN").Value = sht.Cells(j, 1)
      .all("txtDOB").Value = Format(sht.Cells(j, 2), "mm/dd/yyyy")
      .all("btnSubmit").Click
      
    
      
   
    
    End With
    
        Set IE = Nothing


    Next j
 End With
End Sub
    Private Sub Wait(ByVal wSec As Long)


    
       wSec = wSec + Timer
        Do While Timer < wSec
           DoEvents
        Loop
    End Sub


Private Sub Ieready()
    Wait 5
    Do While IE.readyState <> 4
        Wait 2
    Loop
End Sub
```


----------



## Nemat2313 (Jan 18, 2016)

Hi,

My below code works find with F8 key, but run key doesn't fill the web form. I tried so many time readyState function but still doesn't work. Could anyone knows whats the problem there.  

Please watch this video to better understand the problem.
https://youtu.be/zxL5GlhG0iA




```
Sub Sprint()
   Dim IE As Object
   Dim objelement As Object
   Dim c As Integer
   Dim LastRow, i, j As Integer
  
   Set IE = CreateObject("InternetExplorer.Application")
   
   With IE
      .Visible = True
      .navigate "https://website url here #"
      
      'wait until first page loads
      Do Until .readyState = 4
        DoEvents
      Loop
   
      On Error Resume Next

Set sht = ThisWorkbook.Worksheets("Data")
LastRow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row
For j = 4 To LastRow
       
    i = 112
             
         If IE.document.all.Item(i).innertext = "ÔÍÑ (ãîñ. ïîøëèíà)" Then
         IE.document.all.Item(i).Click
         
         End If
    
   IE.Visible = True
   While IE.Busy
   DoEvents  'wait until IE is done loading page.
   Wend
         
   'populate fields
   
   With IE.document
      'text boxes
      
      .all("fio").Value = sht.Cells(j, 1) 
      .all("contact").Value = sht.Cells(j, 2)
      .all("payer_address").Value = sht.Cells(j, 3) 
      .all("inn_from").Value = sht.Cells(j, 4) '"771562265931"
      .all("inn").Value = sht.Cells(j, 5) '"7726062105"
      .all("account").Value = sht.Cells(j, 6) '"45914000"
      .all("purpose").Value = sht.Cells(j, 7) 
      .all("comment").Value = sht.Cells(j, 8) '"02.04.2016"
      .all("sum").Value = sht.Cells(j, 10) '"1000"
      .all("get_total_sum").Click
      '.all("now_pay").Click
    
    End With
        Set IE = Nothing

    Next j
 End With
End Sub
```


----------



## Nemat2313 (Jun 2, 2016)

You haven't changed code that opens form web page.    

Try below code to Login first, if it works fine you can write next code to fill the form.


```
Sub Login()
    Dim objelement As Object
   Dim c As Integer
   Dim LastRow, i, j As Integer
   
  
  
   Set ie = CreateObject("InternetExplorer.Application")
   
With ie
      .Visible = True
      .navigate "https://www.njmmis.com/mevs.aspx"
      
      'wait until first page loads
   On Error Resume Next
    Do
        If ie.ReadyState = 4 Then
            ie.Visible = True
            Exit Do
        Else
            DoEvents
        End If
    Loop
  

 'Login code here
                With ie.document
                      .all("txtUserName").Value = "enter here UserName"       'Sheet1.Range("B2").Value
                       .all("txtPassword").Value = "enter your Password"         'Sheet1.Range("B3").Value
                      .all("btnSubmit").Click
                   
                End With
   

End With
 
    Do While ie.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
 

    ie.Visible = True
 

    Set ie = Nothing
    End Sub
```


----------



## imgaur7 (Jan 19, 2019)

Thanks Ombir, just to ask you something here if you can help

1. After clicking on Submit button say it opens a new web page
2. How to make the VBA read Elements name/id/tag from it, as New IE page is not set as the first one (Set IE = CreateObject("InternetExplorer.Application"))

here is some issues I am facing in defining the next IE page and reading the Elements from it to fill the form.

Kindly suggest


----------



## Macropod (Jan 20, 2019)

This appears to be related to (if not effectively a duplicate of) the question you asked in: https://www.mrexcel.com/forum/excel-questions/1084387-vba-web-elements.html
Please read Mr Excel's policy in Rule 12 against asking questions about the same topic in multiple threads: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html


----------



## imgaur7 (Jan 22, 2019)

Thanks for updating me, its my miss, kindly delete these for now and will check in future.


----------

