Home » 文章 » 电脑技术 » Excel » 如何批量提取文件名

如何批量提取文件名

如何批量提取文件名呢?如何批量提取指定文件夹里所有文件的文件名到Excel里?解决方法如下VBA代码

Sub extract()
Dim MyPath, MyName, AWbName
Dim WbN As String
Dim Num As Long
Application.ScreenUpdating = False

‘弹出文件夹选择框,选择地址
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show Then
MyPath = .SelectedItems(1)
Else
Exit Sub
End If
End With

‘MyPath = ActiveWorkbook.Path   ‘当前工作簿的地址
MyName = Dir(MyPath & “\” & “*.*”) ‘可改为指定格式
AWbName = ActiveWorkbook.Name
Num = 0
b1 = 0

Do While MyName <> “”
If MyName <> AWbName Then
Num = Num + 1
With Workbooks(1).ActiveSheet
b1 = .Range(“A65536”).End(xlUp).Row + 1
.Range(“A” & b1) = Left(MyName, Len(MyName) – 4)
WbN = WbN & Chr(13) & MyName
End With
End If
MyName = Dir
b1 = 0
Loop
Range(“A1”) = “文件名”

Range(“B1”).Select
Application.ScreenUpdating = True
MsgBox “共合并了” & Num & “个” & MyPath & “下的全部文件。如下:” & Chr(13) & WbN, vbInformation, “提示”
End Sub

微信 OR 支付宝 扫描二维码
为博主 打个赏
pay_weixinpay_zhifubao
金额随意 快来“打”我呀~

One comment

  1. 年后第一次来,恭喜恭喜!

Leave a Reply

Your email address will not be published. Required fields are marked *

*