Sharepoint Document Library Hyperlink Metadata Using Excel VBA

Question

I have VBA code written for my Excel document. This code saves the workbook in Sharepoint site and sets the metadata for the document library that the workbook is saved into.

Working code to set meta data for Excel workbook.

CustomerNumber = Range("C1").Value
ActiveWorkbook.ContentTypeProperties("Customer Number").Value = CustomerNumber

All of the metadata types are working except for the Hyperlink type. Whenever I try to set this metadata field I get a Run-time error '91'

This is what my code looks like to set the Hyperlink metadata

ReportOutputFile = path & Filename & ".pdf"
ActiveWorkbook.ContentTypeProperties("Report Output File").Value = ReportOutputFile

What am I missing? What is the correct way to set the Hyperlink Metadata from a VBA script in Excel?

Answer 1

VBA code is not able to find out the actual column since SharePoint updates the internal name of fields with spaces (" ") by replacing spaces with "x0020".

Hence, the best practice is to create the fields/ column names without spaces and then update it later to your desired name. Like, create it as "ReportOutputFile" and then update it as "Report Output File", in this case, SharePoint will retain the original internal name as "ReportOutputFile" and you can refer to it using "ReportOutputFile" everywhere. But, we have past that stage and need to find the solution with the current name :)

If you use a HTML decoder to decode the URL "/Field=Report%5Fx0020%5FOutput%5Fx0020%5FFile", then it would look like "/Field=Report_x0020_Output_x0020_File"

So, you can see, the internal name is "Report_x0020_Output_x0020_File", and you have to refer to it by this name only.

Please check one more time by updating your code like below:

ReportOutputFile = path & Filename & ".pdf"
ActiveWorkbook.ContentTypeProperties("Report_x0020_Output_x0020_File").Value = ReportOutputFile

Let me know if this works.