CODEWITHSUNDEEP
vbaexcelexcel-2007
Sevi
Sevi

Reputation: 627

How to read excel file to content status

I would like read to excel file (this file is not open) "content status" with VBA macros. It is possible? I attached picture what I want to read.

Thanks for your help.

enter image description here

Upvotes: 2

Views: 435

Answers (2)

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

Code posted by Florent B works correctly. However, I didn't get the desired property on mentioned index i.e. 127. Instead I got it on 129 on my computer. I am not sure about its reason.

My suggestion is as below. Create a custom function.

Important: Set reference as indicated or else it will not work. I tried late binding but the code fails so this setting is critical.

Public Function GetExtendedProperty(strFolder As String, _
                                    strFile As String, _
                                    strProperty As String) As String
'\\ Set Reference to "Microsoft Shell Controls and Automation" Library (shell32.dll) in VBE
Dim wShell As Shell
Dim fld As folder
Dim itm As FolderItem
Dim i As Long

Set wShell = CreateObject("Shell.Application")
Set fld = wShell.Namespace(strFolder)
Set itm = fld.ParseName(strFile)

For i = 0 To 255
    If CStr(fld.GetDetailsOf(fld.Items, i)) = strProperty Then
        GetExtendedProperty = fld.GetDetailsOf(itm, i)
    End If
Next i

Set itm = Nothing
Set fld = Nothing
Set wShell = Nothing
End Function

Then it can be used like below in the actual code.

Sub Test()
Dim strComplStatus As String
strComplStatus = GetExtendedProperty("C:\Temp", "Book1.xlsx", "Content status")
MsgBox strComplStatus
End Sub

Upvotes: 1

nishit dey
nishit dey

Reputation: 458

Can you try the below code:

Dim fileProperty As Variant
For Each fileProperty In ActiveWorkbook.BuiltinDocumentProperties
   Debug.Print fileProperty.Name
Next fileProperty

I know this is not the full code, but you can also use .BuiltinDocumentProperties("Content Status")

Let me know if that works.

Upvotes: 1

Related Questions