Monday 12 February 2018

using macro to post data from excel to a website

Private Sub SendAndSave()
Dim n As Integer
Dim min As Integer
Dim max As Integer
Dim dd As Integer
For n = 994 To 1000
    Dim IE As Object
    ' Create InternetExplorer Object
    Set IE = CreateObject("InternetExplorer.Application")
    IE.Navigate "http://example.com"
    ' You can uncoment Next line To see form results
    IE.Visible = True
   
    While IE.Busy
    DoEvents
    Wend
    Application.Wait (Now + TimeValue("00:00:02"))
    IE.document.all("gender").Item(0).Checked = True
    IE.document.all("tos").Checked = True
    IE.document.all("email").Value = ThisWorkbook.Worksheets("Sheet1").Cells(n, 1)
    IE.document.all("firstname").Value = ThisWorkbook.Worksheets("Sheet1").Cells(n, 2)
    IE.document.all("lastname").Value = ThisWorkbook.Worksheets("Sheet1").Cells(n, 3)
    IE.document.all("street_address").Value = ThisWorkbook.Worksheets("Sheet1").Cells(n, 4)
    IE.document.all("city").Value = ThisWorkbook.Worksheets("Sheet1").Cells(n, 5)
    IE.document.all("province_state").Value = ThisWorkbook.Worksheets("Sheet1").Cells(n, 6)
    IE.document.all("postal_zip").Value = ThisWorkbook.Worksheets("Sheet1").Cells(n, 7)
    ThisWorkbook.Worksheets("Sheet1").Cells(n, 12).Value = "Yes"
   
    Set oSelect = IE.document.getElementById("year")
            oSelect.Focus
            oSelect.selectedIndex = 40
            oSelect.FireEvent "onchange"
           
    Set oSelect1 = IE.document.getElementById("month")
            oSelect1.Focus
            oSelect1.selectedIndex = 3
            oSelect1.FireEvent "onchange"
  Set oSelect2 = IE.document.getElementById("day")
            oSelect2.Focus
            oSelect2.selectedIndex = 22
            oSelect2.FireEvent "onchange"
           
  Set oSelect3 = IE.document.getElementById("country")
            oSelect3.Focus
            oSelect3.selectedIndex = 1
            oSelect3.FireEvent "onchange"
           
    Set xx = IE.document.getElementsByTagName("p")
    Dim x As Integer
   
    For x = 0 To xx.Length - 1
        If xx(x).innerHTML = "Year" Then
         xx(x).innerHTML = "1979"
        ElseIf xx(x).innerHTML = "Month" Then
         xx(x).innerHTML = "3"
        ElseIf xx(x).innerHTML = "Day" Then
        xx(x).innerHTML = "22"
        ElseIf xx(x).innerHTML = "Select One" Then
        xx(x).innerHTML = "United States of America"
        End If
       
    Next x
   
    min = 7
    max = 15
    dd = Int((max - min + 1) * Rnd + min)
   
    ThisWorkbook.Worksheets("Sheet1").Cells(n, 11).Value = dd
    Application.Wait (Now + TimeValue("00:00:" & dd))
    IE.document.all("submit").Click
    Application.Wait (Now + TimeValue("00:00:05"))
    IE.Quit
    Set IE = Nothing
    Next n
End Sub