vba - Excel 宏仅从今天创建的文件中读取输入

Question

我有一个以 txt 格式导出每日报告的应用程序。我有一个宏，可以从这些报告中提取某些数据行并将它们放入输出 xls 文件中。我的宏的输入目录目前是一个单独的文件夹，我手动将今天的报告移入该文件夹。

我希望我的宏能够只从默认报告文件夹中读取并且只读取以今天的日期创建的文件。

报告文件的命名规则如下:1101_16_16_AppServiceUser_YYYYMMDDhhmmssXXX.txt不确定文件名的最后 3 位数字代表什么，但它们始终是数字。

帮忙吗？

哇，太快了!谢谢...第一次使用stackoverflow。我想我应该包括提取数据并将其转储到 excel 的代码...这里是:

Sub PullLinesFromEPremisReport()
Dim FileName, PathN, InputLn As String
Dim SearchFor1, SearchFor2, OutpFile As String
Dim StringLen1, StringLen2 As Integer
Dim colFiles As New Collection
Dim bridgekey As String

PathO = "C:\Documents and Settings\GROMERO\Desktop\CM reconciliation\output\"
PathN = "C:\Documents and Settings\GROMERO\Desktop\CM reconciliation\input\"

FileName = Dir(PathN)

While FileName <> ""
    colFiles.Add (FileName)
    FileName = Dir

Wend

SearchFor1 = "BRIDGE KEY"

StringLen1 = Len(SearchFor1)


OutpFile = "RESULTS.xls"
Open PathO & OutpFile For Output As #2

For Each Item In colFiles
    Open PathN & Item For Input As #1
        Do Until EOF(1) = True
               Line Input #1, InputLn
               If (Left(LTrim$(InputLn), StringLen1) = SearchFor1) Then
                    bridgekey = InputLn
           End If

    Loop

Close #1
Next Item

Close #2
End Sub

Answer 1

Daniel 的回答是正确的，但是使用 FileSystemObject 需要几个步骤:

确保您有对“Microsoft Scripting Runtime”的引用:

然后，遍历目录中的文件:

Sub WorkOnTodaysReports()

'the vars you'll need
Dim fso As New FileSystemObject
Dim fldr As Folder
Dim fls As Files
Dim fl As File

Set fldr = fso.GetFolder("C:\Reports")
Set fls = fldr.Files

For Each fl In fls

    'InStr returns the position of the substring, or 0 if not found
    '    EDIT: you can explicitly use the reliable parts of your file name
    '    to avoid false positives
    If InStr(1, fl.Name, "AppServiceUser_" & Format(Now, "YYYYMMDD")) > 0 Then

        'Do your processing

    End If

Next fl

End Sub

编辑:所以我认为，根据您发布的代码，您可以将 PathN 发送到您想要的主 Reports 文件夹，然后只需修改您的 While 语句如下:

While FileName <> ""

    If InStr(1, FileName, "AppServiceUser_" & Format(Now, "YYYYMMDD")) > 0 Then

        colFiles.Add (FileName)

    End If

    FileName = Dir

Wend