Excel VBA to Sign Into and then Extract Data from a Website

[Kc7487]

Hello Mr. Excel folks!

I have made great strides on my attempts to automate logging into, and then extracting data, from a website.

I have gotten all the way to the point where the data is indeed in excel! However, the data is all going in only one column, column A, and I cannot figure out how to fix this problem. I was hoping somebody could take a look at this code and maybe give me a hint as to what I need to do to make the table populate properly (each cell in a different cell).

It is so close, but with the entire row (like 30 columns or so) populating into ONLY one column (column A) it just won't work for me.

Please, any help would be greatly appreciated.

I will post my current code below.

Private Sub CommandButton1_Click()


' CREATE_IEAPP Macro
' Testing signing in creating an internet explorer application and passing username and pw to it
'

'THIS MACRO IS EDITED FROM IEAPP MACRO, trying to pass values directly to the control boxes on the sign in form and not use send keys


    Dim i As Long
    Dim IE As Object
    Dim objElement As Object
    Dim objCollection As Object
Application.DisplayAlerts = False
    
    Worksheets("Auto SSD Here").Cells.ClearContents
    
    
    ' Create InternetExplorer Object
    Set IE = CreateObject("InternetExplorer.Application")
 
    ' You can uncoment Next line To see form results
    IE.Visible = True
 
    ' Send the form data To URL As POST binary request
    IE.Navigate Sheets("Auto Todays SSD").Range("A1").Value 'Changed from MACRO WORKAROUND A13 due to Web Querry not working....trying to go straight to the SSD to login
 
    ' Statusbar
        Application.StatusBar = "My CODE is loading. Please wait..."
 
    ' Wait while IE loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
  
  'on error scip to scraping data because the error is likely that the internet explorer object is already signed in
  On Error GoTo ScrapingData
  
  
    
    ' Wait while IE loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
             'the line below is working fine, take out the ' to login hard coded again, but trying to use a cell to pass the value to the login form, SEEMS TO WORK ON LINE BELOW IT
             'IE.Document.getElementByID("Ecom_User_ID").Value = "HARD CODED USERNAME GOES HERE" 
              IE.document.getElementByID("Ecom_User_ID").Value = Range("B14")
    
      ' Wait while IE loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
             'the line below is working fine, take out the ' to login hard coded again, but trying to use a cell to pass the value to the login form, SEEMS TO WORK ON LINE BELOW IT
             'IE.Document.getElementByID("password-password").Value = "HARD CODED PASSWORD GOES HERE"
              IE.document.getElementByID("password-password").Value = Range("B15")
             
      ' Wait while IE loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
    
            IE.document.getElementByID("loginButton").Click
 
      ' Wait while IE loading...
    Do While IE.Busy
        Application.Wait DateAdd("s", 1, Now)
    Loop
 
 
 
 
 'THIS SCRAPING DATA PART IS WHAT IS GIVING ME TROUBLE
 'SCRAPING THE DATA USING A WEB QUERY DOES NOT SEEM TO WORK, BECAUSE THE WEB QUERY IS NOT RECOGNIZED AS LOGGED IN, EVEN IF EXCEL OPENED UP AN INTERNET EXPLORER OBJECT AND LOGGED IN A FEW SECONDS BEFORE.
 
 ' THEREFORE TRYING TO SCRAPE THE DATA INSIDE OF THE SAME INTERNET EXPLORER OBJECT WHICH EXCEL VBA USES TO LOG IN. USING COPY/PASTE DOES ACTUALLY USUALLY SEEM TO WORK (USING SEND KEYS) BUT UNFORTUNATELY
 'SOMETIMES INSTEAD OF COPYING AND PASTING THE SSD PART, IT WILL SOMEHOW SKIP THE COPY PORTION, AND SIMPLY PASTE WHATEVER WAS LAST COPIED TO THE CLIPBOARD IN CELL A1
 
 'Furthermore and even more unfortunately, a windows security dialog box pops up which requires clicking on cancel like 39 times before the SSD appears....
 
 'NOW TRYING TO USE A DIFFERENT CODE THAT DOES NOT UTILIZE THE CLIPBOARD OR SENDKEYS, AND COMMENTING AWAY ALL OF THIS TEMPORARILY.
 
