|
发表于 2019-10-5 19:50
来自手机
|
显示全部楼层
本帖最后由 youzhenhappy 于 2019-10-5 21:27 编辑
Sub 提取文件名()
Dim Fso As Object, Folder_0 As Object, Folder_1 As Object, File As Object
Dim NWkb As Workbook
Dim KeyArr
Dim FileNameArr() As String
Dim FilePaths As String, FileNames As String, Path As String, FolderPath As String
Dim NWkbName As String
Dim i As Long, j As Long
Set objFD = Application.FileDialog(msoFileDialogFolderPicker)
With objFD
.InitialFileName = ActiveWorkbook.Path
If .Show = -1 Then
Path = .SelectedItems(1) '如果单击了确定按钮,则将选取的路径保存在变量中
Else
End '否则结束程序
End If
End With
Set Fso = CreateObject("scripting.filesystemobject") '创建FSO对象
Set Folder_0 = Fso.GetFolder(Path)
Set D = CreateObject("Scripting.Dictionary") '创建字典
'--------------------获取选择路径下,子文件夹路径及其文件名-----------------
i = 0
For Each Folder_1 In Folder_0.SubFolders '遍历根文件夹下的文件
FolderPath = Folder_1.Path
For Each File In Folder_1.Files
FileNames = FileNames & File.Name & "\"
Next
FileNames = Left(FileNames, Len(FileNames) - 1)
'MsgBox FileNames
If Not D.Exists(FolderPath) Then '判断关键字是否存在
D.Add FolderPath, FileNames
End If
FileNames = ""
Next
'--------------------------------------------------------------------------
'-------------------------获取选择路径下,文件名---------------------------
FileNames = ""
For Each File In Folder_0.Files
FileNames = FileNames & File.Name & "\"
Next
FileNames = Left(FileNames, Len(FileNames) - 1)
If Not D.Exists(Path) Then '判断关键字是否存在
D.Add Path, FileNames
End If
'--------------------------------------------------------------------------
KeyArr = D.keys
For i = LBound(KeyArr) To UBound(KeyArr)
Set NWkb = Workbooks.Add
NWkbName = Right(KeyArr(i), Len(KeyArr(i)) - InStrRev(KeyArr(i), "\"))
NWkb.SaveAs KeyArr(i)
NWkb.Worksheets("Sheet1").Name = NWkbName
NWkb.Worksheets(NWkbName).Cells(1, 2) = "名称"
NWkb.Worksheets(NWkbName).Cells(1, 1) = "序"
FileNameArr = Split(D(KeyArr(i)), "\")
For j = LBound(FileNameArr) To UBound(FileNameArr)
With NWkb.Worksheets(NWkbName)
.Cells(j + 2, 2) = FileNameArr(j)
.Cells(j + 2, 1) = j + 1
End With
Next j
NWkb.Save
NWkb.Close
Next i
End Sub
|
|