VBA to automate web-scrape entering cells

Question

I have a list of numbers in excel, and I want to automate the process of entering them into a web-page one by one.

The code I currently have logs onto this website, enters username info and password. Presses enter. Then goes to the next screen, where I have it clicking initiate.

Next, it goes to the next screen, where the program clicks the history button. It then goes back to excel (to column A) and takes the first number out and enters it where it says "Number Here", and clicks enter. This brings me to a page with a ton of info that I then, copy and paste back into excel.

Again, I have the program running for these factors.

However, I believe my code should be moving onto the next number in the column (ie, doing the above steps first for cell A3, then for cell A4 etc), but it is not.

Below is my code:

Option Explicit

Sub _NumberFix()

Dim IE As Object
Dim IeDoc As Object
Dim aInput As Object
Dim eInput As Object
Dim svalue1 As Object
Dim elems As Object
Dim t As Date
Dim i As Long, lastrow As Long
Dim ws As Worksheet, wks As Excel.Worksheet
Dim NewName As String
Dim sheet As Worksheet
Dim duplicate As Boolean

Const MAXWAIT_sec As Long = 10

Set ws = Sheets("VALUES")

Set IE = CreateObject("InternetExplorer.application")
IE.Visible = True
IE.Navigate ("http://mywebsite.com/")

    Do While IE.busy: DoEvents: Loop

Set IeDoc = IE.document

'Enters username and password
    With IeDoc.all
        .UserName.Value = "userr"
        .Password.Value = "password"
    End With

    With IE.document.forms("signingin")
        .document.forms(0).submit
    End With


Set IeDoc = IE.document ' set new page source

    t = Timer

    Do
        On Error Resume Next
        Set elems = IeDoc.queryselector("input[value=Initiate]")
        On Error GoTo 0
        If Timer - t > MAXWAIT_sec Then
            Exit Do
        End If
    Loop While elems Is Nothing

    If Not elems Is Nothing Then
        elems.Item.Click
    End If



    IeDoc.getElementByID("checkConf").Click


    For Each aInput In IeDoc.getElementsbyTagName("input")
        If aInput.getAttribute("value") = "Request" Then
            aInput.Click
            Exit For
        End If
    Next aInput


    Do While IE.busy: DoEvents: Loop

    'Selects historical
    For Each aInput In IeDoc.getElementsbyTagName("input")
        If aInput.getAttribute("value") = "History" Then
            aInput.Click
            Exit For
        End If
    Next aInput


    lastrow = ws.Cells(ws.rows.Count, "A").End(xlUp).Row
    IE.Visible = True

    For i = 3 To lastrow

    Set IeDoc = IE.document ' set new page source

        Set svalue1 = IeDoc.getElementByID("Number")
        svalue1.Value = ws.Cells(i, 1).Value 'takes the  number out and enters
            'presses submit once numb is entered
            For Each aInput In IeDoc.getElementsbyTagName("input")
                If aInput.getAttribute("value") = "Submit" Then
                    aInput.Click
                    Exit For
                End If
            Next aInput

        IE.Navigate ("https://mywebsite.com/")
        Do While (IE.READYSTATE <> 4 Or IE.busy <> False): DoEvents: Loop
        IE.Visible = True
        Exit For


Next i
        Do While (IE.READYSTATE <> 4 Or IE.busy <> False): DoEvents: Loop
        IE.Visible = True


End Sub

*"However, my code should be moving onto the next number in the column, but it is not."* - what do you mean it's not? Something that immediately stands out to me is that you're using `i` in `For i = 3 to lastrow`, but *within* that loop you're incrementing it as well (`i = i + 1`). I would expect that to skip every other row. — dwirony, Jul 02 '19 at 20:37
Indenting your code properly will go a long way to helping spot what is going on. Rather than posting a code review as an answer, here is a pastebin of some simple changes to consider (not all encompassing but just a quick few thoughts) - https://pastebin.com/V3Rm8vDq The point about incrementing _i_ , which is already incremented by virtue of being the loop variable, is covered above. Notes on avoiding select [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — QHarr, Jul 02 '19 at 21:40
@dwirony, It doesnt even skip rows or anything, it just doesn't enter any of the values into the webpage other than the first. — vba_user111, Jul 03 '19 at 16:00
@QHarr thanks this definitely does help. i think when i copy it from VBA into this website i get it messed up sometimes... — vba_user111, Jul 03 '19 at 16:01
@vba_user111 At the end of your last loop you `Exit For`, which leads you right to `End Sub` - where are you expecting this to go? — dwirony, Jul 03 '19 at 16:05
I was expecting it to navigate back to the webpage, then bascially go back to the top of the code. Ie; go to the web page, click the buttons again, go back to my list of numbers and enter the 2nd one and do this for the whole list of the numbers. I just now changed the 'exit for' and 'next i' to be before the navigate part, but still nothing happens. — vba_user111, Jul 03 '19 at 16:19
@dwirony when I do that it says "compile error: end with without with" — vba_user111, Jul 03 '19 at 17:19

