Define loop in a dynamic range? Auto fill web user forms using vba

[42Nice]

Hi,

My goal is to Fill web forms using dynamic data which is stored in excel table. The proccess should look like the below:

1. Go to link stored in "A"i
2. Fill the given fields in web form using data from "A"i to "G"i
3. Click "save"
4. Wait 5 seconds
5. Populate column "H"i with text: "Created"
6. Repeat next line (i+1) until last row
7. When finished(after last row), msgbox: "proccess completed"

Below is my version without a loop that works but only for B2:G2 Row.
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">Sub copy_project_loop()

Dim IE AsObject
Dim Doc As HTMLDocument
Set IE = CreateObject("InternetExplorer.Application")
Dim SHELL_OBJECT
SHELL_OBJECT ="WScript.Shell"
Set objShell = CreateObject(SHELL_OBJECT)


IE.Visible =True
IE.navigate "URL link"

DoWhile IE.Busy
Application.Wait DateAdd("s",1, Now)
Loop
Set Doc = IE.document

Doc.getElementById("Project|Name").Value = ThisWorkbook.Sheets("data").Range("B2").Value
Doc.getElementById("customer|UserDefinedIdTA").Value = ThisWorkbook.Sheets("data").Range("C2").Value
Doc.getElementById("customer|UserDefinedIdTA").Focus
objShell.SendKeys "{Enter}"
Application.Wait DateAdd("s",2, Now)
Doc.getElementById("selEngagement").Value = ThisWorkbook.Sheets("data").Range("D2").Value
Doc.getElementById("txtPlannedStart").Value = ThisWorkbook.Sheets("data").Range("E2").Value
Doc.getElementById("Project|ProjTimeAppTA").Value = ThisWorkbook.Sheets("data").Range("F2").Value
Doc.getElementById("Project|SecondProjTimeAppTA").Value = ThisWorkbook.Sheets("data").Range("G2").Value
Application.Wait DateAdd("s",5, Now)
Doc.getElementById("Button1").Click

EndSub</code>

I find it difficult to understand hot Can I set a loop to go trough this dynamic range, do all the actions, and repeat by going to next link in the next row unntill the last row of the data.
Please help me undestand the proper syntax I should use for my goal.

Thanks,

 

[Kamolga]

Re: Help: How to define loop in a dynamic range? Auto fill web user forms using vba

To loop through rows, you need to define first row num and last (code below is 2 to last cell in column A for exemple)

