|
本帖最后由 林木水 于 2022-1-6 22:22 编辑
新鲜出炉了,请注意查收,是否ok,思路:
1.使用WPS的filesearch函数,提取所有文件路径到数组arr
2.将数组arr分成文件夹和文件名、内存大小、修改时间四部分存入数组brr
3.使用工作表Hyperlinks.Add方法添加超链接也就是brr(,1)brr(,2)
4.注意事项:
1)当前文件打开的需要处理以下~$前缀,否则当前文件的超链接会出错
2)读取内存vba.filelen 读取字节个数除以1000就是kb
3)读取内存修改日期,vba.filedatetime
5.当然问题解决了,要给什么?
- Sub filesearch()
- Dim i As Long
- Dim arr()
- ActiveSheet.Range("b8:e65535").ClearContents
- With Application.filesearch
- .NewSearch
- .LookIn = ActiveSheet.Range("b5").Value
- ' .LookIn = "C:\Users\Acer\Desktop\搜索"
- .SearchSubFolders = True
- .Filename = ActiveSheet.Range("c1").Value & ActiveSheet.Range("c2").Value & ActiveSheet.Range("c3").Value
- If .Execute() > 0 Then
- ReDim arr(1 To .FoundFiles.Count, 1 To 1)
- For i = 1 To .FoundFiles.Count
- arr(i, 1) = .FoundFiles(i)
- Next i
- End If
- End With
- ReDim brr(1 To UBound(arr, 1), 1 To 4)
- For i = 1 To UBound(arr, 1)
- brr(i, 1) = Mid(arr(i, 1), 1, VBA.InStrRev(arr(i, 1), "") - 1)
- brr(i, 2) = Mid(arr(i, 1), VBA.InStrRev(arr(i, 1), "") + 1, Len(arr(i, 1)))
- If VBA.InStr(brr(i, 2), ThisWorkbook.Name) Then
- brr(i, 2) = VBA.Replace(brr(i, 2), "~$", "")
- arr(i, 1) = VBA.Replace(arr(i, 1), "~$", "")
- End If
- brr(i, 3) = VBA.FileDateTime(arr(i, 1))
- brr(i, 4) = Round(VBA.FileLen(arr(i, 1)) / 1000, 2) & "kb"
- Next i
- ActiveSheet.Range("b8").Resize(UBound(arr, 1), 4) = brr
- For i = 1 To UBound(arr, 1)
- Sheet3.Hyperlinks.Add Range("b8").Offset(i - 1, 0), brr(i, 1)
- Sheet3.Hyperlinks.Add Range("b8").Offset(i - 1, 1), brr(i, 2)
- Next i
- End Sub
复制代码
|
|