VBA Publish to SharePoint with Meta Data

JamesCbury used Ask the Experts™
Hello Experts, I've developed some code that generates a set of workbooks for gathering information from my users.  I've looking for a piece of code that will:
  A) publish each workbook to a SharePoint document library
  B) update some custom fields that have been added to my document library (Sector, Approver, Validate (Y/N)).

My code is currently set up to iterate through each workbook (i.e., creates, saves, and closes one WB at a time).  The code should also be set up to allow me to re-publish and overwrite any existing file with the same name.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Is genrated workbooks has different structure? or they base on one template?
You can create Sharepoint library which can contain one and more Excel templates. These template will be available throw "New" menu of library.
Any user can select what template use to create new workbook. After saving created from shrepoint library workbook will be published in the same library automatically.
Users can update custom fields throw "Edit Item" menu of published workbooks and so on.
For the what you need VBA code?


Hi ruslanin, These are not all standard templates.  Basically I have a Mapping spreadsheet which lists each component needed for each sector.  Based on that mapping my code generates unique workbooks for each sector (there are about 150 workbooks that will be generated).  Right now I have these created and saved on my c drive, but I would like to generate them directly in the SharePoint site so that I can update the custom field while the document is being saved.

I think I can figure out the saving part (that is just updating the file path in my VB string).  But I do not know how to update custom fields in SharePoint via VB...

Hope that helps.

Ok, i understood now.
Two advices
1. Try to use WebDAV path of sharepoint livrary when you will save generated workbooks to sharepoint.
2. If your code will be work locally on workstation (not on sharepoint server) you can update custom fields only using builin sharepoint web service. http://yoursharepointserver/vti_bin/lists.asmx
I'm not sure, but VBA not work with webservices directly as i remember.
But you can use web services from Office Add-Ons written on VB or C#
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Thanks for the suggestions, but that didn't really help me much.  I wound up just setitng my sharepoint directory path in the Save As property and looping through the ContentTypeProperties to set my custom list fields.  I added a snipit of the code if your interested

ActiveWorkbook.SaveAs Filename:=Share_Point_Directory & Title & ".xlsx", FileFormat:=51, CreateBackup:=False

For Each p In ActiveWorkbook.ContentTypeProperties
        If p.Name = "Sector" Then p.Value = My_Sector
        If p.Name = "Region" Then p.Value = My_Region
Next p

Open in new window


I was able to find an example of the contentTypeProperties online.  This turned out to be the best solution for what I'm trying to do.

How did you handle People types? Such as your approver?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial