(Excel) SharePoint -- Get File Property

Demonstrates how to get a specific property of a SharePoint file. Any of the following properties can be retrieved:

Author: Gets a value that specifies the user who added the file.
CheckedOutByUser: Gets a value that returns the user who has checked out the file.
CheckInComment: Gets a value that returns the comment used when a document is checked in to a document library.
CheckOutType: Gets a value that indicates how the file is checked out of a document library. Represents an
ContentTag: Returns internal version of content, used to validate document equality for read purposes.
CustomizedPageStatus: Gets a value that specifies the customization status of the file. Represents an
ETag: Gets a value that specifies the ETag value.
Exists: Gets a value that specifies whether the file exists.
Length: Gets the size of the file in bytes, excluding the size of any Web Parts that are used in the file.
Level: Gets a value that specifies the publishing level of the file. Represents an
ListItemAllFields: Gets a value that specifies the list item field values for the list item corresponding to the file.
LockedByUser: Gets a value that returns the user that owns the current lock on the file.
MajorVersion: Gets a value that specifies the major version of the file.
MinorVersion: Gets a value that specifies the minor version of the file.
ModifiedBy: Gets a value that returns the user who last modified the file.
Name: Gets the name of the file including the extension.
ServerRelativeUrl: Gets the relative URL of the file based on the URL for the server.
TimeCreated: Gets a value that specifies when the file was created.
TimeLastModified: Gets a value that specifies when the file was last modified.
Title: Gets a value that specifies the display name of the file.
UiVersion: Gets a value that specifies the implementation-specific version identifier of the file.
UiVersionLabel: Gets a value that specifies the implementation-specific version identifier of the file.
Versions: Gets a value that returns a collection of file version objects that represent the versions of the file.

Download Excel Class Modules

Chilkat Excel Class Modules

'  This requires the Chilkat API to have been previously unlocked.
'  See Global Unlock Sample for sample code.

Dim http As Chilkat.Http
Set http = Chilkat.NewHttp

'  If SharePoint Windows classic authentication is used, then set the
'  Login, Password, LoginDomain, and NtlmAuth properties.
http.Login = "SHAREPOINT_USERNAME"
http.Password = "SHAREPOINT_PASSWORD"
http.LoginDomain = "SHAREPOINT_NTLM_DOMAIN"
http.NtlmAuth = True

'  The more common case is to use SharePoint Online authentication (via the SPOIDCRL cookie).
'  If so, do not set Login, Password, LoginDomain, and NtlmAuth, and instead
'  establish the cookie as shown at SharePoint Online Authentication

'  Indicate that we want a JSON reply
http.Accept = "application/json;odata=verbose"

'  Get the Author property.

url = "https://SHAREPOINT_HTTPS_DOMAIN/_api/web/GetFileByServerRelativeUrl('/Documents/VCAC-document.docx')/Author"


jsonReply = http.QuickGetStr(url)
If (http.LastMethodSuccess <> True) Then
    Debug.Print http.LastErrorText
    Exit Sub
End If

Dim json As Chilkat.JsonObject
Set json = Chilkat.NewJsonObject
Dim success As Boolean
success = json.Load(jsonReply)
json.EmitCompact = False

'  Make sure it was a success response, and that we really have metadata.
'  If it was an error response, then the JSON is error information..
If (http.LastStatus <> 200) Then
    Debug.Print json.Emit()
    Debug.Print "Failed."
    Exit Sub
End If

Debug.Print json.Emit()

'  The Author property returns JSON looking like this:

'  {
'    "d": {
'      "__metadata": {
'        "id": "Web/GetUserById(18)",
'        "uri": "https://SHAREPOINT_HTTPS_DOMAIN/_api/Web/GetUserById(18)",
'        "type": "SP.User"
'      },
'      "Groups": {
'        "__deferred": {
'          "uri": "https://SHAREPOINT_HTTPS_DOMAIN/_api/Web/GetUserById(18)/Groups"
'        }
'      },
'      "Id": 18,
'      "IsHiddenInUI": false,
'      "LoginName": "i:0#.w|mydomain\\msmith",
'      "Title": "Mike Smith",
'      "PrincipalType": 1,
'      "Email": "msmith@mydomain.com",
'      "IsSiteAdmin": false,
'      "UserId": {
'        "__metadata": {
'          "type": "SP.UserIdInfo"
'        },
'        "NameId": "s-1-5-21-3433503314-2897774614-343593928-1137",
'        "NameIdIssuer": "urn:office:idp:activedirectory"
'      }
'    }
'  }
' 

'  Get the Title and Email:
Debug.Print "----"
Debug.Print "Title: "; json.StringOf("d.Title")
Debug.Print "Email: "; json.StringOf("d.Email")
Debug.Print "----"

'  --------------------------------------------------------------------

'  Get the Versions property.
url = "https://SHAREPOINT_HTTPS_DOMAIN/_api/web/GetFileByServerRelativeUrl('/Documents/VCAC-document.docx')/Versions"

jsonReply = http.QuickGetStr(url)
If (http.LastMethodSuccess <> True) Then
    Debug.Print http.LastErrorText
    Exit Sub
End If

success = json.Load(jsonReply)
Debug.Print json.Emit()

'  The Versions JSON reply looks like this:

'  {
'    "d": {
'      "results": [
'        {
'          "__metadata": {
'            "id": "a8d025e2-8255-4487-9edb-9e796ab2889c",
'            "type": "SP.FileVersion"
'          },
'          "CreatedBy": {
'            "__deferred": {
'              "uri": "https://SHAREPOINT_HTTPS_DOMAIN/_api/CreatedBy"
'            }
'          },
'          "CheckInComment": "",
'          "Created": "2016-03-15T02:22:26Z",
'          "ID": 1,
'          "IsCurrentVersion": false,
'          "Size": 21082,
'          "Url": "_vti_history/1/Documents/VCAC-document.docx",
'          "VersionLabel": "0.1"
'        },
'        {
'          "__metadata": {
'            "id": "8ab3eadd-9126-4f65-a2a3-3b0689c592d6",
'            "type": "SP.FileVersion"
'          },
'          "CreatedBy": {
'            "__deferred": {
'              "uri": "https://SHAREPOINT_HTTPS_DOMAIN/_api/CreatedBy"
'            }
'          },
'          "CheckInComment": "",
'          "Created": "2016-03-15T05:28:24Z",
'          "ID": 2,
'          "IsCurrentVersion": false,
'          "Size": 21082,
'          "Url": "_vti_history/2/Documents/VCAC-document.docx",
'          "VersionLabel": "0.2"
'        }
'      ]
'    }
'  }
' 

'  Get each VersionLabel, Size, and Url...

numVersions = json.SizeOfArray("d.results")

i = 0
Do While i < numVersions
    json.I = i
    Debug.Print "---- i = "; i
    Debug.Print "VersionLabel: "; json.StringOf("d.results[i].VersionLabel")
    Debug.Print "Url: "; json.StringOf("d.results[i].Url")
    Debug.Print "Size: "; json.IntOf("d.results[i].Size")
    i = i + 1
Loop

'  The output is:

'  ---- i = 0
'  VersionLabel: 0.1
'  Url: _vti_history/1/Documents/VCAC-document.docx
'  Size: 21082
'  ---- i = 1
'  VersionLabel: 0.2
'  Url: _vti_history/2/Documents/VCAC-document.docx
'  Size: 21082