VBA IE interaction with a large form

Question

EDIT: See below for updates. See below that for "completed" code, which works successfully but uses non-best-practices.

I have a large form via a vendor portal that I am working on automating filling in with data from excel (pulling from a data warehouse, that part was easy). I'm trying to put data into the (001)(Item), (001)(GTIN), (002)(Item) and so on for all fields.

Specifically, this is the website code I'm working with:

I'm specifically trying to locate the item field within 0lineDetailheader and so on.

With some other fields/buttons on the workflow, I got the below snippets to work successfully, but not here.

Set ElementCol = IE.document.getElementsByClassName("lineDetailsHeader")
    ElementCol.Item(0).Select

With IE.document
    .all("InvoiceNbr").Value = ws.Range("C3").Value
    .all("invoiceDate").Value = ws.Range("C4").Value
    .all("shipDate").Value = ws.Range("C5").Value
End With

I also tried to use sendkeys, which is extremely inefficient, but I couldn't even get to a field :/

I suspect the solution will be apparent to somehow more versed in HTML or Java, but alas that isn't me.

EDIT: UPDATE 1 05.54 6/26/18

Thanks to the reply below, I've gotten into the field. Still unsure how to iterate between line 001, 002 etc via the index. The full code I'm using is below. I'm using sendkey's in certain areas because the web form has these red asterisk's next to it unless it registers completion, and I don't know how to trigger that with "real" code.

Public Sub WebFiller()

'Some definitions
Dim i As Long
Dim HWNDSrc As Long


'Set up workbook
Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.Sheets("Invoice")

'Open Retail Link
Dim IE As Object
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.navigate REDACTED

'Let website load
While IE.ReadyState <> 4
    DoEvents
Wend

'Input store value
With IE.document
    .all("inputStore").Value = ws.Range("C1").Value
    .all("inputStore").Focus
    .all("inputStore").Select
End With

'The section only updates once it recognizes that values have been input. This seems to get force that interaction. It is definitely not best prcatice though.
HWNDSrc = IE.HWND
SetForegroundWindow HWNDSrc
Application.SendKeys "{Tab}", True
Application.Wait (Now + TimeValue("0:00:02"))

'Finish the button clicks on the first page, giving it appropriate refresh time.
Set ElementCol = IE.document.getElementsByClassName("btn btn-primary")
    ElementCol.Item(0).Click
Application.Wait (Now + TimeValue("0:00:02"))
Set ElementCol = IE.document.getElementsByClassName("btn btn-primary pull-right")
    ElementCol.Item(0).Click
Application.Wait (Now + TimeValue("0:00:02"))

'Let website load
While IE.ReadyState <> 4
    DoEvents
Wend

'Fill in the info at the top of the page
HWNDSrc = IE.HWND
SetForegroundWindow HWNDSrc
With IE.document
    .all("InvoiceNbr").Value = ws.Range("C3").Value
    .all("invoiceDate").Value = ws.Range("C4").Value
    .all("shipDate").Value = ws.Range("C5").Value
    .all("InvoiceNbr").Select
End With
Application.SendKeys "{Tab}", True
Application.Wait (Now + TimeValue("0:00:02"))

'Add the necessary number of rows
For i = 1 To ws.Range("C7").Value - 1
Set ElementCol = IE.document.getElementsByClassName("fa fa-plus fa-lg")
    ElementCol.Item(0).Click
Next i

'start first line "Index 0"
With IE.document
    .querySelector("input[title='Item']").Value = ws.Range("B12").Value
    .querySelector("input[title='GTIN']").Value = ws.Range("C12").Value
    .querySelector("input[title='Invoice Quantity']").Value = ws.Range("E12").Value
    .querySelector("input[title='Item Cost']").Value = ws.Range("G12").Value
    .querySelector("input[title='Item Cost']").FireEvent "onkeypress"
End With

'start second line "Index 1"
With IE.document
    .querySelector("input[title='Item']").Value = ws.Range("B15").Value
    'etc etc but this doens't work
End With
End Sub

EDIT 7.16.18 (last update): Here is the full code work. It connects through an OLAP cube to some pivot tables, so if you are trying to copy this you will have to probably change how you interact with the slicers.

The pivot table has this code on it:

Private Sub Worksheet_PivotTableUpdate _
    (ByVal Target As PivotTable)
    ' first remove filter
    Sheets("Invoice").Range("$E$11:$E$43").AutoFilter Field:=1
    ' then apply it again
    Sheets("Invoice").Range("$E$11:$E$43").AutoFilter Field:=1, Criteria1:="<>0"
End Sub

This creates a visual filter on a pre-formatted page, to emulate "invoice" creation, if manual input had to be done. This is a great way to apply special filters to lists, if you are using column/row, index/match/match, vlookup/hlookup type functions.

