Search code examples
vbapowerpoint

PowerPoint VBA Is It Possible To Send POST requests?


I want to send scores obtained in an educational PowerPoint game to an online mysql database.

My question is, is this possible to do?

I can see that it looks possible when using Excel VBA, but I would be grateful to know can this be done using PowerPoint VBA and if so how should I approach this?

Thank you for any comments.

EDIT - I thought I would update here as the code can be set out nicely compared to the comment boxes. I have experimented with the below code and have found that I can post to my database, but the problem is that only blank values are being inserted in the database.

Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")

URL = "http:mydomain/AddNew.php?"
objHTTP.Open "POST", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla / 4.0 ( compatible; MSIE 6.0; Windows NT 5.0); "
    objHTTP.send "name=test,&score=50"

I would be grateful for any advice on the correct syntax to use for the name and score to be inserted into my database. Also, how do I send the data using variables?

Thank you very much for any comments.

EDIT - a commenter said that I need to include my php code (use in AddNew.php). So I have copied this in below.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="en-gb" http-equiv="Content-Language" />
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Untitled 1</title>
</head>

<body>

<?php


$name=$_POST['name'];
$score=$_POST['score'];
echo '$name is '.$name;
echo '$score is '.$score;

if( $_REQUEST["name"] || $_REQUEST["score"] ) {
  echo "name ". $_REQUEST['name']. "<br />";
  $name = $_REQUEST['name'];
  echo "Your score ". $_REQUEST['score']. " points.";
  $score = $_REQUEST['score'];
  //exit();
}


if ($_SERVER["REQUEST_METHOD"] == "POST") {
  // collect value of input field
  $name = $_POST['name'];
  if (empty($name)) {
    echo "Name is empty";
  } else {
  echo $name;
}
$score = $_POST['score'];
  if (empty($score)) {
  echo "Score is empty";
  } else {
  echo $score;
  }
}

$servername = "my server name";
$username = "my username";
$password = "my password";
$dbname = "my database name";


try {
$conn = new PDO("mysql:host=$servername;dbname=mydatabasename", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}

try {
$conn = new PDO("mysql:host=$servername;dbname=mydatabasename", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO mytablename (id, name, score)
VALUES ('1', '$name', '$score')";
// use exec() because no results are returned
$conn->exec($sql);
echo "New record created successfully";
} catch(PDOException $e) {
echo $sql . "<br>" . $e->getMessage();
}


 $conn = null;
?>

</body>
</html>

EDIT - another edit! Below is the result of the objHTTP.ResponseText. Looking at the 3rd line from the bottom you can see that my AddNew.php is not receiving anything for the name and score!

<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta content="en-gb" http-equiv="Content-Language" />
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Untitled 1</title>
</head>

<body>

$name is $score is Name is emptyScore is emptyConnected successfullyNew record created successfully
</body>
</html>

Solution

  • Good news - I found a solution - I needed to change my setRequestHeader which I believe specifies that I am using a form request and I also separately worked out how to send variables. The below code should be as follows:

    'below are some dummy variables to test and send
    Dim name As String
    Dim score As Integer
    name = "John Doe"
    score = 43
    
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    
    URL = "my URL/AddNew.php?"
    objHTTP.Open "POST", URL, False
    'objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)" 'I do not seem to need this line!
    objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
    
    objHTTP.send ("name=" + name + "&score=" + CStr(score))