Excel 2000: how to run a list of VINs through Carfax?

[jfoster]

Aloha,

I just learned about this fantastic site. I would like to know is there is a way (preferably on the simple side) to take a column of VIN numbers (vehicle identification numbers) and copy and paste them one by one into the CARFAX database to find out the 1) Year/Make/Model and 2) Engine Type?

The idea would be that I copy a VIN such as:
1G4HR52K1WH472105

and paste it into
http://www.cardetective.com/vin-lookup.html

and then paste the 2 answers:
1) 1998 BUICK LESABRE LIMITED
2) 3.8L V6 SFI 12V

back into two corresponding columns in Excel 2000.

Your help is much appreciated.

Mahalo,
john

 

[NateO]

Welcome to the board, John.

Odd, you have the same question as this poster (although you provide a better explanation):
http://www.mrexcel.com/board2/viewtopic.php?t=161083

They posted the same question within the last 10 minutes as well...

In any case, here's an example of pushing data to a website and pulling a return value:
http://www.mrexcel.com/board2/viewtopic.php?t=139085

 

[NateO]

Ah, easier, just navigate to your VIN, e.g.,

http://www.carfax.com/cfm/check_order.cfm?vin=1G4HR52K1WH472105

Wait for that, then start pulling data from the resulting table.

 

[jfoster]

Nate,

Thanks for your help. The answer may be right in front of me, but I am still not clear how to replicate the same query for 1325 different VINs.

 

[NateO]

You're welcome,

Where are these VIN's? A range (worksheet cells)? An array? Just concatenate the VIN into the last URL I provided while navigating.

E.g.,

.navigate "http://www.carfax.com/cfm/check_order.cfm?vin=" & VinNumber

This will require VBA.

 

[usurper4]

In this case, I haven't found a way to do it automatically on this type of site...if anybody has some suggestions, I'd love to hear them!

 

[jfoster]

Yes, a range. Here they are....E2:E1327

1FMZU73E0YZA70564
JT3GN86R920226954


Edited by Nate: No need to post all 1,326 of them, the Range is fine.

 

[NateO]

In this case, I haven't found a way to do it automatically on this type of site...if anybody has some suggestions, I'd love to hear them!

Okay, by popular demand.

Try the following:

Sub foobar()
Dim ie As Object, cl As Range
Dim i As Long, j As Long, tmpStr As String
Dim strArr(0 To 1) As String

Set ie = CreateObject("InternetExplorer.Application")
On Error GoTo errHandler

For Each cl In Range("e2:e200")
    With ie
        .navigate "http://www.carfax.com/cfm/check_order.cfm?vin=" & _
            cl.Value
        Do While .busy: DoEvents: Loop
        Do While .ReadyState <> 4: DoEvents: Loop
        Do While Not CBool(InStrB(1, .document.URL, _
            "PopUpStatus"))
            DoEvents
        Loop
        Do While .ReadyState <> 4: DoEvents: Loop
        Let tmpStr = .document.body.innerText
        
        Let i = InStr(1, tmpStr, "Year/Make/Model:") + 17
        Let j = InStr(1, tmpStr, "Body Style:") - 3
        strArr(0) = Trim$(Mid$(tmpStr, i, j - i))
        
        Let i = InStr(1, tmpStr, "Engine Type:") + 12
        Let j = InStr(1, tmpStr, "Manufactured In:") - 3
        strArr(1) = Trim$(Mid$(tmpStr, i, j - i))
    
    End With
    cl.Offset(, 1).Resize(, 2).Value = strArr
Next

errHandler:
ie.Quit: Set ie = Nothing
End Sub

Takes a list from e2:e200 and returns the two values, on the same row, in columns F & G. The site can be slow, by far your biggest bottle neck in performance.

It's only been given limited testing, but the chances are pretty good that you owe me a beer, here.