Communicate Between Excel and PHP
I’m really enjoying the WinHTTP and Web Services (specifically REST) stuff. Even though I need to research quite a bit for each small thing I learn, and I’m just getting started, it’s well worth it.
Last night I figured out how to round trip a variable from VBA to PHP and back. In other words, I learned how to pass a VBA variable to a PHP function (residing in a file on the AutomateExcel server), manipulate the variable, then return the new result back to VBA.
Note: The variable is sent as a string.
For example, the message box in this picture was arrived at by:
1. Sending a variable holding the number 15 over HTTP to a PHP file on AutomateExcel
2. A PHP function takes the variable, Multiplies it by 500, then returns the answer, in this case 7500
3. VBA reads the response and returns the answer in a msgbox
Here’s what the PHP code looks like. Open notepad, copy and paste this info, save the file with the extension php, and upload to your server:
<?php $GetNumber=$_GET["PassThis"]; $NewNumber=$GetNumber*500; echo $GetNumber. " x 500 = " .$NewNumber; ?>
I didn’t return the answer in XML to keep the example simple. Here’s the VBA code to send and receive the data, change the connection string to point to the php file you just created:
' Add a reference to Microsoft WinHTTP Services Const HTTPREQUEST_SETCREDENTIALS_FOR_SERVER = 0 Private Sub GetItems() Dim MyCon As New WinHttpRequest Dim sendthis As Double Dim myanswer As String 'variable to send sendthis = 15 'Connection string to send MyCon.Open "GET", _ "http://www.automateexcel.com/pl/server500.php" & _ "?PassThis=" & sendthis 'send it MyCon.Send 'return it myanswer = MyCon.ResponseText MsgBox myanswer End Sub
Note: Add a reference to Microsoft WinHTTP Services