score 2 · Answer 1 · answered Jul 03 '19 at 17:32

2

I took a shot at cleaning this up - try the below code.

Your question is hard to answer because your code is not only hard to follow, but indented very poorly, and your variables are ambiguous. You also declare variables that never appear to be used.

Option Explicit
Public Const UBlim As Long = 6
Sub Login()

    Dim IE As Object
    Dim eInput As Object
    Dim ws As Worksheet: Set ws = Sheets("VALUES")
    Dim i As Long
    Dim j As Long
    Dim lastrow As Long
    Dim svalue1 As Object
    Dim results As Variant
    Dim wkscnt As Long
    Dim wks As Excel.Worksheet
    Dim wkshtnames()
    Dim a As Object
    Dim b As Object
    Dim t As Date
    Dim elems As Object
    Const MAXWAIT_sec As Long = 10

    Set IE = CreateObject("InternetExplorer.application")
    IE.Visible = True
    IE.Navigate ("http://mywebsite.com/")

    With IE

        Do
            If IE.readystate = 4 Then
                Exit Do
            Else
                DoEvents
            End If
        Loop

        'Enters username and password

        With .document
            .forms("signingin").UserName.Value = "userr"
            .forms("signingin").Password.Value = "password"
            .forms("signingin").document.forms(0).submit

            'this clicks a button after logging in that says initiate new request
            t = Timer

            Do
                On Error Resume Next
                Set elems = .document.queryselectorall("input[value=Initiate]")
                On Error GoTo 0

                If Timer - t > MAXWAIT_sec Then
                    Exit Do
                End If

            Loop While elems Is Nothing

            If Not elems Is Nothing Then
                elems.Item.Click
            End If

            Application.Calculation = xlCalculationManual
            Application.CutCopyMode = False
            wkscnt = ThisWorkbook.Sheets.Count
            j = 0

            For Each wks In ActiveWorkbook.Worksheets

                j = j + 1

                If j > UBlim Then
                    ReDim Preserve wkshtnames(7 To wkscnt)
                    wkshtnames(j) = wks.Name
                End If

            Next wks

            If wkscnt > UBlim Then
                Application.DisplayAlerts = False
                Sheets(wkshtnames).Delete
                Application.DisplayAlerts = True
            End If

            lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

            For i = 3 To lastrow
                Set svalue1 = .getElementbyID("Number")
                svalue1.Value = ws.Cells(i, 1).Value
                i = i + 1

                For Each eInput In .getElementsbyTagName("input")
                    If eInput.getAttribute("value") = "Submit Request" Then
                        eInput.Click
                        Exit For
                    End If
                Next eInput

                IE.Visible = True

                'copy and pasting the info from the web page to a new excel sheet
                Sheets("Sheet4").Range("A1:Z100").ClearContents
                IE.ExecWB 17, 0 '//select
                IE.ExecWB 12, 2 '//Copy Selection
                ActiveSheet.Paste

                Sheets("Sheet4").Range("A3:Q32").Copy

                'Creates a new sheet after & pastes content into it, formats
                Sheets.Add After:=ActiveSheet
                ActiveSheet.Paste
                Selection.Columns.AutoFit
                Selection.Rows.AutoFit

                ActiveSheet.Protect
                'this navigates back to the page where I need to enter the value in the excel column again

                IE.Navigate ("https://mywebsite.com/Default")
            Next i

        End With

    End With

End Sub

answered Jul 03 '19 at 17:32

dwirony

5,487
3
21
43