The primary invoice tab has this code. The vendor's portal has a list of submitted documents, so I inserted this checklist/validation sheets to create a workflow. Given a list of invoices to "review", the macro cycles through them, checks if it's been submitted, that the invoice totals as expected, and that it isn't a credit invoice, which needs to be handled seperately. It averages about 75sec/invoice, down from 8 minutes or so for the associate who was doing it. I'm pretty happy with that, even if (as mentioned above), I kept using sendkeys which is definitely not best-practice.

The code is labeled pretty well, but let me know if any of my logic is unclear.

Private Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal HWND As LongPtr) As LongPtr


Public Sub InvoiceFiller()
'Purpose: To expedite WebEDI experience. Manual input takes too long.

'Some definitions
Dim i, r As Long
Dim lRow1, lRow2 As Long
Dim c As Range
Dim HWNDSrc As Long 'had to use sendkeys, couldn't figure out how else to trigger certain parts
Dim ws As Worksheet 'this is the invoice worksheet
Dim cs As Worksheet 'this is the checklist worksheet
Dim vs As Worksheet 'this is the validation against retail link's database
Dim cm As Worksheet 'this is the main cube report. All slicers affect both cubes
Dim wb As Workbook
Dim IE As Object
Dim SliceArr As Variant
Dim SliceVal As Variant

'Set up workbook shortcuts
Set wb = ThisWorkbook
Set ws = wb.Sheets("Invoice")
Set cs = wb.Sheets("Checklist")
Set vs = wb.Sheets("Validation")
Set cm = wb.Sheets("CUBE_MAIN")

''''''''''''''''''''''''''''''''''''''
'Start of Checklist component
'This sets up the ability to loop a range of invoices, referencing against the validation tab

'Copy tickets to the checklist page
lRow1 = cm.Cells(Rows.Count, 2).End(xlUp).Row - 1
lRow2 = cs.Cells(Rows.Count, 1).End(xlUp).Row

'First copy the tickets
cm.Range(cm.Cells(8, 1), cm.Cells(lRow1, 1)).Copy
cs.Range(cs.Cells(lRow2 + 1, 1), cs.Cells(lRow2 + 1 + lRow1 - 8, 1)).PasteSpecial xlPasteValues
'Next copy the dates
cm.Range(cm.Cells(8, 4), cm.Cells(lRow1, 4)).Copy
cs.Range(cs.Cells(lRow2 + 1, 2), cs.Cells(lRow2 + 1 + lRow1 - 8, 2)).PasteSpecial xlPasteValues
'Then copy the stores
cm.Range(cm.Cells(8, 3), cm.Cells(lRow1, 3)).Copy
cs.Range(cs.Cells(lRow2 + 1, 3), cs.Cells(lRow2 + 1 + lRow1 - 8, 3)).PasteSpecial xlPasteValues

'Trim the store data
For Each c In cs.Range(cs.Cells(lRow2 + 1, 3), cs.Cells(lRow2 + 1 + lRow1 - 8, 3))
    c.Value = Right(c.Value, 4)
Next c
'Apply the vlookup
For Each c In cs.Range(cs.Cells(lRow2 + 1, 4), cs.Cells(lRow2 + 1 + lRow1 - 8, 4))
    c.Formula = "=+VLOOKUP(C" & c.Row & ",'Walmart Table'!A:B,2,FALSE)"
Next c
ws.Activate

''''''''''''''''''''''''''''''''''''''
'Start of Slicer Looping component

For r = lRow2 + 1 To lRow2 + 1 + lRow1 - 8
wb.SlicerCaches("Slicer_Ticket_Number").VisibleSlicerItemsList = Array("[Sales].[Ticket Number].&[" & cs.Range("A" & r).Value & "]")
Application.Wait (Now + TimeValue("0:00:01")) 'This is mainly for visual satisfaction.

'Run some qualifiers before uploading
If ws.Range("D3").Value = "Does not tie-out" Then cs.Range("E" & r).Value = ws.Range("D3").Value
If ws.Range("D3").Value = "Credit memo" Then cs.Range("E" & r).Value = ws.Range("D3").Value
If ws.Range("D3").Value = "Already in WebEDI" Then cs.Range("E" & r).Value = ws.Range("D3").Value

'If no reason not to, then go ahead an upload
If ws.Range("D3").Value = "Okay to upload" Then

''''''''''''''''''''''''''''''''''''''
'Start of WebEDI component

'Open website
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.navigate ***OMMITTED***

'Let website load
While IE.ReadyState <> 4
    DoEvents
Wend

'Input store value
With IE.document
    .all("inputStore").Value = ws.Range("C1").Value
    .all("inputStore").Focus
    .all("inputStore").Select
End With

'The section only updates once it recognizes that values have been input. This seems to get force that interaction. It is definitely not best prcatice though.
HWNDSrc = IE.HWND
SetForegroundWindow HWNDSrc
Application.SendKeys "{Tab}", True
Application.Wait (Now + TimeValue("0:00:02"))

'Finish the button clicks on the first page, giving it appropriate refresh time.
Set ElementCol = IE.document.getElementsByClassName("btn btn-primary")
    ElementCol.Item(0).Click
