Note: I am limited to PHP <-> VBA. Please do not suggest anything that requires an Excel Addon, or any other language/method.
I have a function that connect to a specified URL, submits data, and then retrieves other data. This works great. I'm trying to write it so i can use it as a generic function I can use to connect to any file I need to connect to - each would return different data (one could be user data, one could be complex calculations etc).
When it retrieves the data from PHP, is there a way to dynamically set the variables based on what is received - even if i do not know what has been received.
I can make PHP return to VBA the string in any format, so I'm using the below as an example:
String that is received in vba:
myValue1=Dave&someOtherValue=Hockey&HockeyDate=Yesterday
If i were to parse this in PHP, I could do something similar to (not accurate, just written for example purposes);
$myData = "myValue1=Dave&someOtherValue=Hockey&HockeyDate=Yesterday"
$myArr = explode("&",$myData)
foreach($myArr as $key => $value){
${$key} = $value;
}
echo $someOtherValue; //Would output to the screen 'Hockey';
I would like to do something similar in VBA. The string I am receiving is from a PHP file, so I can format it any way (json etc etc), I just essentially want to be able to define the VARIABLES when outputting the string from PHP. Is this possible in VBA?.
The current state of the function I have that is working great for connections is as below:-
Function kick_connect(url As String, formdata)
'On Error GoTo connectError
Dim http
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "POST", url, False
http.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
http.send (formdata)
kick_connect = http.responseText
Exit Function
connectError:
kick_connect = False
End Function
Ultimately, I want to be able to do something like
sub mySub
myData = "getId=" & Range("A1").Value
myValue = kick_connect("http://path-to-my-php-file.php",myData)
if myValue = False then
'Handle connection error here
exit sub
end if
'do something snazzy here to split "myValue" string (eg "myValue1=Dave&someOtherValue=Hockey&HockeyDate=Yesterday") into own variables
msgbox(myValue1) 'Should output "Dave"
end sub
Obviously I could put the values into an array, and reference that, however I specifically want to know if this exact thing is possible, to allow for flexibility with the scripts that already exist.
I hope this makes sense, and am really grateful for any replies i get.
Thank you.
foo("myValue1")
or return XML and wrap DOM access in a class. – Alex K. Apr 24 at 10:24