So “we must use Sharepoint – it is our corporate answer for everything” … Ok, but it is the wrong tool for … never mind – lets just get a solution and in doing so a lot of learning results … and ALOT did. Lets state that

• SharePoint assumes you have the document and its type (eg. pdf, xlsx etc.) BEFOREHAND – this is not our case
• a lot of programmatic ways of getting info into SharePoint have their nuances, advantages and disadvantages. Some are being phased out or already are

In our example we do NOT have the document before hand NOR do we know what the document from our vendors might be. It might a pdf, might be excel or might be word. We have a Document Library and the admin staff will be editing various dates (columns) long before the docs come in all the way. To start, this SharePoint placeholder list is auto-generated from an excel spreadsheet that states project, placeholder name,  code and how many of that item we should make in SharePoint .

Steps (links at bottom)

• SOAP: we start off with making a blank item with no doc using a SOAP web request using VBA in Excel
  • this creates a HIDDEN FILE in the windows explorer called 15_.000 (no you can’t EVER see it – but try to copy a file over-top of it and Explorer knows it is there. In SharePoint it is an unknown file type called 15_. This assumes that  your ID of your item is 15.
• You CANNOT rename a file to another type – so you can’t drag 15_.xls over-top of it – that makes a new item. You CANNOT rename the extension by hand. You CANNOT (easily) rename it in the explorer as username/password is required.
  • REST: Ahhhh – you can do a “move” using rest to rename
• Auto-rename. Make a web page where the user can drag the new file onto a target. The app at the far end can a) rename the file and then upload it over top of the renamed file. To do this we must make a web-link (calculation column) . Oddly enough to make a web link – change the type to numeric – then it is clickable
  • in my case I made a popup window by adding some JavaScript that responds when the link is clicked. Add a “Script” webpart with <Script>…</script> nodes
• Now that we have SharePoint – we use it as a data source in Excel and we can do some stats to see the progress of the documents vs. the schedule (what docs have arrived, have they arrived on time, how many are past their deadline etc. )

Resources implicitly used above

• characters to escape in SharePoint field name
• Xpath w. SharePoint return xml and VBA (MSXML)
  • Using the debug mode – go the hard way and dig for your node – find out its namespace. Then follow the example in this article and use xpath=//a:myNode – magic!
• REST
  • CRUD with VBA (another, )  , POST example, getting REST reference in VBA, link I used in VBA
  • Files/Folders REST for Sharepoint,
  • Filerename/MoveTo example, another link – one I used
  • Get List Item
• Sharepoint DateFormat in VBA (convert date format)
• Getting xml content OUT of your SharePoint Doc Library and into a file and load it into Excel. Don’t do this. There are other ways in Excel to do this. Excel messes up the XML import if you just “File->Open” the xml from a sharepoint file. Under Excel’s data ribbon – there are data import tools. Record a macro to learn the VBA
  • Other links
    • retreive sharepoint list library as xml
    • how to get al items in a view using rest-api
    • caml query for REST
    • json formatter
    • how to fetch data from xml file to excel using vba
    • pull data into ms excel with web query
• I never got this working but there are REST clients to test Sharepoint. I found this a major pain and rolled my own in VBA