Internet communication via Excel VBA

HTTP communication is a typical method for exchanging data with external computers/servers over the Internet. The coding is done using javascript, but it is also possible to write it using Excel VBA. The residence application management system used by the office: NICRAS also uses Excel VBA when transmitting residence application data using the Mynaportal API and when acquiring data from the NICRAS server.

The following is the simplest coding example.

001 Set objXmlHttp = CreateObject("MSXML2.XMLHTTP") 
002 objXmlHttp.Open "GET","<endpoint>", False
003 objXmlHttp.setRequestHeader "X-Access-Key", i_access_key
004 objXmlHttp.send
005 lStrResponse = objXmlHttp.responseText

001.Declare an object for http communication.

If you get a compilation error, check that Micosoft XML X.X is referenced in the reference settings.

002.Open communication to the destination.

003.Write the request header. (Depends on the API specifications of the connection destination)

004.Send the data. When sending using the POST method, set the parameters here. When using the GET method, set it to the endpoint’s query string.

005.The return value from the destination computer/server is stored in .responseText.

Japan Immigration News