-
Jun 26th, 2017, 10:22 AM
#1
Thread Starter
Fanatic Member
[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!
-
Jun 26th, 2017, 04:26 PM
#2
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
How is the module, module1 accessed
as long as it is in the same workbook, excel will find it
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 26th, 2017, 05:43 PM
#3
Re: Display Excel Document Properties in Cells
It needs to be a Public function, not a Private one though, correct?
-
Jun 27th, 2017, 02:45 AM
#4
Re: Display Excel Document Properties in Cells
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jun 27th, 2017, 08:21 AM
#5
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?
-
Jun 27th, 2017, 08:27 AM
#6
Thread Starter
Fanatic Member
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
Thank you.
-
Jun 27th, 2017, 10:00 AM
#7
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.
-
Jun 27th, 2017, 10:33 AM
#8
Thread Starter
Fanatic Member
Re: Display Excel Document Properties in Cells
I got this figured out. There was a link in the property.
Thanks for all the help!
-
Oct 9th, 2017, 10:24 AM
#9
New Member
Re: Display Excel Document Properties in Cells
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.
-
Oct 10th, 2017, 10:27 AM
#10
Thread Starter
Fanatic Member
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|