Solved

Get data from webpage in Access

Posted on 2014-10-28
22
153 Views
Last Modified: 2014-10-29
I would like to grab a field of data in access and enter that value into the vin field fo the following url:
view-source:https://www.3xmotorcycles-superstore.com/vehicle-search

then grab the return data if any and store in an access table.

Not sure how I go about this.
0
Comment
Question by:PeterBaileyUk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 3
  • 2
  • +1
22 Comments
 

Author Comment

by:PeterBaileyUk
ID: 40408812
ok ive got this far but not sure how to find the element in the page, I run it but it fails on the for loop saying the remote server is not available

    url = "https://www.3xmotorcycles-superstore.com/vehicle-search"
    Dim IE As Object, obj As Object
    Dim myState As String
    Dim t As Integer, r As Integer, c As Integer
    Dim elemCollection As Object
    Dim WebPageFieldName As String
    
    Set IE = CreateObject("InternetExplorer.Application")
    
    WebPageFieldName = InputBox("field name")
    
    Set mydb = CurrentDb
     Set sourceRs = mydb.OpenRecordset("baz_export_vinStems_Peugeot", dbOpenDynaset)
    sourceRs.MoveFirst
    
    With IE
    
    .Visible = True
    .navigate (url)
    End With
    While IE.ReadyState <> 4
        DoEvents
    Wend
    
    'find field so that I can enter value
    For Each obj In IE.Document.All.Item("mrefarg1").Options
    
        If obj.innertext = "mrefarg1" Then
            Debug.Print "found"
          
        End If
    
    Next obj
    
'    insert value into field name here
    'then submit
    IE.Document.getElementsByName("Retrieve").Item.Click

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40409850
what is a VIN that we can use to see what's going on
0
 
LVL 39

Expert Comment

by:thenelson
ID: 40409971
Open the source for the webpage you are trying to get data from (In IE, right click on the page, select view page source). Search the source for the field you want to capture and find its id value. Then use something like this:
Dim myString as String
myString = IE.Document.getElementById("theFieldIDValueHere").Value

This database will show you how to work with interfacing with the web:
http://www.thenelson.name/#WebAccess
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:PeterBaileyUk
ID: 40410064
I wanted to insert a value in that field. I thought but am not sure as I dont understand the html that it was this "mrefarg1"

https://www.3xmotorcycles-superstore.com/vehicle-search
0
 

Author Comment

by:PeterBaileyUk
ID: 40410073
Ok in the meantime I have downloaded the suggested db and have used it to look at the objects on the page. it appears the object appears twice its the first instance of that field that i need to insert a value and then have the button object clicked. the button also appears twice.
eeexample.JPG
0
 

Author Comment

by:PeterBaileyUk
ID: 40410102
A vin is a vehicle identification number, I have a list of these that i need to insert one by one into the first input field
0
 

Author Comment

by:PeterBaileyUk
ID: 40410120
i am guessing that i do something like:

IE.Document.getElementById("theFieldIDValueHere").Value = myvin

then force the button reprieve to click.
0
 

Author Comment

by:PeterBaileyUk
ID: 40410152
ok ive got to this which appears to work:
IE.Document.getElementById("mrefarg1").Value = "VGAS1B00B00077392"

cannot enforce click as i dont know which object it is

IE.Document.getElementById("object to submit").Item.Click

<FORM class=form_frame method=get action=/cms/cms.jsp><INPUT type=hidden value=5713 name=menu_id> <INPUT id=mrefarg1 name=mrefarg1 placeholder="By frame number..."> <INPUT type=submit value=Retrieve>
0
 
LVL 34

Expert Comment

by:ste5an
ID: 40410190
Analyse the page using Fiddler. Is there any JavaScript involved?

When no, then it's often easier to use XMLHTTP to post the necessary calls directly to the web server.
0
 

Author Comment

by:PeterBaileyUk
ID: 40410289
ok so from this site https://www.3xmotorcycles-superstore.com/vehicle-search how do i send the "vin" to the server and get the returned rows?
0
 

Author Comment

by:PeterBaileyUk
ID: 40410291
vin in the site is the frame number
0
 

Author Comment

by:PeterBaileyUk
ID: 40410294
in vba that is
0
 

Author Comment

by:PeterBaileyUk
ID: 40410354
Ok as I am not getting anywhere, I recorded a macro in excel using its data from web feature of me inputting a value into the field and then seeing what excel did. The macro after recording showed this:

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://www.3xmotorcycles-superstore.com/cms/cms.jsp?menu_id=5713&mrefarg1=VGAS2AB0030042237" _
        , Destination:=Range("$A$1"))
'        .CommandType = 0
        .Name = "cms.jsp?menu_id=5713&mrefarg1=VGAS2AB0030042237"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

Open in new window


I would prefer to do the same in access.
0
 

Author Comment

by:PeterBaileyUk
ID: 40410363
I just tested a couple of codes:

https://www.3xmotorcycles-superstore.com/cms/cms.jsp?menu_id=5713&mrefarg1=VGAS2AB0030042237
https://www.3xmotorcycles-superstore.com/cms/cms.jsp?menu_id=5713&mrefarg1=VGAA2ABJB40001746

VGAA2ABJB40001746 these values are in my recordset of which i am looping around
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;https://www.3xmotorcycles-superstore.com/cms/cms.jsp?menu_id=5713&mrefarg1=VGAS2AB0030042237" _
        , Destination:=Range("$A$1"))