Dim sht as worksheet
Set sht = [COLOR=#303336][FONT=Consolas][FONT=inherit]ThisWorkbook[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Sheets[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]"data"[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit])
[/FONT][/FONT][/COLOR]Dim LastRow as long
Dim i as long
[COLOR=#3D3D3D][FONT='inherit']LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

[/FONT][/COLOR]For i = 2 to LastRow
   [COLOR=#303336][FONT=Consolas][FONT=inherit]Doc[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]getElementById[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]"Project|Name"[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]).[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Value [/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]=[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] sht[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Range[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]"B" & i[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]).[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Value[/FONT][/FONT][/COLOR]
   '....
   [COLOR=#303336][FONT=Consolas][FONT=inherit]Doc[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]getElementById[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]"Button1"[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]).[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Click
[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]  Application[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit].[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]Wait DateAdd[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit]([/FONT][/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas][FONT=inherit]"s"[/FONT][/FONT][/COLOR][FONT=Consolas][FONT=inherit][COLOR=#303336],[/COLOR][COLOR=#7d2727]5[/COLOR][/FONT][/FONT][COLOR=#303336][FONT=Consolas][FONT=inherit],[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit] Now[/FONT][/FONT][/COLOR][COLOR=#303336][FONT=Consolas][FONT=inherit])
[/FONT][/FONT][/COLOR]   sht.Range("H" & i).value = "Created"
Next i
MsgBox "Process 100% completed"

 

[42Nice]

Re: Help: How to define loop in a dynamic range? Auto fill web user forms using vba

Hi Kamaloga,

Thank you so much for your reply! IT WORKED.


Few things to that I need to polish:

1) When I reach the final row and do all the steps, I recieve the following pop up message from the web:

The webpage you are viewing is trying to close the tab. Do you want to close this tab?

Is there a command that can be used before or after the "next i" to close IE? Every new "i" I open new window because after I click "save" button I need to enter to the same url.

2) Is there "healtier" way to define waiting time for population data into WEB elemnts or opening / closing IE?

3) is there any extra commands I should add to avoid possible errors?

The new "polished" module looks like that: ("I have changed few cell positions, added your suggested loop and told IE to navigate to a dynamic rage within the range [link is appearing in cell H)

Sub DynamicWebPage()

' here I define elemnts for the loop
Dim sht As Worksheet
Set sht = ThisWorkbook.Sheets("data")
Dim LastRow As Long
Dim i As Long
LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row


' here I define Internet Explorer
Dim IE As Object
Dim Doc As HTMLDocument
Set IE = CreateObject("InternetExplorer.Application")


' here I define Object to sendkeys
Dim SHELL_OBJECT
SHELL_OBJECT = "WScript.Shell"
Set objShell = CreateObject(SHELL_OBJECT)


' here I define range for the loop
For i = 2 To LastRow


' here I ask the Internet explorer to be visable & Navigate to value in cell "H"i
IE.Visible = True
IE.navigate sht.Range("H" & i).Value


' here I ask the Internet explorer to wait few seconds
Do While IE.Busy
    Application.Wait DateAdd("s", 5, Now)
Loop


Set Doc = IE.document


'******* From here the macro fill the data from excel table in range into WEB elements******
Doc.getElementById("Project|Name").Value = sht.Range("A" & i).Value
Doc.getElementById("customer|UserDefinedIdTA").Value = sht.Range("B" & i).Value
Application.Wait DateAdd("s", 1, Now)
Doc.getElementById("customer|UserDefinedIdTA").Focus
objShell.SendKeys "{Enter}"
Application.Wait DateAdd("s", 5, Now)
Doc.getElementById("selEngagement").Value = sht.Range("C" & i).Value
'date format should be m/d/yyyy
Doc.getElementById("txtPlannedStart").Value = sht.Range("D" & i).Value
Doc.getElementById("Project|ProjTimeAppTA").Value = sht.Range("E" & i).Value
Doc.getElementById("Project|SecondProjTimeAppTA").Value = sht.Range("F" & i).Value
Application.Wait DateAdd("s", 5, Now)
Doc.getElementById("Button1").Click


' here I ask to populate column "G"i with "created" string to know that this raw was successfully done
sht.Range("G" & i).Value = "Created"






Next i
MsgBox "Process 100% completed"


End Sub

I will continue testing the outcome, and try to get more solid understanding in loops. Thank you again reaaly, I truely appriciate your assistance!

 

[Kamolga]

Re: Help: How to define loop in a dynamic range? Auto fill web user forms using vba

In vba we normally use

Application.DisplayAlerts=False

at the beginning of the code and put it back to true at the end. We also do that with screenupdating to increase execution speed.

Sub 123()
Application.ScreenUpdating = False
Application.DisplayAlerts = False

[COLOR=#008000]'Your code[/COLOR]

Application.DisplayAlerts = True
Application.ScreenUpdating = True
End sub

Does it workwork?

otherwise

[COLOR=#303336][FONT=inherit]IE[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Quit[/FONT][/COLOR]

at the end of the loop and reopen it at its begining just after for i=2 to lastrow

 

[42Nice]

Re: Help: How to define loop in a dynamic range? Auto fill web user forms using vba

Dear Kamolga,

Thanks again for your assistance. I must say it works and do wonders.
I will continue to look for ways to improve my approach and I truely appriciate your guidence.



With best regards,
42Nice