Automatic Web Form Fill Up using VBA and Internet Explorer,VBA Teacher Sourav,Kolkata 08910141720

Sub ienavigate()

Dim firstName, lastName, phone, UserName, address1, address2, city, state, postalCode, country, email, password, confirmPassword As Object
'Dim HTMLDoc As HTMLDocument
Dim ie As Object
Set ie = CreateObject("internetexplorer.application")
With ie

    .Visible = True
    .navigate "http://www.newtours.demoaut.com/mercuryregister.php"
    'to make the page load so that it will not close in fraction of second
    'to make the internet explorer wait
    'it should do some events
    Do While .busy
   
    DoEvents
   
    Loop
   
    'now if the internet explorer is not fully loaded it should do some events
   
    Do While .readystate <> 4
       
    DoEvents
   
    Loop
   
   
    'to make this application wait for 5 seconds
    'Application.Wait (Now + TimeValue("00:00:05"))
   
    'to search google for a phrase
    'get the name of the textbox by right click on it and inspect elements
    'it is q
   
   
    'Set searchtext = .document.getelementsbyclassname("gLFyf gsfi")
    Set firstName = .document.getElementsByName("firstName")
    firstName.Item.innerText = "Sourav"
    Set lastName = .document.getElementsByName("lastName")
    lastName.Item.innerText = "Bhattacharya"
    Set phone = .document.getElementsByName("phone")
    phone.Item.innerText = "Bhattacharya"
    Set UserName = .document.getElementsByName("userName")
    UserName.Item.innerText = "allsourav"
    Set address1 = .document.getElementsByName("address1")
    address1.Item.innerText = "123/1/1,Roy Bahadur Road"
    Set address2 = .document.getElementsByName("address2")
    address2.Item.innerText = "123/1/1,Roy Bahadur Road"
    Set city = .document.getElementsByName("city")
    city.Item.innerText = "kolkata"
     Set state = .document.getElementsByName("state")
    state.Item.innerText = "West Bengal"
     Set postalCode = .document.getElementsByName("postalCode")
    postalCode.Item.innerText = "700034"
   
    'this is for the country selection from the drop down values

     Set e = .document.getElementsByName("country")(0)
    For Each o In e.Options
        If StrComp(o.Text, "INDIA", vbTextCompare) = 0 Then
            o.Selected = True
            Exit For
        End If
    Next
    e.FireEvent ("onchange")

  
   

 
  
   
     Set email = .document.getElementsByName("email")
    email.Item.innerText = "allsourav2@gmail.com"
   
    Set password = .document.getElementsByName("password")
   
    password.Item.innerText = "123456"
   
    Set confirmPassword = .document.getElementsByName("confirmPassword")
   
    confirmPassword.Item.innerText = "123456"
   

   
    email.Item.innerText = "allsourav2@gmail.com"
   
    'now to press the search button
   
    .document.forms(0).submit
   
    'now the search results page will load,
    'to ensure the page load completes
    'we need to do perform the same steps
   
     'to make the page load so that it will not close in fraction of second
    'to make the internet explorer wait
    'it should do some events
    Do While .busy
   
    DoEvents
   
    Loop
   
    'now if the internet explorer is not fully loaded it should do some events
   
    Do While .readystate <> 4
       
    DoEvents
   
    Loop
   
   
    'now I need to get the resultset such as howmany pages google find on this phrase
   
    'to get this we need to right click on the result number at the top of the page
    'and go to inspect elements
   
    'there we see this div which shows the result has an id of
    'resultStats
   
    'Set numberofpages = .document.getelementbyid("resultStats")
   
    'to get the resultant text from numberofpages we need to use innertext
   
    'MsgBox ("The number of searches is " & numberofpages.innerText)
   
   
   
    .Quit
   
   
End With


End Sub

Serarch google for a keyword and get the resultstat automatically using vba and internet explorer,VBA Teacher Sourav,Kolkata 08910141720

Sub ienavigateandsearch()

