0

So I thought I had gotten close to this, the code goes through a column of unrefined addresses, generates a google search url in another, and then pulls the address google has an writes it to the 3rd column.

Before I could only get it to work if I specified the cell location, I want it to work by going down every URL in a column and writing the address one by one.

So i thought "let's put the getElementsByClassName in another loop"

Needless to say it doesn't work, I get automation error on the IE.Navigate line.

Private Sub CommandButton1_Click()
Dim IE As Object

' Create InternetExplorer Object
Set IE = CreateObject("InternetExplorer.Application")

' You can uncoment Next line To see form results
IE.Visible = False

'START LOOP
' URL to get data from
For r = 2 To 3
    IE.navigate Sheets("Sheet1").Cells(r, "A").Value

    Do While IE.Busy
    Application.Wait DateAdd("s", 1, Now)
    Loop

    Dim dd As String, c
    ' Runs loops to look for the value within the classname, if classname alternates it will change the element, if null it will exit.
        For Each c In Array("vk_sh vk_bk", "_Xbe")
            On Error Resume Next
            dd = IE.document.getElementsByClassName(c)(0).innerText
            On Error GoTo 0
            If Len(dd) > 0 Then Exit For
        ' Gives a confirmation message and writes the result to a cell
        Cells(r, "C").Value = dd
        Next
Next r

' /LOOP

' Show IE
IE.Visible = False

' Clean up
Set IE = Nothing


End Sub

Notes:

From 2 to 3 is correct, the list is pretty long so i want to test it out with just 2 addresses first.

Can someone more proficient in VBA tell me where I am going wrong?

UPDATED: Changed range to cells

3
  • Can you give a couple of examples of URLs? Commented Aug 17, 2016 at 4:48
  • 1
    Sure [link]google.com.au/… [link]google.com.au/search?q=+Priceline+Mascot [link]google.com.au/search?q=+Null+Test Respectively, they are 1. Good but not perfect address, 2. Useless until put through google address 2. Null checker to ensure it can still run if no address is found. Commented Aug 17, 2016 at 5:00
  • Sorry about the comment links, I'm new at this. Commented Aug 17, 2016 at 5:01

4 Answers 4

1

No offence, but your code (and coding style) is a mine field.

This part Sheets("Sheet1").Range(r, "A").Value will throw error 1004, Range cant take two params as Row and Columns, Cells can. Change it to : Sheets("Sheet1").Cells(r, "A").Value

Second, if Cells(r, "A").Value is blank/empty Navigate will throw error 5. Check for a non empty value before navigating.

Same range issue, error 1004 for Range(r, "C").Value

Not writing address: It will not write anything in column C because of your ill constructed inner for loop. When a condition is met, you are jumping out of the loop before writing the value in cells.

Here

 If Len(dd) > 0 Then Exit For
      'Gives a confirmation message and writes the result to a cell
      ws.Cells(r,"C").Value = dd

if length of dd is > 0 it will never reach the statement, ws.Cells(r,"C").Value = dd

Change it to:

 If Len(dd) > 0 Then
        'Gives a confirmation message and writes the result to a cell
         ws.Cells(r,"C").Value= dd
        Exit For
      End If

Bonus: Learn and start using F8

Sign up to request clarification or add additional context in comments.

3 Comments

Yeah it's not my best work, no excuses other than I'm new at VBA. I changed from range to value and that got rid of the errors but now it runs and doesn't change anything. This seems to be what dgorti is talking about where the macro runs but stop before it can grab the addresses I want.
Great it's working better now, it goes automatically through the list but it is giving me duplicates. It gets the first one right, then the 2nd, then the 3rd one is writes what it wrote in the 2nd, then the 4th and 6th is correct but the 7th and 8th is what the 6th produces. Would I be correct in saying it isn't finding the latest address and just writing what it did previously so as to continue the loop? OR it's coming across one of the different class names and breaking there?
its certainly not due to VBA. read HTML source of each URL and check if the classes fo conatin same value. Most likely yes, as the URLs you supplied are all from google family. And read this: skidmore.edu/~pdwyer/e/eoc/help_vampire.htm and stackoverflow.com/help/someone-answers
1

One issue that will definitely give you an error is how you are using range.

When using range for your code you would need to use the following:

IE.navigate Sheets("Sheet1").Range("A" & r).Value

