Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

Get data from webpage in Access

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
PeterBaileyUk
Asked:
PeterBaileyUk
  • 16
  • 3
  • 2
  • +1
1 Solution
 
PeterBaileyUkAuthor Commented:
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
 
aikimarkCommented:
what is a VIN that we can use to see what's going on
0
 
thenelsonCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
PeterBaileyUkAuthor Commented:
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
 
PeterBaileyUkAuthor Commented:
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
 
PeterBaileyUkAuthor Commented:
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
 
PeterBaileyUkAuthor Commented:
i am guessing that i do something like:

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

then force the button reprieve to click.
0
 
PeterBaileyUkAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
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
 
PeterBaileyUkAuthor Commented:
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
 
PeterBaileyUkAuthor Commented:
vin in the site is the frame number
0
 
PeterBaileyUkAuthor Commented:
in vba that is
0
 
PeterBaileyUkAuthor Commented:
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
 
PeterBaileyUkAuthor Commented:
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
 
thenelsonCommented:
"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
 
PeterBaileyUkAuthor Commented:
ok thats cool just going to code now.
0
 
PeterBaileyUkAuthor Commented:
it gives automation error on the forms(3).submit line

    IE.Document.getElementById("mrefarg1").Value = VIN
   IE.Document.Forms(3).submit
0
 
PeterBaileyUkAuthor Commented:
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
 
PeterBaileyUkAuthor Commented:
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
 
PeterBaileyUkAuthor Commented:
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
 
aikimarkCommented:
You can do this quicker/simpler with the MSXML2.XMLHTTP object
0
 
aikimarkCommented:
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

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 16
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now