(Excel) OneDrive -- Search for Files

See more OneDrive Examples

Search for files within a hierarchy of items for items matching a query. You can search within a folder hierarhcy or a whole drive.

Note: This example requires Chilkat v9.5.0.68 or greater.

Download Excel Class Modules Chilkat Excel Class Modules

' This example 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

' Use your previously obtained access token here:
' See the following examples for getting an access token:
'    Get Microsoft Graph OAuth2 Access Token (Azure AD v2.0 Endpoint).
'    Get Microsoft Graph OAuth2 Access Token (Azure AD Endpoint).
'    Refresh Access Token (Azure AD v2.0 Endpoint).
'    Refresh Access Token (Azure AD Endpoint).

' (Make sure your token was obtained with the FilesRead or Files.ReadWrite scope.)
http.AuthToken = "MICROSOFT_GRAPH_ACCESS_TOKEN"

' Sends the following GET request:

' Search the entire drive like this:
' If not using "me", then the path should be /v1.0/users/{id | userPrincipalName}/...
' GET /v1.0/me/drive/root/search(q='{search-text}')

' Or search under a particular folder like this:
' GET /v1.0/me/drive/root:/{item-path}:/search(q='{search-text}')

' This example will search for files in or under the "/Misc" directory, having "penguin" in the file path.
Dim success As Boolean
success = http.SetUrlVar("item_path","/Misc")
success = http.SetUrlVar("search_text","penguins")

Dim sbResults As Chilkat.StringBuilder
Set sbResults = Chilkat.NewStringBuilder

success = http.QuickGetSb("https://graph.microsoft.com/v1.0/me/drive/root:/{$item_path}:/search(q='{$search_text}')",sbResults)
If (http.LastMethodSuccess <> True) Then
    Debug.Print http.LastErrorText
    Exit Sub
End If

' The response should be JSON.
Dim json As Chilkat.JsonObject
Set json = Chilkat.NewJsonObject
json.EmitCompact = False
success = json.LoadSb(sbResults)

' A successful response should return a status code of 200.
If (http.LastStatus <> 200) Then
    Debug.Print json.Emit()
    Debug.Print "Response status = "; http.LastStatus
    Exit Sub
End If

Debug.Print json.Emit()
Debug.Print "--"

' Iterate over the results in the JSON response:
Dim lastMod As Chilkat.CkDateTime
Set lastMod = Chilkat.NewCkDateTime
Dim photoTaken As Chilkat.CkDateTime
Set photoTaken = Chilkat.NewCkDateTime

i = 0

numItems = json.SizeOfArray("value")
Do While i < numItems
    json.I = i
    Debug.Print "-- DriveItem "; (i + 1)
    Debug.Print "id: "; json.StringOf("value[i].id")
    Debug.Print "name: "; json.StringOf("value[i].name")
    Debug.Print "size: "; json.IntOf("value[i].size")

    ' Get the lastModifiedDateTime
    success = lastMod.SetFromTimestamp(json.StringOf("value[i].fileSystemInfo.lastModifiedDateTime"))

    ' Is this a folder?
    If (json.HasMember("value[i].folder") = True) Then
        Debug.Print "This is a folder with "; json.IntOf("value[i].folder.childCount"); " children"
    End If

    If (json.HasMember("value[i].file") = True) Then
        Debug.Print "This is a file."
        Debug.Print "SHA1 hash: "; json.StringOf("value[i].file.hashes.sha1Hash")
        Debug.Print "mimeType: "; json.StringOf("value[i].mimeType")
    End If

    If (json.HasMember("value[i].image") = True) Then
        Debug.Print "This is an image."
        Debug.Print "height: "; json.IntOf("value[i].image.height")
        Debug.Print "width: "; json.IntOf("value[i].image.width")
    End If

    If (json.HasMember("value[i].photo") = True) Then
        Debug.Print "This is a photo."
        success = photoTaken.SetFromTimestamp(json.StringOf("value[i].photo.takenDateTime"))
        Debug.Print "photo taken on "; photoTaken.GetAsRfc822(True)
    End If

    If (json.HasMember("value[i].audio") = True) Then
        Debug.Print "This is an audio file."
        Debug.Print "duration: "; json.IntOf("value[i].audio.duration")
    End If

    i = i + 1
Loop

' Sample JSON OneDrive search response:

' 	{
' 	  "@odata.context": "https://graph.microsoft.com/v1.0/$metadata#Collection(driveItem)",
' 	  "value": [
' 	    {
' 	      "@odata.type": "#microsoft.graph.driveItem",
' 	      "@microsoft.graph.downloadUrl": "https://public.dm2301.livefilestore.com/y4m01N6dLIV7ditztcRwWDx2Jzf1qiwJNASFiHfajfZUuJnEMgHmIZTASy3lNzwUFHQgET0I11hzqCyb0A6I5jhI9FgFAJlBJHFrToT7QXj2xvCV46LOZVJASk79LEWRRM97aOhdOHhKeIl_HxoIWSl08sX3kGyC50RP72rDE7ERSVdZAa3CmMNkp6tRtfKI5wfJNqSx3NeqLQ6LxwBBY4KgniPQOgEna0G_MKTGDt10T4",
' 	      "createdBy": {
' 	        "user": {
' 	          "displayName": "Matt Smith",
' 	          "id": "3a33fceb9b74cc15"
' 	        }
' 	      },
' 	      "createdDateTime": "2017-06-04T20:40:22.48Z",
' 	      "cTag": "aYzozQTMzRkNFQjlCNzRDQzE1ITQ4NzIuMjU3",
' 	      "eTag": "aM0EzM0ZDRUI5Qjc0Q0MxNSE0ODcyLjI",
' 	      "id": "3A33FCEB9B74CC15!4872",
' 	      "lastModifiedBy": {
' 	        "user": {
' 	          "displayName": "Matt Smith",
' 	          "id": "3a33fceb9b74cc15"
' 	        }
' 	      },
' 	      "lastModifiedDateTime": "2017-06-04T20:40:23.463Z",
' 	      "name": "penguins.jpg",
' 	      "parentReference": {
' 	        "driveId": "3a33fceb9b74cc15",
' 	        "id": "3A33FCEB9B74CC15!4871",
' 	        "name": "wildlife",
' 	        "path": "/drive/root:/Misc/wildlife"
' 	      },
' 	      "size": 777835,
' 	      "webUrl": "https://1drv.ms/i/s!ABXMdJvr_DM6pgg",
' 	      "file": {
' 	        "hashes": {
' 	          "sha1Hash": "DF7BE9DC4F467187783ACA68C7CE98E4DF2172D0"
' 	        },
' 	        "mimeType": "image/jpeg"
' 	      },
' 	      "fileSystemInfo": {
' 	        "createdDateTime": "2017-06-04T20:40:22.48Z",
' 	        "lastModifiedDateTime": "2009-07-14T05:32:31.674Z"
' 	      },
' 	      "image": {
' 	        "height": 768,
' 	        "width": 1024
' 	      },
' 	      "photo": {
' 	        "takenDateTime": "2008-02-18T05:07:31Z"
' 	      },
' 	      "searchResult": {
' 	        "onClickTelemetryUrl": "https://www.bing.com/personalsearchclick?IG=D10F15177AE54D49A97F361270D53290&CID=3A33FCEB9B74CC150000000000000000&ID=DevEx%2c5005&q=penguins&resid=3A33FCEB9B74CC15%214872"
' 	      }
' 	    }
' 	  ]
' 	}