Then of course you would need to print out the same way

Range("C" & r).Value = dd

now a couple of hints that you can place in your code to make it more efficient,

Private Sub CommandButton1_Click()

  Dim IE As Object, r as integer
  Dim wb as Workbook, ws as Worksheet
  Dim dd as String, c as Variant, found as Boolean

  'Create InternetExplorer Object
  Set IE = CreateObject("InternetExplorer.Application")
  'create other objects
  Set wb = ThisWorkbook
  Set ws = wb.Worksheets("Sheet1")


  ' You can uncoment Next line To see form results
  IE.Visible = False

  'START LOOP
  ' URL to get data from
  For r = 2 To 3
    debug.print ws.Range("A" & r).Value 'see what the url is
    IE.navigate ws.Range("A" & r).Value
    Do while IE.ReadyState <> 4: DoEvents: Loop
    'page loaded.
    ' Runs loops to look for the value within the classname, if classname alternates it will change the element, if null it will exit
    found = false
    For each c in Array("vk_sh vk_bk", "_Xbe")
      On Error Resume Next
      dd = IE.document.getElementsByClassName(c)(0).innerText
      On Error GoTo 0
      If Len(dd) > 0 Then
        found = true
      End If
      If found Then
        ws.Range("C" & r).Value = dd
        dd = ""  'need to set to nothing or it will retain the value.
        Exit For
      End If
    Next c
  Next r

  IE.Quit

'Clean up
Set IE = Nothing


End Sub

There are definitely more ways to do what you are after but fix up those 2 things. If you need more help post your workbook.

Thanks

EDIT: I made some changes to the above code. It looks like you may have been exiting your loop too quickly. I was able to get the URLs you provided to work using this.

11 Comments

Wow this was a good answer. I threw in your recommended changes but it doesn't write the addresses. My guess is it's not reading the info in time or something. Is there a way I can post my workbook?
If you are not seeing the addresses you probably just need to enable your immediate window. VIEW > Immediate Window Or press CTRL + G
To post a workbook you may need to add it google drive and then paste the link. I am actually relatively new to stackoverflow so Im not sure if just messaging me would be ok or not but if it is you can and I can look at the workbook for you.
Ah yes I can see immediate window now. What it's doing is listing the Unrefined Addresses one by one and then stopping. Whereas in my old code (where it would work only if I specified the cell number) nothing appears.
Ah I wish I could post it but I'm at work and our VPN blocks out Google Drive
|
0

Including the other issues raised in the comments, the below worked for me at run time (i.e. not just while going through with F8)

Private Sub CommandButton1_Click()
Dim IE      As Object
Dim LngRow  As Long
Dim Wksht   As Worksheet
Dim dd      As String
Dim c       As Variant

On Error Resume Next

Set Wksht = ThisWorkbook.Worksheets("Sheet1")
    Set IE = CreateObject("InternetExplorer.Application")
        IE.Visible = False
        'The below will process the all rows in column A
        For LngRow = 2 To Wksht.Range("A" & Wksht.Rows.Count).End(xlUp).Row
            If Wksht.Cells(LngRow, 1) <> "" Then
                IE.Navigate Wksht.Cells(LngRow, 1)

                'This loops waits for IE to be ready
                Do Until (IE.Document.ReadyState = "complete") And (Not IE.busy)
                    DoEvents
                Loop

                For Each c In Array("vk_sh vk_bk", "_Xbe")
                    dd = ""
                    dd = IE.Document.getElementsByClassName(c)(0).innerText
                    If Len(dd) > 0 Then
                        Wksht.Cells(LngRow, 3) = dd
                        Exit For
                    End If
                Next

            End If
        Next
        IE.Quit
    Set IE = Nothing
Set Wksht = Nothing

End Sub

1 Comment

Honestly I wish I could mark everyones answer as the correct one since everyone helped so much but yours worked right off the bat and it works independent of the number of addresses in column A
0

There seems to be 2 issues with your code. I have not tried, but looking at your code

  1. Range is defined as cell1, cell2. Cell Address is "A2", "A3" etc. so in your code you might need to do Range("A" & r) to concatenate the row with the column.
  2. Even after that you will have an issue. When you Navigate to a URL, You need to wait until the document is loaded. You have to either do some event driven code or loop until the document state is ready or something. Otherwise you will not be able to parse and read the contents of the loaded document.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.