使用VBA循环文件夹中的文件? [英] Loop through files in a folder using VBA?
问题描述
我想使用循环浏览目录的文件vba 。
在循环中,我需要
- 文件名,以及
- 格式化文件的日期。
如果文件夹不超过50个文件,我已经编码了以下工作,否则它是可笑的慢(我需要它使用> 10000个文件夹)。这个代码的唯一问题是查找 file.name
的操作需要非常多的时间。
代码正常但是waaaaaay太慢(每100个文件15秒):
作为对象,MySource作为对象,文件作为变量
设置MySource = MyObj.GetFolder(c: \testfolder\)
对于每个文件在MySource.Files
如果InStr(file.name,test)> 0然后
MsgBoxfound
Exit Sub
End If
下一个文件
End Sub
解决问题
- 我的问题已经通过下面的解决方案解决了
Dir
以特定方式(15000个文件为20秒),并使用命令FileDateTime
检查时间戳。 - 考虑到从20秒以下的另一个答案减少到不到1秒。
这是我作为一个功能的解释:
'################################# ####################################
'#LoopThroughFiles
'#函数循环当前目录中的文件并返回文件名
'#用法:LoopThroughFiles ActiveWorkbook.Path,txt'inputDirectoryToScanForFile
'#https://stackoverflow.com/questions/10380312/loop-through -files-in-a-folder-using-vba
'################################ #############################
函数LoopThroughFiles(inputDirectoryToScanForFile,filenameCriteria)As String
Dim StrFile As String
'Debug.Printin LoopThroughFiles。inputDirectoryToScanForFile:,inputDirectoryToScanForFile
StrFile = Dir(inputDirectoryToScanForFile&\ * & filenameCriteria)
Do While Len(StrFile)> 0
Debug.Print StrFile
StrFile = Dir
循环
结束函数
I would like to loop through the files of a directory using vba in Excel 2010.
In the loop, I will need
- the filename, and
- the date at which the file was formatted.
I have coded the following which works fine if the folder has no more then 50 files, otherwise it is ridiculously slow (I need it to work with folders with >10000 files). The sole problem of this code is that the operation to look up file.name
takes extremely much time.
Code that works but is waaaaaay too slow (15 seconds per 100 files):
Sub LoopThroughFiles()
Dim MyObj As Object, MySource As Object, file As Variant
Set MySource = MyObj.GetFolder("c:\testfolder\")
For Each file In MySource.Files
If InStr(file.name, "test") > 0 Then
MsgBox "found"
Exit Sub
End If
Next file
End Sub
Problem solved:
- My problem has been solved by the solution below using
Dir
in a particular way (20 seconds for 15000 files) and for checking the time stamp using the commandFileDateTime
. - Taking into account another answer from below the 20 seconds are reduced to less than 1 second.
Here's my interpretation as a Function Instead:
'#######################################################################
'# LoopThroughFiles
'# Function to Loop through files in current directory and return filenames
'# Usage: LoopThroughFiles ActiveWorkbook.Path, "txt" 'inputDirectoryToScanForFile
'# https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba
'#######################################################################
Function LoopThroughFiles(inputDirectoryToScanForFile, filenameCriteria) As String
Dim StrFile As String
'Debug.Print "in LoopThroughFiles. inputDirectoryToScanForFile: ", inputDirectoryToScanForFile
StrFile = Dir(inputDirectoryToScanForFile & "\*" & filenameCriteria)
Do While Len(StrFile) > 0
Debug.Print StrFile
StrFile = Dir
Loop
End Function
这篇关于使用VBA循环文件夹中的文件?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!