'        .CommandType = 0
        .Name = "cms.jsp?menu_id=5713&mrefarg1=VGAS2AB0030042237"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

Open in new window

0
 
LVL 39

Expert Comment

by:thenelson
ID: 40410364
"ok ive got to this which appears to work:
IE.Document.getElementById("mrefarg1").Value = "VGAS1B00B00077392"

You are correct - that should fill the "Frame Number" field. I believe you are saying it is working.

"cannot enforce click as i dont know which object it is"

The tag that submits the information is:
<input type="submit" value="Retrieve">
This tag submits the form (there are 6 forms on the page separated by <form ...> and the <\form> tags).  
To submit the form, you would use:
IE.Document.Forms(FormNumber).submit
Where FormNumber starts with 0 for the first form, 1 for the second and so on.
It looks like the form you want to submit is the forth form on the page so you would use:
IE.Document.Forms(3).submit

So:
IE.Document.getElementById("mrefarg1").Value = "VGAS1B00B00077392"
IE.Document.Forms(3).submit
should do it but you may need to play with the form number to get it right.
0
 

Author Comment

by:PeterBaileyUk
ID: 40410371
ok thats cool just going to code now.
0
 

Author Comment

by:PeterBaileyUk
ID: 40410377
it gives automation error on the forms(3).submit line

    IE.Document.getElementById("mrefarg1").Value = VIN
   IE.Document.Forms(3).submit
0
 

Author Comment

by:PeterBaileyUk
ID: 40410384
This is the code so far
Sub Main()


    url = "https://www.3xmotorcycles-superstore.com/vehicle-search"
    Dim IE As Object, obj As Object
 
    Set IE = CreateObject("InternetExplorer.Application")
    

    Set mydb = CurrentDb
    Set sourceRs = mydb.OpenRecordset("baz_export_vinStems_Peugeot", dbOpenDynaset)
    
    sourceRs.MoveFirst
 

    Do

           vrr_vehicleId = sourceRs.vrr_vehicleId.Value
           VIN = sourceRs.vin_original_dvla.Value
           With IE
    
                .Visible = True
                .navigate (url)
           End With
                
           While IE.ReadyState <> 4
                    DoEvents
           Wend
    
 
        IE.Document.getElementById("mrefarg1").Value = VIN
        IE.Document.Forms(3).submit
   

        'go to next vin record
            sourceRs.MoveNext
    
    Loop
    

    
End Sub

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 40410387
It looks like .forms(2).submit works

so how do i grab the table data?
also I am assuming my code wont open a browser each time it will just refresh then push the value in.
Sub Main()


    url = "https://www.3xmotorcycles-superstore.com/vehicle-search"
    Dim IE As Object, obj As Object
 
    Set IE = CreateObject("InternetExplorer.Application")
    

    Set mydb = CurrentDb
    Set sourceRs = mydb.OpenRecordset("baz_export_vinStems_Peugeot", dbOpenDynaset)
    
    sourceRs.MoveFirst
 

    Do

           vrr_vehicleId = sourceRs.vrr_vehicleId.Value
           VIN = sourceRs.vin_original_dvla.Value
           With IE
    
                .Visible = True
                .navigate (url)
           End With
                
           While IE.ReadyState <> 4
                    DoEvents
           Wend
    
 
        IE.Document.getElementById("mrefarg1").Value = VIN
        IE.Document.Forms(3).submit
   

        'go to next vin record
            sourceRs.MoveNext
    
    Loop
    

    
End Sub

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 40410455
its working but not sure how to grab the table data
ive added an obeject called
Dim elemCollection As Object

done this:
Set elemCollection = IE.Document.getElementsByTagName("TABLE")

not sure how to grab the data within that object
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40410531
You can do this quicker/simpler with the MSXML2.XMLHTTP object
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40410567
In the attached database, I created a VINs table and a ModelColour table and the following routine.  I added two VIN data from earlier comments in this thread.
Sub Q_28545825()
    Dim rsVINS As Recordset
    Dim rsMC As Recordset   'ModelColour
    Dim oXMLHTTP As Object
    Dim strHTML As String
    Dim oRE As Object
    Dim oMatches As Object
    Dim oM As Object

    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "<tr>\s*<td>(\w[^<]*)</td>\s*<td>(\w[^<]*)</td>(?:.|\n)*?</tr>"

    Set rsVINS = DBEngine(0)(0).OpenRecordset("VINs", dbOpenTable)
    Set rsMC = DBEngine(0)(0).OpenRecordset("ModelColour", dbOpenTable)
    
    Do Until rsVINS.EOF
        Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")
        oXMLHTTP.Open "GET", "https://www.3xmotorcycles-superstore.com/cms/cms.jsp?menu_id=5713&mrefarg1=" & rsVINS!VIN, False
        oXMLHTTP.Send
        
        Do Until oXMLHTTP.ReadyState = 4
            DoEvents
        Loop
        
        If oXMLHTTP.Status = 200 Then
            strHTML = oXMLHTTP.responsetext
            'parse the result
            If oRE.test(strHTML) Then
                Set oMatches = oRE.Execute(strHTML)
                For Each oM In oMatches
                    'push the data into the modelcolour table
                    rsMC.AddNew
                        rsMC!VIN = rsVINS!VIN
                        rsMC!Model = oM.submatches(0)
                        rsMC!Colour = oM.submatches(1)
                    rsMC.Update
                Next
            End If
        End If
        
        rsVINS.MoveNext
    Loop
End Sub

Open in new window

Q-28545825.mdb
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question