MTS to AVI Converter: Display Excel properties in spreadsheets [Solved]

Thursday, September 29, 2016

Display Excel properties in spreadsheets [Solved]

Display Excel properties in spreadsheets

In Excel 2003, click File > Properties > Summary, you can see such properties as Title, Subject, Author, Manager, Company, etc.

How to display these properties in spreadsheets? For example, I want to A1 to display the Title, A2 to display the Subject, A3 to display the Author.
And if I change the file properties, each time I open the file, the properties displayed in the spreadsheets
will also be updated automatically.

How to achieve it?

Solutions to the Problem Display Excel properties in spreadsheets

Download SmartPCFixer to Fix It (Free)

Open the VBA Editor (Alt/F11)
Double click ThisWorkbook in the project explorer in the left column.
Copy and paste the following code into the blank area of the VBA editor on the right.
Note the comment to edit to your worksheet name.
Close the VBA Editor (Cross with red background top right.)
Save the workbook.
Open the workbook and see if it works.
(Updates each time the workbook is opened)

Private Sub Workbook_Open()
With Sheets("Sheet1") 'Edit to your sheet name
    .Range("A1") = ThisWorkbook.BuiltinDocumentProperties("Title")
    .Range("A2") = ThisWorkbook.BuiltinDocumentProperties("Subject")
    .Range("A3") = ThisWorkbook.BuiltinDocumentProperties("Author")
End With
End Sub
To find all of the available properties if more are required, Insert a standard module.
(With VBA editor open select menu item Insert -> Module.)
Copy the following code into the standard module.
Note where you need to edit to insert your required sheet name for the list.
(Suggest you use an empty sheet).
Run the code from the VBA editor.
(Click any where within the sub and then press F5 to run)
Check out the worksheet for the list of available properties.

Sub AllProperties()
'Will display the list of available workbook properties
Dim docProp As DocumentProperty
Dim lngRow As Long
lngRow = 1
With Worksheets("Sheet3") 'Edit to required sheet name
    For Each docProp In ActiveWorkbook.BuiltinDocumentProperties
        .Cells(lngRow, 1).Value = docProp.Name
        lngRow = lngRow + 1
    Next
    .Columns("A:A").Columns.AutoFit
End With
End Sub

The following added after original post.
A UDF (User Defined Function) could have been used in lieu of the code in ThisWorkbook but I am not sure how knowledgeable you are with Excel and VBA so have kept it simple for the moment.
If you want to use a UDF then feel free to get back to me for more
help if you need it.

Access the files on your computer via MY COMPUTER
Under the C: drive, access the WINDOWS folder
Select the SOFTWARE DISTRIBUTION folder, followed by the DOWNLOAD folder.
Delete any files within this download folder.
If you are unable to delete these files, open the command prompt (CMD) as the administrator, and type "net stop wuauserv" into the CMD prompt window. Press ENTER. This should allow you to delete the files from step 4.
Once the files are deleted, open and run the CMD as the administrator and type "wuauclt.exe /updatenow". Press ENTER.

Another Safe way to Repair the Problem: Display Excel properties in spreadsheets:

How to Fix Display Excel properties in spreadsheets with SmartPCFixer?

1. Click the button to download SmartPCFixer . Install it on your system. Run it, and it will scan your computer. The errors will be shown in the list.