You can use the VBA library "Microsoft Internet Controls" and "Microsoft HTML Object Library" and fully automate web page interaction...
Below is a macro to do it... To enter the macro code, you can use Alt+F11 to enter the Microsoft Visual Basic for Applications environment.
- Add the library References (under the Tools>References menu)
- Add a code module (under Insert>Module)
- Paste the code below... modify they url
The code will open IE then go to the web page and wait for you to hit OK (after you have done whatever you need.. maybe logging in, navigating somewhere, entering the number of rows, ...). Then it will loop through all the columns you have used in your spreadsheet (using Find to populate the numCols) and drop the cell values into the HTML elements with the relevant IDs. It won't hit Add Employees for you, just in case something went wrong, but the library does give you the ability to .Click() on things.
I've used this for a few automation hacks and it seems quite reliable... Difficult parts can be waiting for page loads/updates - but you shouldn't have that problem.
'References needed: "Microsoft Internet Controls" and "Microsoft HTML Object Library"
' (add under menu Tools > References)
Sub populate()
Dim ws As Worksheet
Set ws = Application.ActiveSheet
Dim appIE As InternetExplorer
Set appIE = New InternetExplorer
appIE.Visible = True
appIE.Navigate "http://localhost:8080/your_form"
If vbCancel = MsgBox("Make sure the page has loaded, enter the number of ids etc. etc. Then click OK", vbOKCancel) Then
Set appIE = Nothing
Exit Sub
End If
Dim counter As Integer, index As Integer, numCols As Integer
numCols = ws.Cells.Find(What:="*", After:=[A1], LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column
For counter = 1 To numCols
'A1 = id='firstname0'
'A2 = id='middleinitial0'
'A3 = id='lastname0'
'A6 = id='ntlogin0'
index = counter - 1
appIE.document.getElementById("firstname" & index).Value = ws.Cells(1, counter)
appIE.document.getElementById("middleinitial" & index).Value = ws.Cells(2, counter)
appIE.document.getElementById("lastname" & index).Value = ws.Cells(3, counter)
appIE.document.getElementById("ntlogin" & index).Value = ws.Cells(6, counter)
Next counter
Set appIE = Nothing
Set ws = Nothing
MsgBox "All done - hit Submit if all OK!"
End Sub
VERSION FOR VALUES IN ROWS
'References needed: "Microsoft Internet Controls" and "Microsoft HTML Object Library"
' (add under menu Tools > References)
Sub populate()
Dim ws As Worksheet
Set ws = Application.ActiveSheet
Dim appIE As InternetExplorer
Set appIE = New InternetExplorer
appIE.Visible = True
appIE.Navigate "http://localhost:8080/your_form"
If vbCancel = MsgBox("Make sure the page has loaded, enter the number of ids etc. etc. Then click OK", vbOKCancel) Then
Set appIE = Nothing
Exit Sub
End If
Dim counter As Integer, index As Integer, numRows As Integer
numRows = ws.Cells.Find(What:="*", After:=[A1], LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For counter = 1 To numRows
'A1 = id='firstname0'
'B1 = id='middleinitial0'
'C1 = id='lastname0'
'D1 = id='ntlogin0'
index = counter - 1
appIE.document.getElementById("firstname" & index).Value = ws.Cells(counter,1)
appIE.document.getElementById("middleinitial" & index).Value = ws.Cells(counter,2)
appIE.document.getElementById("lastname" & index).Value = ws.Cells(counter,3)
appIE.document.getElementById("ntlogin" & index).Value = ws.Cells(counter,4)
Next counter
Set appIE = Nothing
Set ws = Nothing
MsgBox "All done - hit Submit if all OK!"
End Sub
