Solved

Get data from webpage in Access

Posted on 2014-10-28
22
145 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
  • 16
  • 3
  • 2
  • +1
22 Comments
 

Author Comment

by:PeterBaileyUk
Comment Utility
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
Comment Utility
what is a VIN that we can use to see what's going on
0
 
LVL 39

Expert Comment

by:thenelson
Comment Utility
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
 

Author Comment

by:PeterBaileyUk
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
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
Comment Utility
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
Comment Utility
vin in the site is the frame number
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:PeterBaileyUk
Comment Utility
in vba that is
0
 

Author Comment

by:PeterBaileyUk
Comment Utility
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
Comment Utility
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
Comment Utility
"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
Comment Utility
ok thats cool just going to code now.
0
 

Author Comment

by:PeterBaileyUk
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
You can do this quicker/simpler with the MSXML2.XMLHTTP object
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now