VBA循环通过目录 [英] VBA loop through directory

问题描述

**嗨所有，

我将在下面的脚本中加入搜索文件的能力，并仅导出文件夹中最新文件中的数据。我将每周添加一个新的文件到文件夹，所以不希望复制旧的数据范围。

I would to incorporate into the below script the ability to search through files and export ONLY the data from the most recent file in folder. I will be adding a new file every week into folder so do not want the old data range to be copied across.

有人可以帮忙吗？**

Can someone please help?**

Sub loopthroughdirectory()
Dim myfile As String
Dim erow
fileroot = "C:\Users\ramandeepm\Desktop\consolidate\"
myfilename = Dir("C:\Users\ramandeepm\Desktop\consolidate\")

Do While Len(myfilename) > 7

    If myfilename = "zmaster.xlsm" Then
      Exit Sub
    End If

    myfile = fileroot & myfilename
    Workbooks.Open (myfile)
    Range("range").Copy
    ActiveWorkbook.Close

    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets("sheet1").Range(Cells(erow, 1), Cells(erow,       4))

    myfilename = Dir()

Loop

End Sub

推荐答案

如果您使用 FileSystemObject 可以使用 .DateLastModified 属性。以下代码应该让你开始：

If you use FileSystemObject it can be done using the .DateLastModified property. The below code should get you started:

未验证

Dim FSO As FileSystemObject
Dim objFile As File
Dim myFolder
Dim strFilename As String
Dim dtFile As Date

'set folder location
Const myDir As String = "C:\Users\ramandeepm\Desktop\consolidate"

'set up filesys objects
Set FSO = New FileSystemObject
Set myFolder = FSO.GetFolder(myDir)

'loop through each file and get date last modified. If largest date then store Filename
dtFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
    If Len(objFile.Name) > 7 Then
        If objFile.DateLastModified > dtFile Then
            dtFile = objFile.DateLastModified
            strFilename = objFile.Name
        End If
    End If
Next objFile
Workbooks.Open strFilename

注意：此代码正在寻找最新的修改日期。因此，只有在文件夹中其他文件中的任何修改之后创建了最新的文件后才会生效。另外，您可能需要启用 Microsoft Scripting Runtime 库参考。

Note: This code is looking for the most recent modified date. So this will only work if the newest file was created after any modifications in other files in the folder. Also, you may need to enable the Microsoft Scripting Runtime library reference.

这篇关于VBA循环通过目录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！