CODEWITHSUNDEEP
excelvbacheckboxclick
Repmat
Repmat

Reputation: 690

Click all checkboxes on website

I have (in Excel, VBA) a sub that will navigate to this webpage:

http://www.nordea.dk/wemapp/currency/dk/valutaKurser

And copy the quoted rates. However, I would like to remove all the rates, and only add the ones my sub needs.

To do this, I need to check all of the boxes (to the left in the table), but the number of boxes is not constant - so I can't hardcode the boxnames.

I suppose one way to this is to extract the entire html, determine the number of rows, and then loop. But it seems very unhandy. Surely there is some smatter way, which requires less code and less storage?

Upvotes: 0

Views: 399

Answers (1)

user3819867
user3819867

Reputation: 1120

You can Split() the table by vbNewLine and search for the currency in each row of the set (mind the headers).
Since the inputs are named like table:body:rows:2:cells:1:cell:check you can match the currency with the checkbox.
In practice it looks like (up to getting the element names):

Function FirstRow(myArray() As String, Optional curr As String) As Long
If curr = "" Then
    curr = "*[A-Z][A-Z][A-Z]/[A-Z][A-Z][A-Z]*"
Else
    curr = "*" & curr & "*"
End If

For i = LBound(myArray) To UBound(myArray)
    If myArray(i) Like curr Then 'FX denoted as "XXX/XXX"
        FirstRow = i
        Exit Function
    End If
Next i
End Function
Sub ert()
Dim myArray() As String, URLStr As String
URLStr = "http://www.nordea.dk/wemapp/currency/dk/valutaKurser"

Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = False
ie.Navigate URLStr

Do Until (ie.readyState = 4 And Not ie.Busy)
    DoEvents
Loop

On Error GoTo Err:
Data = ie.Document.body.innerHTML 'innerText

If False Then
Err:
    Data = ie.Document.body.innerHTML
End If

myArray = Split(Data, "<tr>") 'vbNewLine) 'vbCrLf)

'For i = LBound(myArray) To UBound(myArray)
'    Cells(i + 1, 1).Value2 = myArray(i)
'Next

Dim curr() As String
ReDim curr(2)
    curr(0) = "DKK/NOK"
    curr(1) = "EUR/SEK"
    curr(2) = "USD/CHF"
For i = LBound(curr) To UBound(curr)
    x = FirstRow(myArray, curr(i)) - FirstRow(myArray) + 1
    MsgBox "table:body:rows:" & x & ":cells:1:cell:check"
Next i
ie.Quit
Set ie = Nothing
End Sub

I'm sorry, the vbNewLine won't cut it this time, my bad. Also, checking a named element shouldn't be that hard, had you provided your snipplet for checking all the boxes I would have even given it a shot.

Upvotes: 1

Related Questions