excel中用vba实现自动提取文件夹内的文件名的方法如下:
1、新建一个vba宏脚本
2、写入如下代码:
Function GetFileList(FileSpec As String) As Variant
' Returns an array of filenames that match FileSpec
' If no matching files are found, it returns False
Dim FileArray() As Variant
Dim FileCount As Integer
Dim FileName As String
On Error GoTo NoFilesFound
FileCount = 0
FileName = Dir(FileSpec)
If FileName = "" Then GoTo NoFilesFound
' Loop until no more matching files are found
Do While FileName <>""
FileCount = FileCount + 1
ReDim Preserve FileArray(1 To FileCount)
FileArray(FileCount) = FileName
FileName = Dir()
Loop
GetFileList = FileArray
Exit Function
' Error handler
NoFilesFound:
GetFileList = False
End Function
3、传入文件路径就可以获取文件名到指定的excel表格中
4、结果:
针对ThisWorkbook对象的BeforeClose事件添加如下代码
Private Sub Workbook_BeforeClose(Cancel As Boolean)Dim fileStr As String
fileStr = "$111.xls$333.xls$“ ‘头尾都用$分隔,循环判断文件名
For I = Workbooks.count To 1 Step -1
If InStr(fileStr, Workbooks(I).Name) <> 0 Then
Workbooks(I).Close False ‘False是不保存关闭 true是保存并关闭
End If
Next
End Sub
关闭时就会遍历所有打开的excel文件,执行不保存 *** 作,并关闭
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)