Dim searchtxt As Object
Dim numberofpages As Object
Dim ie As Object
Set ie = CreateObject("internetexplorer.application")
With ie

    .Visible = True
    .navigate "https://www.google.com"
    'to make the page load so that it will not close in fraction of second
    'to make the internet explorer wait
    'it should do some events
    Do While .busy
   
    DoEvents
   
    Loop
   
    'now if the internet explorer is not fully loaded it should do some events
   
    Do While .readystate <> 4
       
    DoEvents
   
    Loop
   
   
    'to make this application wait for 5 seconds
    'Application.Wait (Now + TimeValue("00:00:05"))
   
    'to search google for a phrase
    'get the name of the textbox by right click on it and inspect elements
    'it is q
   
   
    'Set searchtext = .document.getelementsbyclassname("gLFyf gsfi")
    Set searchtext = .document.getElementsByName("q")
    searchtext.Item.innertext = "bbw"
   
    'now to press the search button
   
    .document.forms(0).submit
   
    'now the search results page will load,
    'to ensure the page load completes
    'we need to do perform the same steps
   
     'to make the page load so that it will not close in fraction of second
    'to make the internet explorer wait
    'it should do some events
    Do While .busy
   
    DoEvents
   
    Loop
   
    'now if the internet explorer is not fully loaded it should do some events
   
    Do While .readystate <> 4
       
    DoEvents
   
    Loop
   
   
    'now I need to get the resultset such as howmany pages google find on this phrase
   
    'to get this we need to right click on the result number at the top of the page
    'and go to inspect elements
   
    'there we see this div which shows the result has an id of
    'resultStats
   
    Set numberofpages = .document.getelementbyid("resultStats")
   
    'to get the resultant text from numberofpages we need to use innertext
   
    MsgBox ("The number of searches is " & numberofpages.innertext)
   
   
   
    .Quit
   
   
End With


End Sub

Open Internet explorer and navigate to an url and wait some seonds using VBA ,VBA Teacher Sourav,Kolkata 08910141720

Sub ienavigate()

Dim ie As Object
Set ie = CreateObject("internetexplorer.application")
With ie

    .Visible = True
    .navigate "https://www.google.com"
    'to make the page load so that it will not close in fraction of second
    'to make the internet explorer wait
    'it should do some events
    Do While .busy
   
    DoEvents
   
    Loop
   
    'now if the internet explorer is not fully loaded it should do some events
   
    Do While .readystate <> 4
       
    DoEvents
   
    Loop
   
   
    'to make this application wait for 5 seconds
    Application.Wait (Now + TimeValue("00:00:05"))
   
   
    .Quit
   
   
End With


End Sub

Extract employee id from text in combobox whenever combobox text is changed in a label,VBA Teacher Sourav,Kolkata 08910141720

Private Sub ComboBox1_Change()

If Me.ComboBox1.Text <> "" Then

If Me.OptionButton1 = True Then
Me.Label1 = Left(Me.ComboBox1.Text, Application.WorksheetFunction.Find(" -", Me.ComboBox1.Text) - 1)

'Me.Label1 = Application.WorksheetFunction.Find(" -", Me.ComboBox1.Text)
Else
'Me.Label1 = Right(Me.ComboBox1.Text, Application.WorksheetFunction.Find(" -", Me.ComboBox1.Text) + 1)
Me.Label1 = Right(Me.ComboBox1.Text, Len(Me.ComboBox1.Text) - (Application.WorksheetFunction.Find(" -", Me.ComboBox1.Text) + 2))


End If


Else

Exit Sub

End If

End Sub

Include or Exclude employee names with their employee id in a dropdown based on a criteria,VBA Teacher Sourav,Kolkata 08910141720

Sub loadcmb()
Dim sdsheet As Worksheet
Set sdsheet = ThisWorkbook.Sheets("Sortsheet")
If sdsheet.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
lr = 2
Else
lr = sdsheet.Cells(Rows.Count, 1).End(xlUp).Row
End If

SortForm.ComboBox1.Clear

If SortForm.CheckBox1 = True Then


For x = 2 To lr

If SortForm.OptionButton1 = True Then



SortForm.ComboBox1.AddItem Sortsheet.Cells(x, 1) & " - " & Sortsheet.Cells(x, 2)


Else

SortForm.ComboBox1.AddItem Sortsheet.Cells(x, 2) & " - " & Sortsheet.Cells(x, 1)

End If


Next x

Else


For x = 2 To lr

If SortForm.OptionButton1 = True Then

If UCase(Cells(x, 14)) = "A" Then

SortForm.ComboBox1.AddItem Sortsheet.Cells(x, 1) & " - " & Sortsheet.Cells(x, 2)

End If

Else

If UCase(Cells(x, 14)) = "A" Then

SortForm.ComboBox1.AddItem Sortsheet.Cells(x, 2) & " - " & Sortsheet.Cells(x, 1)

End If


End If


Next x


End If


End Sub