Convert PHP to VBA

Question

I have a bit of PHP code to retrieve street (str), place (pl) given a (Dutch) postal code and house number.

<?php

	$pc		= "1234AB";		// vul een geldige postcode in 

	$hn		= 1;			// vul een geldig huisnummer in 

	$tv		= "a";			// vul een geldige toevoeging in of laat leeg 

	$ak		= "apikey";		// vul hier uw eigen key in (zie registratie hieronder). 

$getadrlnk = "https://bwnr.nl/postcode.php?pc=".urlencode($pc)."&hn=".urlencode($hn)."&tv=".urlencode($tv)."&tg=data&ac=pc2adres&ak=$ak"; $result = file_get_contents($getadrlnk); if ($result=="Geen resultaat.") {echo $result;} else { $adres = explode(";",$result); $str = $adres[0]; $pl = $adres[1]; $lat = $adres[2]; $lon = $adres[3]; $gm = $adres[4]; echo " straat : $str<br> plaats : $pl<br> lat : $lat<br> lon : $lon<br> googlemaps : $gm<br>"; } ?>

I would like to have something like that in my MS Access database. On a form the user provides a postal code and house number, and the street and place are presented in the form. Is possible to do this in VBA?

I am aware that I need an API-key in the final solution. I can arrange that, but need to be sure it can be done.

Answer 1

So, I did realize the code I pasted got mangled like it did. The .Send and the response are 2 distinct thing. Try:

Private Sub zoek_op_pc_nummer_Click()
    On Error GoTo Error_Handler
    Dim xmlhttp               As Object    'New MSXML2.XMLHTTP60
    Dim myurl                 As String

    Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
    myurl = "https://bwnr.nl/postcode.php?pc=3124RB&hn=60&tv=&tg=data&ac=pc2adres&ak=123456"
    xmlhttp.Open "GET", myurl, False
    xmlhttp.Send
    MsgBox (xmlhttp.responseText)

Error_Handler_Exit:
    On Error Resume Next
    If Not xmlhttp Is Nothing Then Set xmlhttp = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: zoek_op_pc_nummer_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

Answer 2

Thanks for your reply Daniel. Right now I am not at my computer. I will give it a try later this day.

Answer 3

Here, I found this basic example

Dim xmlhttp As New MSXML2.XMLHTTP60, myurl As String myurl = "http://requestb.in/15oxrjh1" //replace with your URL xmlhttp.Open "GET", myurl, False xmlhttp.Send MsgBox(xmlhttp.responseText)

I've you get the responseText you just do normal VBA parsing (left, right, split, ...)

Answer 4

Yes, it is possible. You can make a web cam and return the response for passing and use in your db without issue. I'm not at my computer right now, but I'm try to provide an example in the morning.

Answer 5

I would like to have something like that in my MS Access database. On a form the user provides a postal code and house number, and the street and place are presented in the form. Is possible to do this in VBA?

I am aware that I need an API-key in the final solution. I can arrange that, but need to be sure it can be done.

Hi Peter,

I "think" that there are possibilities to do that, but it still depends on many conditions.

Let us go back 10-15 years ago. You had a service on your site to download the Dutch postalcode database. At that time I used that service to update my local postalcode database. After that service ceased, I had to do my updates "manually".

In the meantime I developped a technique to make dynamical forms. These dynamical forms are just building blocks of code to tune one standarized underlying form. It can transpose unstructured data in a structured way, ready to import in Access tables. For the transposition however you also need a couple of string manipulation procedures.

This I use now to (semi)automatic update my postalcode database from internet, but also for a couple of other updates.

In my applications I still use my "own" postalcode database to find "Street" and "City" belonging to a certain "Postcode" and "Housenumber", because a number of the applications are not connected to internet. Just by placing the postalcode in the BE-map makes it available to the application.

If you want a simple one-time search, it should be possible to do after some experimentation. As I use only generalized forms in all my applications, a way must be found to translate this to a "standard" Access use.

Imb.