-5

I'm working on building a local php page to output the public Company info from Facebook in XML with help from a friend and I am going to create a VBA macro to webcrawl to that page and download the information into my workbook in Excel. However, the input for the company name must be from a cell on the spreadsheet and I don't know how to pass this into the request I am sending to the Graph API besides writing that straight into the PHP file. How do I make VBA pass the value from the spreadsheet cell (eg. A1=Hilton) to PHP to replace the hard-coded company name so that I can use this for any company?

Clarification of Requirements:

  1. VBA Reads company name from cell A1
  2. VBA sends company name as variable to PHP
  3. PHP post to Graph API using the company name sent from VBA
  4. VBA Macro crawls local PHP page and downloads the XML output into workbook
Community
  • 1
  • 1
Jeremy Friedman
  • 7
  • 1
  • 4
  • [What have you tried?](http://mattgemmell.com/2008/12/08/what-have-you-tried/) – Matt Feb 26 '13 at 21:27
  • The PHP should be finished tomorrow, so I haven't actually had a chance to try some different methods to send it. I was hoping that the code in this thread might be of some use, but I'm not sure how to apply it to my particular problem. http://stackoverflow.com/questions/6312780/sending-data-from-excel-to-server-using-http-post – Jeremy Friedman Feb 26 '13 at 21:32
  • 4
    At least break up the question to clearly show the order of operations. Step 1: VBA macro crawls page and inserts into excel. Step 2: PHP grabs value from excel and posts to Graph API. Step 3: ??? Step 4: PROFIT!!! Do you want to send the data directly to PHP or do you want PHP to read from the excel spreadsheet? – Matt Feb 26 '13 at 21:37
  • I see. I'll clarify the question in a moment so it's easier to read :). I would like to send the data directly to my local server running the PHP page since the output has to be in excel. – Jeremy Friedman Feb 26 '13 at 21:41
  • In the simplest of explanations, you can hit script.php?value1=whatever&value2=whatever Within your script you can access these values, sanitize them, and send them on to the Graph API. If you can do it by directly writing to the PHP file, then where you might be getting stuck is grabbing the variables from the querystring. `$value1=$_GET['value1];` might be what you're looking for. A clear question with clear examples of what you've tried will help your question tremendously. – Matt Feb 26 '13 at 21:44
  • If all you need to do is post the company name to a url, you could also just try using a hyper link Set the formula in cell A1 to be something like =HYPERLINK("http://www.domain.com?companyname=name", "start crawl") though this would need the user to click the link. – Sam Plus Plus Feb 27 '13 at 03:01

1 Answers1

1

You could use a VBA macro like I created below. In my example I made the macro tied to a button click

Sub Button1_Click()
    Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
   URL = "http://www.yourdomain.com/page.php?variable=" & ActiveWorkbook.Worksheets(1).Range("A1").Value
   objHTTP.Open "GET", URL, False
   objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
   objHTTP.send ("")


End Sub

Your PHP script on your server would look like

<?php

$excelCellA1Value = $_GET["variable"];

//Work magic here such as posting to Facebook API

//TO DO: Profit!


?>
Sam Plus Plus
  • 4,381
  • 2
  • 21
  • 43
  • 1
    The VBA uses POST but the PHP uses GET. The actual call uses GET syntax so presumably the VBA is in error? – barrowc Feb 27 '13 at 23:08
  • barrowc, while having post would still work, you are correct using GET would be the correct way of doing it. I updated the VBA snippet. Nice catch. – Sam Plus Plus Feb 28 '13 at 02:47
  • So, I've tried to put this in my excel workbook and run it. When I load the PHP page on my local machine I just get an error that it can't find the variable. Notice: Undefined index: variable in C:\xampp\htdocs\Facebook Project\index.php on line 2 – Jeremy Friedman Mar 02 '13 at 17:42
  • Nevermind...I'm retarded and didn't copy paste the code and put parens instead of square brackets. Working just fine now :). I ended up recording a macro of adding data from the web instead of using a XMLHTTP object for the web request. – Jeremy Friedman Mar 06 '13 at 06:58