Microsoft 365 WinSearch VBA | PowerPivot et/ou PowerQuery

Sub test_simple_B()
'Dans VBA Menu "Tools/References..."
'Cocher "Microsoft ActiveX Data Object 2.8 Library"
Dim t
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
objRecordset.Open "SELECT System.ItemName, System.DateModified FROM SYSTEMINDEX WHERE DIRECTORY='file:C:\Users\STAPLE\Documents'", objConnection
j = 1
Redim t(1 to Quoi?, 1 to 2)

Do Until objRecordset.EOF
    t(j, 1) = objRecordset.Fields.Item("System.ItemName")
    t(j, 2) = Format(objRecordset.Fields.Item("System.DateModified"), "dd/mm/yyyy hh:mm:ss")
    objRecordset.MoveNext
    j = j + 1
Loop
Cells(1).Resize(UBound(t, 1), UBound(t, 2)).Value = t
End Sub

Private Sub CommandButton1_Click()
'Dans VBA Menu "Tools/References..."
'Cocher "Microsoft ActiveX Data Object 2.8 Library"
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
objRecordset.Open "SELECT System.ItemName, System.DateModified FROM SYSTEMINDEX WHERE DIRECTORY='file:C:\Users\STAPLE\Documents'", objConnection
While objRecordset.EOF = False
        UserForm1.ListBox1.AddItem objRecordset.Fields(0)
        objRecordset.MoveNext
Wend
End Sub

Private Sub CommandButton1_Click()
'Dans VBA Menu "Tools/References..."
'Cocher "Microsoft ActiveX Data Object 2.8 Library"
Dim vArr
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
objRecordset.Open "SELECT System.ItemName, System.DateModified FROM SYSTEMINDEX WHERE DIRECTORY='file:C:\Users\STAPLE\Documents'", objConnection
vArr = objRecordset.GetRows()
With ListBox1
    .ColumnCount = 2
    .ColumnWidths = "300;300"
    .List() = Application.Transpose(vArr)
End With
End Sub

t = objRecordset.GetRows()

' Dans VBA Menu "Tools/References..."
' Cocher "Microsoft ActiveX Data Object 2.8 Library"
Sub testt()
Dim a
a = W_SearchIDX("C:\Users\STAPLE\Documents", "xlsx")
Cells(1).Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub
Private Function W_SearchIDX(strPath As String, strExt As String) As Variant
Dim objConnection As Object, objRecordset As Object, vArr
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
' Ouverture de la connexion
objConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
' Format de la requête avec LIKE pour extension et nom partiel
objRecordset.Open _
"SELECT System.ItemName, System.DateModified FROM SYSTEMINDEX WHERE DIRECTORY='file:" & strPath & "' " & "AND System.ItemName LIKE '%." & strExt & "%'", objConnection ' Adaptation du filtre d'extension
vArr = objRecordset.GetRows()
W_SearchIDX = Application.Transpose(vArr)
objRecordset.Close
objConnection.Close
End Function

"WHERE System.ItemPathDisplay LIKE '" & Chemin & "%' " & _

"WHERE DIRECTORY='file:" & Chemin & "' " & _

Sub test_OK()
'Staple1600 - 12/10/24
Dim strPath$, objConnection As Object, objRecordset As Object, vArr As Variant, ta_b
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
strPath = Environ("USERPROFILE") & "\Documents"
objConnection.Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
objRecordset.Open "SELECT System.ItemPathDisplay, System.ItemTypeText, System.Size FROM SystemIndex WHERE SCOPE='file:" & strPath & "'", objConnection
Application.ScreenUpdating = False
vArr = objRecordset.GetRows()
ta_b = Transpose2dim(vArr)
Cells(1).Resize(UBound(ta_b, 1), UBound(ta_b, 2)).Value = ta_b
objRecordset.Close
objConnection.Close
End Sub
Private Function Transpose2dim(t)
'|>patricktoulon<|
ReDim tb(LBound(t) To UBound(t, 2), LBound(t) To UBound(t))
    For c = 0 To UBound(t, 2)
        For lig = 0 To UBound(t)
            tb(c, lig) = t(lig, c)
        Next
    Next
Transpose2dim = tb
End Function

S
Sub test_simple()
Dim Sql As String
 
    ' Construire la requête SQL avec SCOPE
    Sql = "SELECT System.ItemName, System.ItemPathDisplay, System.DateModified, System.Size " & _
          "FROM SYSTEMINDEX WHERE SCOPE='file:" & CreateObject("Shell.Application").Namespace(&H5&).Self.Path & "'"
Debug.Print Sql
    With CreateObject("ADODB.Connection")
        .Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';" ' Ouvrir la connexion
        ' Exécuter la requête SQL
        ActiveCell.CopyFromRecordset .Execute(Sql)
            ' Utiliser GetRows pour obtenir les résultats dans un tableau
        .Close
    End With  
End Sub

Sub test_simple_BIS()
'-> dysorthographie
Dim SqlB As String
SqlB = "SELECT System.ItemName, System.ItemPathDisplay, System.DateModified, System.Size" & vbCrLf & _
      "FROM SYSTEMINDEX" & vbCrLf & _
      "WHERE SCOPE = '" & CreateObject("Shell.Application").Namespace(&H5&).Self.Path & "' and System.FileExtension = '.xlsx'"
Debug.Print SqlB
    With CreateObject("ADODB.Connection")
        .Open "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';" ' Ouvrir la connexion
        ' Exécuter la requête SQL
        ActiveCell.CopyFromRecordset .Execute(SqlB)
            ' Utiliser GetRows pour obtenir les résultats dans un tableau
        .Close
    End With
End Sub