• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pulling information from website when the ClassName is not unique when running this in an excel module

That is Fantastic thank you so much i just have one question that I don't Understand about it if you could explain it to me
Cells(R, 10).Value2 = Mid(oSpan.innerText, 6)
can you tell me what the 6 means?
 
  • It's the starting point as you can read in the VBA help of Mid function …

  • Check if you have the last version of the code within post #25.
 
The Mid VBA function is now removed from the post #25 code for optimization / simplication …​
​
 
Thank you very much but can i ask one more question so i want to take the online store section and put all the store name in one cell but have them stacked on top of each other like
Ebay
Amazon
And so can you tell me what vba code will do that like if i hit ALT+ENTER
 
okay i have tried to write this but I keep getting an error Compile error: for Control variable already in use
Code: 
Sub Demo2()
      Const D = ": "
        Dim R&, V, W, Obj As Object, S$(), oSpan As Object
    With Range("A1", Cells(Rows.Count, 1).End(xlUp)).Columns
        R = .Rows.Count:     If R = 1 Or Application.CountA(.Item(4)) = R Then Beep: Exit Sub
        V = .Item(1).Value2
        W = .Item(4).Value2
    End With
        Application.Cursor = xlWait
        On Error GoTo Fin
    With CreateObject("InternetExplorer.Application")
        For R = 2 To R
            If V(R, 1) > "" And W(R, 1) = "" Then
                   .Navigate "https://barcodelookup.com/" & V(R, 1)
                    While .Busy Or .readyState < 4:  DoEvents:  Wend
                If IsObject(.document.all("h1-404")) Then
                    Cells(R, 4).Value2 = " not valid barcode !"
                Else
                        Cells(R, 5).Value2 = .document.getElementsByTagName("H4")(0).innerText
                    For Each Obj In .document.getElementsByClassName("product-text-label")
                            S = Split(Obj.innerText, D)
                        Select Case S(0)
                               Case "Manufacturer"
                                    Cells(R, 8).Value2 = S(1)
                               Case "Description"
                                    Cells(R, 6).Value2 = S(1)
                               Case "Attributes"
                                    For Each oSpan In Obj.getElementsByTagName("SPAN")
                                            S = Split(oSpan.innerText, D)
                                        Select Case S(0)
                                               Case "Format"
                                                    Cells(R, 4).Value2 = S(1)
                                               Case "MPN"
                                                    Cells(R, 10).Value2 = S(1)
                                               Case "Release Date"
                                                    Cells(R, 12).Value2 = S(1)
                                                    Exit For
                                For Each Obj In .document.getElementsByClassName("Store-List")
                                        S = Split(Obj.innerText, D)
                                            Select Case S(0)
                                                Case "Store-name"
                                                Cells(R, 19).Value2 = S(1)
                                                Exit For
                                End Select
                            Next
                        End Select
                    Next
                End If
            End If
        Next
Fin:
        If Err.Number <> -2147023706 Then .Quit
    End With
        If Err.Number Then Beep: Debug.Print "#" & Err.Number; " : "; Err.Description
        Application.Cursor = xlDefault
        Set oSpan = Nothing
End Sub
the green text is the part that i am getting the error message above on
 
According to the error message you can't use a variable for the loop currently used in an upper loop so just use another variable …​
​
But as this is a collection without any link with the Obj loop so just place this new loop after the main one - aka outside -​
so you can use the same Obj variable, if only a loop is really necessary …​
​
 
With the column S right formatted without any loop :​
​
Cells(R, 19).Value2 = Replace(.Document.getElementsByClassName("store-list")(0).innerText, " " & vbCr, "") …​
​
You may Like it !​
​
 
As your barcode samples are only for DVD / Blu-ray so the demonstration was designed to be located in the respective worksheet.​
​
If you need to use the same website for another worksheet it should be possible to locate the VBA procedure in a standard module​
so when launched it will operate the active sheet but obviously with only a smart enough worksheet design​
in particular for the layout of the columns to be imported from the website …​
​
But if the layouts are different then you must adapt the VBA procedure to each worksheet module.​
​
 
You must log in or register to reply here.
Back
Top