• 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

D

dheckart

New Member
When I run this code I do not get an error message but it also does not pull the information that i am looking for. Is there something that I am missing as to why it is not pulling the information. Also please let me know if you need the HTML code as well. Also I am trying to pull the innertext from the ClassName "product-text" that is inside the ClassName "product-text-label" that is inside the classname "product-meta-data" and then innertext is the description on the webpage.
Code: 
Sub FillInternetForm()
Dim IE As Object
Dim html As HTMLDocument
Set IE = CreateObject("InternetExplorer.Application")
IE.Navigate "http://barcodelookup.com"
IE.Visible = True

While IE.Busy
DoEvents 'wait until IE is done loading page.
Wend

IE.document.All("search-input").Value = ThisWorkbook.Sheets("Sheet1").Range("a1").Text
'IE.Document.All("btn btn-danger btn-search").Click
Set btn = IE.document.querySelector("span[class=btn-seach-text]")
btn.Click
Set tags = IE.document.getElementsByTagName("button")
For Each tagx In tags
If tagx.innerText = "Search" Then
tagx.Click
Exit For
End If
Next

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

Set html = IE.document
Set HoldingsClass = html.getElementsByClassName("product-meta-data")

For Each HoldingClass In HoldingsClass
If InStr(HoldingClass.innerText, "Description") And InStr(HoldingClass.className, "Product-text-label") Then
If InStr(HoldingClass.innerText, "Format") And InStr(HoldingClass.className, "product-text") Then
Sheet1.Cells(2, f).Value = HoldingClass.innerText
Exit For
End If
End If
Next

'Cleanup
IE.Quit
Set IE = Nothing

End Sub
 

Attachments

  • test.xlsm
    19.8 KB · Views: 4
Last edited:
As you forgot to attach the workbook and to elaborate what should be done on the website and what is the expected result​
this is more a challenge for some mind readers forum rather than for any Excel forum …​
​
 
I am sorry for not posting everything that was required i hope that i was able to get everything that that is needed. and the expected result is that in putts the innerText of the description of the webpage in the cell f2 on the excel document.
 
Marc L said:
As you forgot to attach the workbook and to elaborate what should be done on the website and what is the expected result​
this is more a challenge for some mind readers forum rather than for any Excel forum …​
​
Click to expand...
are you saying that you guys might not be able to help me on this post
 
The weird thing with your code is you mixed early binding with late binding and​
as this is an easy webpage it just needs to well inspect it in order to use the appropriate elements …​
​
According to your attachment for starters a late binding only VBA demonstration so it does not need to activate any additional reference :​
Code: 
Sub Demo1()
        If [A1].Text = "" Then Beep: Exit Sub
        Application.Cursor = xlWait
        On Error GoTo Fin
    With CreateObject("InternetExplorer.Application")
       .Navigate "http://barcodelookup.com"
        While .Busy Or .ReadyState < 4:  DoEvents:  Wend
       .Document.all("search-input").Value = [A1].Text
       .Document.querySelector("button.btn.btn-danger.btn-search").Click
        While .Document.ReadyState <> "complete":  DoEvents:  Wend
        [F2].Value2 = .Document.querySelector("div.product-meta-data>div>span").innerText
Fin:
        If Err.Number <> -2147023706 Then .Quit
    End With
        If Err.Number Then Beep: [F2].Value2 = "#" & Err.Number & " : " & Err.Description
        Application.Cursor = xlDefault
End Sub
Do you like it ? So thanks to click on bottom right Like !​
​
 
Thank you for the help can i ask one more question does this code work for expansion for instance if i wanted to add something like this on the next line "[D2].Value3 = .Document.querySelector("div.col-md-12>div>span").innerText do i need to to add anything at the end of the [F2] to run the next line of code
 
scratch last question i figured it out. but i am also trying to to pull data from the div.col-md-12>div.product-text-label>span.product-text" but for everyone of those there are multiple instances with the same name so i need to narrow it down for this instance under the div.product-text-label where the innerText says Category how can I narrow it down to that specific one in the html code. I am sorry for asking so many questions i am new at writing excel code so I thank you for any help that you are giving me.
 
When an element is not 'unique'​
- in fact this is a wrong assertion as all elements are unique as you can check when inspecting the CCS path of any element -​
so proceed the same way I used in post #5 : go back wih a parent until it is 'unique' …​
​
When there is no unique criteria just use some classic getElementsBy… statement to get a collection​
where you can pick up the desired element with its index # (first index of a collection is zero) …​
​
 
First you must target a collection like With .Document.getElementsBy…​
​
then you can reach any element with its index like for the first one Range.Value = .Item(0).innerText inside the With … End With block.​
​
 
I just realized that the code i just spent all this time on is only for one row and there is going to be multiple rows for this. how hard would it be to change the code so that if something is entered into column A no matter what row in gathers the same information and inserts it into the same row and puts the information into the right columns for that information. i am just wondering how hard it would be.
thank you for the help.
 
As it is harder to guess what you are expecting for - at mind reader level - instead of just reading a webpage code - at beginner level -​
so attach at least your expected result workbook …​
​
 
okay this is the sheet that the information is going into but I need for when a bar code is scanned into Column A for whatever row it fills in the information for that row. I hope this helps for what I am looking for. And again thank you.
 

Attachments

  • Copy of Inventory System.xlsx
    26.3 KB · Views: 6
If the barcodes are scanned 'slowly', with enough time to grab some data from the website so an event can launch the web procedure​
but if it is too fast some issues may occur like missing data from web, crash, …​
​
 
I understand that but how hard would it be to change what I already have and if you need that code I can send that as well
 
So well describe / elaborate the behavior you expect for, how it should occur, work, …​
​
 
it can either be when they scan in a barcode in column a row 1 it pulls the information from the website and puts it in the appropriate column and row 1. and so on for row 2 and then row three. now I don't if it runs when they scan it or they scan them all in and then the script runs through all that are there.
I hope this helps
 
Your post #13 attachment contains an unique barcode but as I need at least two to give it a try …​
​
 
According to your post #13 attachment another VBA demonstration for columns D, E, H and J​
to paste to the Sheet2 (DVD-Blu-ray) worksheet module : (v2)​
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 "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)
                                                    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
You should Like it !​
​
 
You must log in or register to reply here.
Back
Top