This looks good! I just updated my code. So right now it works, but it doesnt click through the buttons again. It just navigates to that page (possibly "enters" the account number- but theres no spot on this page to enter anytinhg so it looks like nothing happens), and high lights the page and tries to copy it into sheet4. Is there anyway to get it to click through the buttons again? – vba_user111 Jul 03 '19 at 17:48
@vba_user111 Do you have to sign in again each time? – dwirony Jul 03 '19 at 18:43
nope, the user is still logged in, but the buttons have to be click through again( to initiate it ) – vba_user111 Jul 08 '19 at 12:30
@vba_user111 Since I don't have a visual I'm trying to figure out where you're going back to when you hit the line `IE.Navigate ("https://mywebsite.com/Default")` near the end - I thought going back to the top of the loop would suffice. – dwirony Jul 08 '19 at 14:59
so the web-page starts with having to log in, then once u enter username/password and submit it brings you to the "default" page, where you need to click the initiate button. once you click the initiate, the checkbox button pops up, once you click that, the review button pops up and it loads. Then it goes to the page where you can click the historical button and input the number. So essentially, I have my code doing all this and entering the first number (copy/pasting etc.) but as it stands right now, it just does it for the first number, then goes back to that site. – vba_user111 Jul 08 '19 at 15:12
however, now with your improvements, it goes back to that page and copys and pastes it again, but doesn't click the buttons (so the 2nd number is never entered) – vba_user111 Jul 08 '19 at 15:13
any ideas? Thanks again for all your current help. – vba_user111 Jul 11 '19 at 13:08
@vba_user111 My guess is you need to add some wait time after the `IE.Navigate ("https://mywebsite.com/Default")` line, it's probably navigating to the page and loading while the code is still running. Try adding an `Application.Wait(Now + TimeValue(0:00:05))` after that line or something. – dwirony Jul 11 '19 at 16:36
hmm i tried this and it still just goes and tries to copy the second page (wherethe buttons are) – vba_user111 Jul 11 '19 at 19:49
To check static HTML readiness, you can use the following one-liner: `Do While (IE.readystate <> 4 or IE.busy <> False): DoEvents: Loop`. Adding 10 seconds timer after every button click or page load is highly recommended for troubleshooting because of possible JavaScript-generated HTML elements. Even better is to open your code editor on one half of the screen, run it step by step and on the other half observe IE with `.Visible` to narrow down the problematic lines of the code. – Ryszard Jędraszyk Jul 11 '19 at 23:34
@RyszardJędraszyk yes, i added these one-liners all over now. And I have tried to troubleshoot and go step by step but about three quarters of the way through the code a pop up comes and says it cannot enter break mode. even with these one liners it still doesnt go through the buttons again – vba_user111 Jul 12 '19 at 14:00
@vba_user111 Unfortunately I can only do so much without knowing the actual website URL and layout, you'll have to step line by line through this code to see why it's skipping the clicking of these buttons. – dwirony Jul 12 '19 at 15:23
@dwirony with this current code, is the program supposed to loop back to the beginning of the code and click through them again? – vba_user111 Jul 15 '19 at 13:01
@vba_user111 It's looping through all your rows, anything before that I assumed was logging into the website. – dwirony Jul 15 '19 at 14:42
@dwirony so I have it logging in, clicking the buttons and taking the first cell from excel and entering it. It then copys the data from this page back into excel. What I need help fixing is having it do this entire thing again and again(minus entering user/password). So going to first page (still logged in) and clicking the buttons, entering the next row # and copying back to excel. And I have already tried to go line by line but by the line in my code where I copy it back to excel a pop up on VBA occurs that says I cannot go into break mode, hence why by line by line wasn't working. – vba_user111 Jul 15 '19 at 14:55
@vba_user111 What is this "initiate new request" bit - is that something you have to do each time as well? This line: `Set elems = .document.queryselectorall("input[value=Initiate]")` – dwirony Jul 15 '19 at 15:55

vba_user111 · Accepted Answer · 2019-07-18T12:55:58.390

Ok, after asking this question a few times and trying to find different ways to solve this issue, I have FOUND ( / written ) my answer!

Here it is:

Option Explicit

Sub NewScrape()

Dim IE As Object
Dim IeDoc As Object
Dim aInput As Object
Dim eInput As Object
Dim svalue1 As Object
Dim a As Object
Dim b As Object
Dim elems As Object
Dim t As Date
Dim i As Long, lastrow As Long
Dim results As Variant, wkshtnames()
Dim ws As Worksheet, wks As Excel.Worksheet
Dim NewName As String
Dim sheet As Worksheet
Dim duplicate As Boolean

Const MAXWAIT_sec As Long = 10

Set ws = Sheets("VALUE")

Set IE = CreateObject("InternetExplorer.application")
IE.Visible = True
IE.Navigate ("http://mywebsite.com/")

    Do While IE.busy: DoEvents: Loop

Set IeDoc = IE.document

'Enters username and password
    With IeDoc
        .forms("signingin").UserName.Value = "userr"
        .forms("signingin").Password.Value = "password"
        .forms("signingin").document.forms(0).submit
    End With