'ScrapingData:
'
'Application.DisplayAlerts = False
'
'    SendKeys "^a"
'
'    Do While IE.Busy
'        Application.Wait DateAdd("s", 1, Now)
'    Loop
'
'    SendKeys "^c"
'
'    Do While IE.Busy
'        Application.Wait DateAdd("s", 1, Now)
'    Loop
'
'
'   Worksheets("Auto SSD Here").Cells.UnMerge
'   Worksheets("Auto SSD Here").Paste Destination:=Worksheets("Auto SSD Here").Range("A1")
'   Worksheets("Auto SSD Here").Cells.UnMerge
'
'    ' Clean up
'    'IE.Quit 'Closes the window,deactivate if you want to leave ssd window open to make sure pasted properly, etc...
'    Set IE = Nothing
'    Set objElement = Nothing
'    Set objCollection = Nothing
'
'    Application.StatusBar = ""
'    Application.DisplayAlerts = True
'
'Exit Sub
'ErrorMessageBox:
'    'MsgBox "Error, you are probably already signed in, OR the code is no longer working and needs attention, contact xxxxxxxxxxx."





'This is the new code for scraping that does not use the clipboard and sendkeys, the portion above has all been commented INOP so that if needed it can be re-activated
'This code below has a problem too though, it only populates into the A column, all data is in one column.... inquiring on Mr Excel

ScrapingData:

With IE
            x = .document.body.innertext
            x = Replace(x, Chr(10), Chr(13))
            x = Split(x, Chr(13))
            Worksheets("Auto SSD Here").Range("A1").Resize(UBound(x)) = Application.Transpose(x) 'WHAT DO THE LAST 3 LINES DO???

            .Quit
        End With

        'Cleaning up
         Set IE = Nothing
         Set objElement = Nothing
         Set objCollection = Nothing
         Application.StatusBar = ""
         Application.DisplayAlerts = True


'The problem with this code above is that every line of the SSD populates into only 1 column. It does get all of the data, and put each row on a different row (but also scrapes notes).
         
         
         
         
         
         
    End Sub

 

[John_w]

Hi,

will like to check how can I change the coding so as that I can a specific column of a table.

Please assist.
Thanks

Loop through the rows in the required column, e.g. cells(0) means the first column:

    Dim r As Long
    For r = 0 To table.Rows.Length - 1
        Debug.Print table.Rows(r).Cells(0).innerText
    Next

 

[sylvesterbetta]

Loop through the rows in the required column, e.g. cells(0) means the first column:

    Dim r As Long
    For r = 0 To table.Rows.Length - 1
        Debug.Print table.Rows(r).Cells(0).innerText
    Next

Hi John,

Thanks for the reply. But will you be able to explain the coding as I dun understand why do we need to use "debug.print"

Please assist.

Thanks.

 

[John_w]

You don't need Debug.Print. Just change it to the required sheet/cell reference to write the value to a cell, as with the previous code:

        Worksheets("Auto SSD Here").Range("A1").Offset(r).Value = table.Rows(r).Cells(0).innerText

 

[sylvesterbetta]

You don't need Debug.Print. Just change it to the required sheet/cell reference to write the value to a cell, as with the previous code:

        Worksheets("Auto SSD Here").Range("A1").Offset(r).Value = table.Rows(r).Cells(0).innerText

Hi John, I did as what u had mention and was being prompt “Run-time error ‘424’, Object required” . Will you be able to assist n advice what went wrong. Thanks

 

[John_w]

It was based on the previously posted code, so have you included the table reference?

    Dim table As Object
    Dim r As Long
    Set table = IE.document.getElementsByTagName("TABLE")(0)   '0 = 1st table, 1 = 2nd table, etc.
    For r = 0 To table.Rows.Length - 1
        Worksheets("Auto SSD Here").Range("A1").Offset(r).Value = table.Rows(r).Cells(0).innerText
    Next

The above is all very generic and based on the OP's code. Please start your own thread if you need help with your specific site.

 

[sylvesterbetta]

Hi John, thanks for your help. I did that’s why dun understand why the error?