Get two bits of Data from Microsoft Web Browser Control Access 2007

J

jeffreylewis

New member
Local time
Today, 22:01
Joined
Sep 17, 2020
Messages
15
In Ms Access 2007 32 Bit
Windows 10

I am trying to extract data from a website that updates 2 fields on a regular basis.

I have created a form which has a large Microsoft Web Browser Control called "WebBrowser0"
and a field with the url called "URL
I am able to get the Microsoft Web Browser Control to load and display the website correctly.

The Form also has 2 other fields called.
"quantity"
"showPrice"

I would like to extract data from the website showing in the Microsoft Web Browser Control
And put it into the 2 fields "quantity" and "showPrice"

The source on the webpage is:
<div class="quantity">Qty <input name="Quantity" type="text" value="1" maxlength="3" /></div>
<strong class="showPrice" style="">THE PRICE APPEARS HERE</strong>

Can anyone help?
Getting desparate
 
This may take a bit of patience and study, but here are two excellent discussions that will help.

This
and This

on this one I uploaded a sample attachment which may be of interest
 
sample scraping:
Code: 
    Dim ipos As Integer
    Dim dblQuantity As Double
    Dim dblPrice As Double
    Dim strQuantity As String
    Dim html As String

    html = html & "<html><head>"
    html = "<html>" & _
        "<head></head>" & _
        "<body>" & _
        "<div class=""quantity"">Qty <input name=""Quantity"" type=""text"" value=""1"" maxlength=""3"" /></div>" & _
        "<strong class=""showPrice"" style="""">100.00</strong>" & _
        "</body>" & _
        "</html>"

    'check first if the textbox is present
    ipos = InStr(html, "<input name=""Quantity""")
    If ipos > 0 Then
        'textbox is present
        'get the Value for Quantity
        ipos = InStr(ipos, html, "value=")
        html = Mid(html, ipos + Len("value="))
        ipos = InStr(1, html, "maxlength")
        'this is the quantity
        strQuantity = Trim(Left(html, ipos - 1))
        Do While InStr(1, strQuantity, """") > 0
            strQuantity = Replace(strQuantity, """", "")
        Loop
        dblQuantity = CDbl(strQuantity)
    End If
    'check if class showPrice is present
    ipos = InStr(1, html, "<strong class=""showPrice""")
    If ipos > 0 Then
        'showPrice is present
        ipos = InStr(ipos, html, ">")
        html = Mid(html, ipos + 1)
        ipos = InStr(1, html, "<")
        dblPrice = CDbl(Left(html, ipos - 1))
    End If
    objIE.Quit
    Debug.Print dblQuantity
    Debug.Print dblPrice
 
Hi Arnelgp

Your code is the closest I have got to achieving my aim for these 2 fields.
I would like to have shared my answer on this site after 1.5 hours composing a response the administrators say that the response contains spam like elements and will not allow me to post.

So I have attached a text file which is my answer to you

Best regards
and Thanks


Snap4.jpg
 

Attachments

Crossposted with responses.

Extract Data from Microsoft Web Browser Control

In Ms Access 2007 Windows 10 I am trying to extract data from a website that updates 2 fields on a regular basis. I have created a form which has a large Microsoft Web Browser Control called "ctlBrowser" and a field with the url called "URL I am able to get the Microsoft Web Browser...
www.accessforums.net
 
Hi Gasman

It still tells me

Although the preview displays correctly.
I have 3 urls to show as examples outside of the code area

Your content can not be submitted. This is likely because your content is spam-like or contains inappropriate elements. Please change your content or try again later. If you still have problems, please contact an administrator.
 
see if this modified code will work for you:
Code: 
Private Sub Command1_Click()
    Dim ipos As Long
    Dim dblQuantity As Double
    Dim dblPrice As Double
    Dim strQuantity As String
    Dim strCurrency As String
    Dim strPrice As String
    Dim HTML As String

    HTML = Me.WebBrowser0.Object.Document.documentElement.OuterHtml

    'check if class showPrice is present
    ipos = InStr(1, HTML, "<strong class=""showPrice""")
    If ipos > 0 Then
        'showPrice is present
        ipos = InStr(ipos, HTML, ">")
        HTML = Mid(HTML, ipos + 1)
        ipos = InStr(1, HTML, "<")
        strPrice = Trim(Left(HTML, ipos - 1))
        strCurrency = getCurrency(strPrice)
        dblPrice = CDbl(Replace(strPrice, strCurrency, ""))
    End If
    
    'check first if the textbox is present
    ipos = InStr(HTML, "<input name=""Quantity""")
    If ipos > 0 Then
        'textbox is present
        'get the Value for Quantity
        ipos = InStr(ipos, HTML, "value=")
        HTML = Mid(HTML, ipos + Len("value="))
        ipos = InStr(1, HTML, "></div>")
        'this is the quantity
        strQuantity = Trim(Left(HTML, ipos - 1))
        Do While InStr(1, strQuantity, """") > 0
            strQuantity = Replace(strQuantity, """", "")
        Loop
        dblQuantity = CDbl(strQuantity)
    End If
    Debug.Print dblQuantity
    Debug.Print dblPrice
End Sub

Public Function getCurrency(p As String) As String
    Dim i As Integer
    i = 1
    Do Until IsNumeric(Mid(p, i, 1)) = True
        i = i + 1
    Loop
    i = i - 1
    getCurrency = Left(p, i)
End Function
 
jeffreylewis said:
Hi Gasman

It still tells me

Although the preview displays correctly.
I have 3 urls to show as examples outside of the code area

Your content can not be submitted. This is likely because your content is spam-like or contains inappropriate elements. Please change your content or try again later. If you still have problems, please contact an administrator.
Click to expand...
You cannot post links until you have 10 posts. This is to prevent spammers. Try and break the link with a space or remove the http://www. as below for this thread.

access-programmers.co.uk/forums/threads/get-two-bits-of-data-from-microsoft-web-browser-control-access-2007.313835/#post-1720459
 
arnelgp said:
see if this modified code will work for you:
Code: 
Private Sub Command1_Click()
    Dim ipos As Long
    Dim dblQuantity As Double
    Dim dblPrice As Double
    Dim strQuantity As String
    Dim strCurrency As String
    Dim strPrice As String
    Dim HTML As String

    HTML = Me.WebBrowser0.Object.Document.documentElement.OuterHtml

    'check if class showPrice is present
    ipos = InStr(1, HTML, "<strong class=""showPrice""")
    If ipos > 0 Then
        'showPrice is present
        ipos = InStr(ipos, HTML, ">")
        HTML = Mid(HTML, ipos + 1)
        ipos = InStr(1, HTML, "<")
        strPrice = Trim(Left(HTML, ipos - 1))
        strCurrency = getCurrency(strPrice)
        dblPrice = CDbl(Replace(strPrice, strCurrency, ""))
    End If
   
    'check first if the textbox is present
    ipos = InStr(HTML, "<input name=""Quantity""")
    If ipos > 0 Then
        'textbox is present
        'get the Value for Quantity
        ipos = InStr(ipos, HTML, "value=")
        HTML = Mid(HTML, ipos + Len("value="))
        ipos = InStr(1, HTML, "></div>")
        'this is the quantity
        strQuantity = Trim(Left(HTML, ipos - 1))
        Do While InStr(1, strQuantity, """") > 0
            strQuantity = Replace(strQuantity, """", "")
        Loop
        dblQuantity = CDbl(strQuantity)
    End If
    Debug.Print dblQuantity
    Debug.Print dblPrice
End Sub

Public Function getCurrency(p As String) As String
    Dim i As Integer
    i = 1
    Do Until IsNumeric(Mid(p, i, 1)) = True
        i = i + 1
    Loop
    i = i - 1
    getCurrency = Left(p, i)
End Function
Click to expand...

Thanks
But in the Field "showPrice" I do not seem to be picking up the correct element from the website.
tinyurl.com/y2wsnsxn

In the Field "quantity" it seems a little more complex than I at first thought
The website hides the quantity field when out of stock as in this item as in the example below
tinyurl.com/y5pz68ll

Or it displays "qty 1" when it is in stock as in the example below
tinyurl.com/y2wsnsxn
 
Walk through the code line by line with F8 and see what the code actually does.

It *seems* correct to me, but debugging it, will be the real test.?
 
You must log in or register to reply here.

Similar threads

M
Solved Issue with Edge Browser Control on User's Computer
Replies
7
Views
1,329
Mohsin Malik
M
Uncle Gizmo
Microsoft’s “Premier” Mix-Up: A Tale of Access and Amnesia
Replies
1
Views
325
Isaac
Isaac
M
Solved Enable Annotations Settings in Microsoft Access Edge Web Browser Control
Replies
3
Views
1,372
Mohsin Malik
M
L
Consuming an API to retrieve data and store it in a Microsoft Access table.
Replies
3
Views
698
lbcurrier
L
M
Solved Microsoft Access: Edge Browser Control Clipboard Sharing Issue
Replies
7
Views
1,894
Mohsin Malik
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom