Automate the Autopopulate document Properties in Cells of Excel Sheet

Most of the time we usually forget to update some of the document properties in the excel sheet which are linked with the document prperties like file name and file version if you are using the automated version control system.



Here is the tip to automate that
Open Excel sheet --> Press Alt + F11--> Insert New Module --> Paste Below Mentioned function

Module1
--------------------------------------------------------------
Function CusProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.CustomDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function


Module2
--------------------------------------------------------------
Function BinProps(prop As String)
Application.Volatile
On Error GoTo err_value
DocProps = ActiveWorkbook.BuildinDocumentProperties _
(prop)
Exit Function
err_value:
DocProps = CVErr(xlErrValue)
End Function

Now add functions in cells
Function to Insert File Name

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

Function to Insert the RCSRevision
=CusProps(“RCSRevision”)

Function to Insert Author
=BinProps(“Author”)