excel宏-提取文件夹中所有文件及文件夹名称
宏-提取文件夹中所有文件及文件夹名称
Sub FSO_File_Extraction():
Call ExtractionFileAddHyperlinks(strFldPath) '调取文件提取及增加超链接的函数
Range("a:b").EntireColumn.AutoFit '自动列宽
Sheets("Sheet1").Select
Sheets("Sheet1").Copy
ActiveSheet.Shapes.Range(Array("Button 1")).Delete
ActiveWorkbook.SaveAs Filename:= _
strFldPath & "\文档目录.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.CloseApplication.ScreenUpdating = True '触发屏幕刷新操作
Range(“a:c”).ClearContents
Columns(“A:C”).ColumnWidth = 8.08
ThisWorkbook.Save
End Sub
Sub 文件提取()
'子函数
'ExtractionFileAddHyperlinks 如下图所示:
Function named ExtractionFileAddHyperlinks is defined by the parameter strFldPath of type String.
通过直接创建法生成F Sof 对象 objMyFSO = CreateObject(“Scripting.FileSystemObject”) '
遍历文件夹内的每个文件
lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
获取文件路径
使用instrrev函数获取最后文件夹名截至的位置
将获取的路径部分赋值给对应的单元格
将文件名部分赋值给对应的单元格
' 继续设置其他属性并添加超链接
Next objFile
Loop through each objSubFld in objFld.SubFolders '遍历文件夹内的子文件夹
Execute the function named ExtractionFileAddHyperlinks with argument objSubFld.Path '递归调用
End Loop
Initialize the relevant object variables (objMyFSO, objFld, objFile, and objSubFld) with the value of Nothing ' Release associated object resources
Assign appropriate data types to each variable using the Assign function.