Application.Wait (Now + TimeValue("0:00:03"))

    lastrow = ws.Cells(ws.rows.Count, "A").End(xlDown).Row
    IE.Visible = True

    For i = 3 To lastrow

Set IeDoc = IE.document ' set new page source

    t = Timer

    Do
        On Error Resume Next
        Set elems = IeDoc.queryselector("input[value=Initiate]")
        On Error GoTo 0
        If Timer - t > MAXWAIT_sec Then
            Exit Do
        End If
    Loop While elems Is Nothing

    If Not elems Is Nothing Then
        elems.Item.Click
    End If

    Application.Wait (Now + TimeValue("0:00:03"))


    IeDoc.getElementByID("checkConf").Click


    For Each aInput In IeDoc.getElementsbyTagName("input")
         If aInput.getAttribute("value") = "Request" Then
            aInput.Click
            Exit For
        End If
    Next aInput


    Do While IE.busy: DoEvents: Loop

    'Selects history
    For Each aInput In IeDoc.getElementsbyTagName("input")
        If aInput.getAttribute("value") = "History" Then
            aInput.Click
            Exit For
        End If
    Next aInput


        Set svalue1 = IeDoc.getElementByID("accountNumber")
        svalue1.Value = ws.Cells(i, 1).Value 'takes the  number out and enters
            'presses submit once acct numb is entered
            For Each aInput In IeDoc.getElementsbyTagName("input")
                If aInput.getAttribute("value") = "Submit Request" Then
                    aInput.Click
                    Exit For
                 End If
            Next aInput


    Do While (IE.READYSTATE <> 4 Or IE.busy <> False): DoEvents: Loop
    IE.Visible = True



    'Webpage sometimes takes time to load.
    Application.Wait DateAdd("s", 10, Now)


    'Selects and clears sheet 4
    Sheets("Sheet4").Select
    Range("A1:Z100").Select
    Selection.ClearContents

    Do While (IE.READYSTATE <> 4 Or IE.busy <> False): DoEvents: Loop


    IE.ExecWB 17, 0 '//select all from webpage
    IE.ExecWB 12, 2 '//Copy Selection

    Do While (IE.READYSTATE <> 4 Or IE.busy <> False): DoEvents: Loop

    Application.Wait DateAdd("s", 2, Now)

    Application.DisplayAlerts = False  '//Doesnt display alerts
    ActiveSheet.Paste

    Do While (IE.READYSTATE <> 4 Or IE.busy <> False): DoEvents: Loop


    Sheets("Sheet4").Select '//Selects sheet 4 again
    Range("A3:Q32").Select
    Selection.Copy

    Do While (IE.READYSTATE <> 4 Or IE.busy <> False): DoEvents: Loop

    Application.Wait DateAdd("s", 2, Now)

    'Creates a new sheet after & pastes content into it, formats
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Selection.Columns.AutoFit
    Selection.rows.AutoFit

    Do While (IE.READYSTATE <> 4 Or IE.busy <> False): DoEvents: Loop


    'If the worksheet already has been made:
    duplicate = False
    For Each sheet In ThisWorkbook.Sheets
        If sheet.Name = Range("D10") Then
            MsgBox ("ERROR: This Numb has already been formulated")
            NewName = InputBox("Please Rename:")
            ActiveSheet.Name = NewName
            duplicate = True
                Exit For
        End If
    Next sheet

    If duplicate = False Then
        ActiveSheet.Name = Range("d10")
        Range("A6").Clear
        ActiveSheet.Protect
       ' MsgBox ("DONE, next...")
    End If


            'this navigates back to the page where I need to enter the value in the excel column again


        IE.Navigate ("https://mywebsite.com/Default")
        Do While (IE.READYSTATE <> 4 Or IE.busy <> False): DoEvents: Loop
        IE.Visible = True


Next i
        Do While (IE.READYSTATE <> 4 Or IE.busy <> False): DoEvents: Loop
        IE.Visible = True

        On Error GoTo 0
        MsgBox ("All Accounts Have Been Formulated, Check it out!")




  End Sub

So basically I think my issue lies with where I put the lastrow= line and the i=3 etc line as well as having the Set IeDoc = IE.document line after that too. :)

Aha, I knew you needed to move the initiate inside your loop :). Sorry I couldn't have been more help, webscraping without access to the URL is like coding in the dark. — dwirony, Jul 19 '19 at 15:24

VBA to automate web-scrape entering cells

2 Answers2