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 "POST", 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!