Application.Wait (Now + TimeValue("0:00:02"))
Set ElementCol = IE.document.getElementsByClassName("btn btn-primary pull-right")
    ElementCol.Item(0).Click
Application.Wait (Now + TimeValue("0:00:02"))

'Let website load
While IE.ReadyState <> 4
    DoEvents
Wend

'Give IE a chance to un-stuck
Application.Wait (Now + TimeValue("0:00:03"))

'Fill in the info at the top of the page
HWNDSrc = IE.HWND
SetForegroundWindow HWNDSrc
With IE.document
    .all("InvoiceNbr").Value = ws.Range("C3").Value
    .all("invoiceDate").Value = ws.Range("C4").Value
    .all("shipDate").Value = ws.Range("C5").Value
    .all("InvoiceNbr").Select
End With
Application.SendKeys "{Tab}", True
Application.Wait (Now + TimeValue("0:00:02"))

'Add the necessary number of rows
For i = 1 To ws.Range("C7").Value - 1
Set ElementCol = IE.document.getElementsByClassName("fa fa-plus fa-lg")
    ElementCol.Item(0).Click
Next i

With IE.document
    .querySelector("input[title='Item']").Value = 0
    .querySelector("input[title='Item']").Select
End With

For i = 12 To 43
    If ws.Range("B" & i).EntireRow.Hidden = False Then
    Application.SendKeys ws.Range("B" & i).Value, True
    Application.SendKeys "{Tab}", True
    Application.Wait (Now + TimeValue("0:00:01"))
    Application.SendKeys ws.Range("C" & i).Value, True
    Application.SendKeys "{Tab}", True
    Application.SendKeys "{Tab}", True
    Application.Wait (Now + TimeValue("0:00:01"))
    Application.SendKeys ws.Range("E" & i).Value, True
    Application.SendKeys "{Tab}", True
    Application.SendKeys "{Tab}", True
    Application.Wait (Now + TimeValue("0:00:01"))
    Application.SendKeys ws.Range("G" & i).Value, True
    Application.SendKeys "{Tab}", True
    Application.SendKeys "{Tab}", True
    Application.Wait (Now + TimeValue("0:00:01"))
    End If
Next i

'Submit Invoice
Set ElementCol = IE.document.getElementsByClassName("fa fa-arrow-up fa-lg")
    ElementCol.Item(0).Click

'Give IE a chance to un-stuck
Application.Wait (Now + TimeValue("0:00:01"))

'Let website load
While IE.ReadyState <> 4
    DoEvents
Wend

'Give IE a chance to un-stuck
Application.Wait (Now + TimeValue("0:00:05"))

'Close IE
IE.Quit
Set IE = Nothing

'End of WebEDI component
''''''''''''''''''''''''''''''''''''''

cs.Range("E" & r).Value = "Uploaded!"

'Go to next ticket and repeat the evaluation sequence
End If
Next r

'End of Slicer Looping component
''''''''''''''''''''''''''''''''''''''

End Sub

QHarr · Accepted Answer

General observation:

I am a little cautious on advising on this based solely on what is provided above. It feels like there is too much I can't see. I am working on the assumption that you can't share an URL.

So, is there something you press and numbers are entered and it moves to the next line or does the HTML repeat itself? I note that the above larger HTML section has input tagged elements, but only 1 for each column, and the entire section is line index 1, which I assume is top row (text: lineNumber($index())">001) –

Starter for 10:

As a starter for 10 on selecting the top line elements you can use CSS selectors for Item, GTIN, Stock, Invoice qty, Selling Unit,Item Cost:

.document.querySelector("input[title='Item']")
.document.querySelector("input[title='GTIN']")
.document.querySelector("#VndrStk")
.document.querySelector("input[title='Invoice Quantity']")
.document.querySelector("input[title='Selling Unit']")
.document.querySelector("input[title='Item Cost']")

.querySelector is a method of document and applies the CSS selector inside the "".

If these items were repeated you could use the .querySelectorAll method to return a nodeList of elements with matching CSS patterns and then access items from that nodeList by index. Similar to how you do it with the collection returned by .getElementsByClassName, for example, except you cannot use a For Each Loop to traverse, but instead traverse its .Length.

onkeypress event

The elements appear to have associated onkeypress events.

So you may need to simulate those events after setting a value e.g.

.document.querySelector("input[title='Item']").Value = 10 
.document.querySelector("input[title='Item']").FireEvent "onkeypress"

You may additionally need to use .Focus on the element before attempting an assigment as well.

Some CSS selector examples explained:

input[title='Item']

This says element with input tag that has an attribute title whose value is 'item'. The [] means attribute.

#VndrStk

This says element with id VndrStk. # means id.

.querySelectorAll and nodeList:

Multiple elements use .querySelectorAll method and syntax might be:

.querySelectorAll("input[title='Item']").item(1).Value = ws.Range("B15").Value

or

.querySelectorAll("input[title='Item']")(1).Value = ws.Range("B15").Value

Using example of index 1. I can't tell from the HTML above if this is applicable.

VBA IE interaction with a large form

Answers (1)

Related Questions