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

Randomly choose between two given characters (for example A and I) and format the row based on that choice using excel,Excel Teacher Sourav,Kolkata 08910141720

=CHAR(CHOOSE(RANDBETWEEN(1,2),65,73))

Or

=CHOOSE(RANDBETWEEN(1,2),"A","I")

Source:https://exceljet.net/formula/random-text-values

Now select the whole section of data ,go to home,conditional formatting ,manage rules,new rule

select use a formula to determine which cells to format

in the formula box write

=$N2="I"

then go to format,under fill tab make sure grey is selected,so that people with
status I (inactive ) have their rows greyed out

Load Combobox with Employee ID and Names on initialization of userform ,VBA Teacher Sourav,Kolkata 08910141720

Private Sub UserForm_Initialize()
Call loadcmb
End Sub


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

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

End Sub

Create random but unique numbers for employee id column in a given range using VBA,VBA Teacher Sourav,Kolkata 08910141720

Sub fillcolumnwithuniquerandomnumbers()

Sheets("Sortsheet").Select
 Dim cell As Range
    Dim rng As Range
    Dim High As Long, Sample As Long
   
  
    'High = Application.InputBox("Enter population total", Type:=1)
    'Sample = Application.InputBox("Enter the Sample Size", Type:=1)
    High = 500
    Low = 1
   
    'Set rng = Application.Range(ActiveCell, ActiveCell.Offset(Sample, 0))
    Set rng = Range("A2:A500")
   
    For Each cell In rng.Cells
        If WorksheetFunction.CountA(rng) = (High - Low + 1) Then Exit For
        Do
            rndNumber = Int((High - Low + 1) * Rnd() + Low)
        Loop Until rng.Cells.Find(rndNumber, LookIn:=xlValues, lookat:=xlWhole) Is Nothing
        cell.Value = rndNumber
    Next
    rng.Select
    Selection.NumberFormat = "@" 'changing the format to text so that cells can contain leading zeros
   
    For Each cell In rng.Cells
    cell.Value = "000" & cell.Value
   
   
   
    Next
   
   
   
End Sub

Sort excel data from an userform using column names(sort key) in option button using VBA,VBA Teacher Sourav,Kolkata 08910141720

The code behind sort button

Private Sub CommandButton1_Click()

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

Set sortarea = Range("A2:L" & lr)
If Me.OptionButton1 = True Then

Set sortcol = Range("A2:A" & lr)
ElseIf Me.OptionButton2 = True Then
Set sortcol = Range("G2:G" & lr)

Else

End If






sdsheet.Sort.SortFields.Clear
sdsheet.Sort.SortFields.Add Key:=sortcol _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With sdsheet.Sort
    .SetRange sortarea
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
End Sub


Source:https://www.youtube.com/watch?v=6Ju6B99eleo&index=2&list=PLw8O1w0Hv2zvnLFyiMrihcaOqA0sT0X2U