Results 1 to 10 of 10

Thread: [RESOLVED] Display Excel Document Properties in Cells

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Resolved [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!

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  3. #3
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Display Excel Document Properties in Cells

    It needs to be a Public function, not a Private one though, correct?

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  5. #5
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    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?

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    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
    Name:  2017-06-27_8-08-43.jpg
Views: 1608
Size:  40.1 KB
    Name:  2017-06-27_8-10-18.jpg
Views: 1589
Size:  51.9 KB


    Thank you.

  7. #7
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    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.

  8. #8

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    Re: Display Excel Document Properties in Cells

    I got this figured out. There was a link in the property.

    Thanks for all the help!

  9. #9
    New Member
    Join Date
    Oct 2017
    Posts
    1

    Re: Display Excel Document Properties in Cells

    Quote Originally Posted by ssabc View Post
    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.

  10. #10

    Thread Starter
    Fanatic Member
    Join Date
    Mar 2013
    Location
    Minneapolis, MN
    Posts
    531

    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
  •  



Click Here to Expand Forum to Full Width