[RESOLVED] Display Excel Document Properties in Cells
Hello:
Supposedly there is a way within Excel VBA to write a function like this...
Code:
Private Function DocProp(Info_needed As String) As Variant
Application.Volatile
DocProp = ThisWorkbook.BuiltinDocumentProperties(Info_needed).Value
End Function
Or this...
Code:
Private Function DocProp(Info_needed As String) As Variant
Application.Volatile
DocProp = ThisWorkbook.CustomDocumentProperties(Info_needed).Value
End Function
And be able to then define a cell in Excel to =DocProp("Property Name")
This needs to be a module. How is the module, module1 accessed, because when I do this I get nothing.
Thank you!
Re: Display Excel Document Properties in Cells
some of the property values (unassigned date/time values) may cause an error, so will need to be handled
i tested you function, it worked fine, obviously only properties with assigned values will return anything, the only values returned on my test are author, application and creation date, the rest returned nothing or an error
you can test like
Code:
c = 1
On Error Resume Next
For Each p In ThisWorkbook.BuiltinDocumentProperties
Cells(1, c) = p.Name
Cells(2, c) = p.Value
If Not Err.Number = 0 Then Cells(2, c) = "error"
Err.Clear
c = c + 1
Next
Quote:
How is the module, module1 accessed
as long as it is in the same workbook, excel will find it
Re: Display Excel Document Properties in Cells
It needs to be a Public function, not a Private one though, correct?
Re: Display Excel Document Properties in Cells
Quote:
It needs to be a Public function, not a Private one though, correct?
i tested as posted worked correctly as a private function, though i did consider it should be public
Re: Display Excel Document Properties in Cells
That's weird, in 2016 it doesn't expose the function to me when private. What version are you in, Pete?
2 Attachment(s)
Re: Display Excel Document Properties in Cells
Hello:
I changed it to a public function.
I would like to simply read in the property to the spreadsheet. It seems like it is writing something, because it is changing the value. I have tried both .BuiltinDocumentProperties and .CustomDocumentProperties.
I have attached two pics. The first one shows my goal, to just do =DocProp("property name"). I am not looking to have users code much here.
Note, the Project name property did have a value until I entered =DocProp("Project name") into the field, and then it changed.
To reiterate, here is the code:
Code:
Public Function DocProp(Info_needed As String) As Variant
Application.Volatile
DocProp = ThisWorkbook.CustomDocumentProperties(Info_needed).Value
End Function
Attachment 148975
Attachment 148973
Thank you.
Re: Display Excel Document Properties in Cells
Can you zip and attach? What you're attempting is working for me, so I can't figure out why it would be writing anything, rather than simply reading.
Re: Display Excel Document Properties in Cells
I got this figured out. There was a link in the property.
Thanks for all the help!
Re: Display Excel Document Properties in Cells
Quote:
Originally Posted by
ssabc
I got this figured out. There was a link in the property.
Thanks for all the help!
I have the exact same issue here. Could you please tell me exactly where was the link with the property that did not work? I feel like it would help me a lot!
Thanks.
Re: [RESOLVED] Display Excel Document Properties in Cells
GDE2017:
My apologies, I do not remember the situation completely. I do believe there was a link in the actual document property that was preventing the code from populating the data I wanted.
Perhaps click through all the properties and make sure the Link to content option is not selected.
I hope this helps. June was a long time ago for me, and I cannot even locate the document referenced with the question.