0

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?

  • Please double check the SharePoint internal names for your columns. "Report Output File" is often referred as Report%5Fx0020%5FOutput%5Fx0020%5FFile in SharePoint. To get the exact internal name, open the list/ library settings and click on the required column name to edit it. Then inspect the browser URL, where you will see the column's internal name after "Field=" section. – UBK May 14 at 20:02
  • Here is the URL as requested from the library. .../Field=Report%5Fx0020%5FOutput%5Fx0020%5FFile I see that you are correct in how the url is formatted, but how would that affect how I set the metadata in my Excel file. – John McCord May 15 at 0:56
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.