Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    jeffreylewis is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2020
    Posts
    19

    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 Control to load and display the website correctly.

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

    So Far So Good.

    My question is: I would like to extract 2 pieces of 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="">£86.39</strong>

    I'd be most grateful if anyone could help anyone help!

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Me.WebBrowser0.Object.Document.documentElement.out erHTML gives you the page source. Declare a string variable and set it to that then parse it to get your two pieces:
    Code:
    Dim strSourceCode as string,vQuantity as variant,vPrice as Variant
    strSourceCode =Me.WebBrowser0.Object.Document.documentElement.outerHTML
    
    'strSourceCode ="<div class="quantity">Qty <input name="Quantity" type="text" value="1" maxlength="3" /></div>
    <strong class="showPrice" style="">£86.39</strong>"
    
    vQuantity=Left(strSourceCode,Instr(strSourceCode,vbNewLine)  'first line
    vQuantity=Replace(Replace(Replace(vQuantity,"<div class="quantity">Qty <input name="Quantity" type="text" value=","")," maxlength="3" /></div>",""),""","")
    vQuantity=CInt(vQuantity) 'Convert to Integer
    
    vPrice =Mid(strSourceCode,Instr(strSourceCode,vbNewLine)+1+)  'second line
    vPrice =Replace(Replace(vPrice ,"<strong class="showPrice" style="">",""),"</strong>","")
    vPrice =CCur(vPrice ) 'Convert to Currency
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    jeffreylewis is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2020
    Posts
    19
    Thank you Vlad. I will try that

  4. #4
    jeffreylewis is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2020
    Posts
    19
    Hi Vlad
    I cannot get past this line
    Set strSourceCode = Me.ctlBrowser.Object.Document.documentElement.inne rHTML
    It faults to "Object required"

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try that line without using Set like in my example and let us know what happens.
    Cheers,

  7. #7
    jeffreylewis is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2020
    Posts
    19
    Run-time error 91

    Object variable or with block not set

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101

  9. #9
    jeffreylewis is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2020
    Posts
    19
    Thanks for staying with me. It's 1am. I will have a look again tomorrow

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    This post might also help, see if you can use getElementByID instead of string parsing:

    https://stackoverflow.com/questions/...ntrols-content

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    jeffreylewis is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2020
    Posts
    19
    Thanks Vlad I will have a closer look at over the Weekend

  12. #12
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi all!


    The sample code below is an example of using the XMLHTTP60 and HTMLDocument instead of Web Browser Control:
    Code:
    Sub GetWebValues()
        Dim http As New MSXML2.XMLHTTP60    'Need a reference to Microsoft XML, v6.0
        Dim doc As MSHTML.HTMLDocument      'Need a reference to Microsoft HTML Object Library
        Dim strURL As String
        Dim strName As String
        Dim strTAGnCLASS As String
        Dim strTitle As String
        Dim intItem As Integer
        
        'Uncomment the lines below for your actualy web site.
        strURL = "https://www.accessforums.net/showthread.php?t=81766"
        'strURL = Me.URL
        strName = "post462648"
        'strName = "Quantity"
        strTAGnCLASS = "blockquote.postcontent.restore"
        'strTAGnCLASS = "strong.showPrice"
        intItem = 10
        'intItem = 0
        
        With http
            .Open "GET", strURL, False
            .send
            If .ReadyState = 4 And .Status = 200 Then
                Set doc = New MSHTML.HTMLDocument
                doc.body.innerHTML = .responseText
                'Get the number of your message using the name of HTML element.
                strTitle = "AccessForums post " & doc.getElementsByName(strName)(0).innerText 'Use .Value in case of <input>
                'Get the body of your message using the tag and the class name of the HTML element.
                MsgBox doc.querySelectorAll(strTAGnCLASS)(intItem).innerText, , strTitle
                doc.Close
            Else
                MsgBox "Error at " & .ReadyState & vbCrLf & "Status: " & .Status, vbExclamation
            End If
        End With
    
        Set doc = Nothing
        Set http = Nothing
    End Sub
    Cheers,
    John

  13. #13
    jeffreylewis is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2020
    Posts
    19
    Hi John

    That worked absolutely wonderful for me. Thanks you soooooo much.
    I have just one last thing to do on this form, and that is differentiate whether an item is in stock or not.
    I would hope to do this by seeing if the Quantity field exists as in this page:
    machinemart.co.uk/p/bk114-air-compressor-pump/
    Or does not exist as in this page as in this page.
    machinemart.co.uk/p/clarke-cam24-24-drum-fan/

    Once again
    Thank you
    Jeff

  14. #14
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi Jeffrey!

    Please, paste this function in the code of your form:
    Code:
    Private Function ItemInStock(ByVal strURL As String) As Boolean
        Dim http As New MSXML2.XMLHTTP60    'Need a reference to Microsoft XML, v6.0
        Dim doc As MSHTML.HTMLDocument      'Need a reference to Microsoft HTML Object Library
    
        With http
            On Error GoTo ErrH
            .Open "GET", strURL, False
            .send
            If (.ReadyState = 4 And .Status = 200) Then
                Set doc = New MSHTML.HTMLDocument
                doc.body.innerHTML = .responseText
                On Error Resume Next
                Me.Quantity = 0
                'Try to get the quantity.
                Me.Quantity = doc.getElementsByName("Quantity")(0).Value
                If Me.Quantity > 0 Then
                    'Item is in stock.
                    ItemInStock = True
                End If
                'Get the show price.
                Me.showPrice = doc.querySelectorAll("strong.showPrice")(0).innerText
            Else
                MsgBox "Error at state" & .ReadyState _
                       & " with status: " & .Status & vbCrLf & vbCrLf _
                       & "Description: " & Err.Description, vbExclamation, "HTTP Error #" & Err
            End If
        End With
    ExitHere:
        Set doc = Nothing
        Set http = Nothing
        Exit Function
    ErrH:
        MsgBox Err.Description, vbExclamation, "HTTP request error #" & Err
        Resume ExitHere
    End Function
    This returns True - among others - if "Quantity" is greater that zero, so you can use this to inform the user for the item availability, or, you can make a loop with several URLs while returns False.

    For example:
    Insert a checkbox with name "InStock" and in the AfterUpdate event of the "URL" control put the code below:
    Code:
        With Me.URL
            If IsNull(.Value) Then
                Beep
            Else
                Me.InStock = ItemInStock(.Value)
            End If
        End With
    Unfortunately, my system's IE8 doesn't open the web pages that you provided, so I can't test that code and I just hope that works.

    Cheers,
    John

  15. #15
    jeffreylewis is offline Novice
    Windows 10 Access 2007
    Join Date
    Sep 2020
    Posts
    19
    Hi

    That almost works exept it doesn't differentiate between in stack and out of stock

    Many thanks for staying with me

    Regards
    Jeff

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-03-2014, 02:53 PM
  2. Web Browser Control
    By MFS in forum Programming
    Replies: 0
    Last Post: 02-28-2013, 11:08 AM
  3. Web Browser Control
    By imran688 in forum Programming
    Replies: 1
    Last Post: 11-29-2012, 03:52 PM
  4. Focus in web browser control
    By bjornhe in forum Access
    Replies: 0
    Last Post: 10-27-2011, 05:10 AM
  5. Web Browser Control
    By ctgann in forum Forms
    Replies: 5
    Last Post: 03-18-2011, 08:17 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums