SharePoint Asked by John McCord on October 25, 2021
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?
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.
Answered by UBK on October 25, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP