Send data from VBA (excel) to php and vice versa

Question

Basically I want to communicate with a MYSQL server using php as the middle-ware.

I cannot connect directly to the database due to drivers needing to be installed and our IT will not allow drivers to be installed on our company computers.

I understand how to insert data using vba ->php->mysql but I cannot figure out how to get data from mysql to excel. vba->php->mysql->php->vba.

I want to do a query and send the results to vba excel.

Here is the VBA Code that works but it also sends multiple spaces back as well. Im not sure if this is the correct way to do this.

    Private Sub ExtractPHP()
    Dim item As String
    Dim objHTTP As Object
    Dim URL As String
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    URL = "http://localhost/php/requestdata.php"
    objHTTP.Open "POST", URL, False
    objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencode"
    objHTTP.send ("")
    Worksheets("hiddendata").Range("k4").Value = Replace(objHTTP.responseText, " ", "")
    End Sub

The PHP code is as follows.

    <?php 
    $con = mysqli_connect("localhost","root","","php") or die("Connection was not created!");
    $select = "select name FROM users where id=5";
    $run = mysqli_query($con,$select);
    $value = mysqli_fetch_array($run);
    $result = $value["name"];
    echo $result;
    ?>

Result that is placed in the excel file has multiple spaces below the text. Is this the correct way to communicate between excel and mysql?

score 0 · Answer 1 · answered Jan 01 '18 at 00:04

0

I am not sure about the VBA method and what exactly you need to do but one solution would be to write requestdata.php to parse the result of your query into a .csv file and then load that into Excel via VBA. Parsing the result array into the .csv plain text format is made even more trivial by the fact that PHP already has a function fputcsv($file, $records) to use (documentation).

answered Jan 01 '18 at 00:04

bboll

84
6

Thank you for your help. I really just need a single word or string sent back from php to vba/excel. My problem is that anything that is sent back has multiple spaces (return key) under the string. – Ma1sk1y Jan 02 '18 at 00:35
Apologies, I was too caught up trying to figure out the VBA portion and checking the PHP connection that I didn't notice you are just returning a single attribute—and already doing it successfully—which would make my solution a bit overkill in addition to being more of an indirect workaround. If you are getting the data you need and are just dealing with some trailing newline characters (most likely) then you should start with a var_dump on $result and make sure there aren't extra characters. I imagine you could trim them from VBA as well. – bboll Jan 02 '18 at 03:41

score 0 · Answer 2 · 2018-01-01T00:46:22.503

i can help you 10% cause i programming in several languages but nothing vba/excell .I programming in php too...anyway my answer for you is : Let say you create in C: a new folder and you create a runme.bat file there. Using one methods to run a external .bat file and to grab external result from that runme.bat more or less like in

Execute a command in command prompt using excel VBA

Wait for shell command to complete

you can use like me a method to run from that batch php cli:

you can drop runme.php in that folder as well:

<?php
   if(isset($argv[1])){
     echo($argv[1]);
    //here you can drop some mysql
   }
?>

then you should understand how to reconfiger php ini to connect to mysql ,some problems you can dare are PHP: mysql_connect() won't work via command line

if you put a command like this one i already check via windows cmd and works c:\xampp\php>php "c:\test\runme.php" yep2

and you are getting "yep2"

as c:\test\ 's runme.bat version :

rem @echo off
set parameter=%1
set PHP_BIN="c:\xampp\php\php.exe"
set script=%cd%\runme.php
%PHP_BIN%  %script% %parameter%

and for test we call it in cmd : runme yep2 with the result: yep2

then with the batch modiffied you can fill data in mysql let's say you will call that not runme.php but putmysqldata.php . at this point creating another file php to read from mysql (even you use only one file php to write or read by calling it by different parameter) so you will be able to read from mysql

so isn't easy if you don't know much vba or how to do it. following these steps i think if you know some vba more than me you can figure out the shortest way.

Send data from VBA (excel) to php and vice versa

2 Answers2