We need to periodically migrate Excel reports data into Drupal nodes. We looked at replicating some Excel functionality in Drupal with slickgrid, but it wasn't up to snuff. The Excel reports people don't want to double-enter their data, but their data is important to be in this Drupal site.
They have hundreds of Excel reports, and update a row in each weekly. We want a button at the row end to fire a VBA macro that submits the data to Drupal, where a new node is created from the info submitted. (Yes, we are experienced with both Drupal and VBA; all users and the site are behind our firewall.) We need the new node's nid or URL returned so we can then create a link in Excel directly to that node
Site is D6, using Services 3.x module. I tried the REST server module, but we can't get it to retrieve data without session authentication on, which we can't do from Excel. (unless you can?) I also noticed the 'data' it was returning via browser url was 14 or 20 nodes' info, not the one nid requested (Example: http://mysite.com/services/rest/report/node/30161)
When I attempt to create a simple node like this from VBA:
Dim MyURL as String
MyURL = "http://mysite.com/services/rest/report/node?node[type]=test&node[title]=testing123&node[field_test_one][0][value]=123"
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
With objHTTP
.Open "GET", MyURL, False
.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
.send (MyURL)
End With
I get HTTP Status: Unauthorized: Access denied for user 0 "anonymous" and HTTP Response: null
Everything I search for has examples in php or java, nothing in VBA. Also tried switching to using an XMLRPC server but that's even more confusing. We would like json (used application/json, set formatter accordingly in REST server settings), but will use anything that works.
Ideas? Thanks in advance!
EDIT: I changed POST to GET in the .Open, and tried the different request headers (json, xml, etc.) I still get 14 nodes of basic node data returned, not my single node I requested. I guess this proves my REST server is working at the URL I specified, but I still don't know how to POST a node to Drupal from Excel.
Another Edit: GET and DELETE are working, just not POST or PUT !?! As anon user in browser, I can paste into address bar the 'http://mysite.com/node/add/test' as normal, it prompts me for just the title, and I can anonymously create it with no problem. Submitted via Excel results in the 'Unauthorized: Access denied for user 0 "anonymous"' error. So I can view and delete but cannot create? I double-checked my perms are set correctly.
