DIR无法正常运行

马特

我正在使用VBA将.txt文件中的数据导入电子表格的表格中,以用于进一步的数据透视图。我从中导入文件的网络目录包含约5500个文件,并且随着时间的流逝,目前每年增长约2000个文件。表中的条目按日期排序(从最旧到最新)。

我有一个宏,它检查最新条目的日期,然后使用DIR搜索网络位置并遍历该目录中的文件。对于每个文件,如果该文件比最新条目要新,我想导入数据并将其添加到表中。如果文件较旧,我希望DIR移至下一个文件。以下是我当前正在使用的代码。

Sub NewFilesFromNetwork()

Dim myDatabase As Worksheet
Set myDatabase = Sheets("Database")

Dim TotalRows As Long, LastDate As Date

TotalRows = myDatabase.ListObjects("Table1").Range.Rows.Count
LastDate = Cells(TotalRows + 48, 6).Value 'the "+48" here is important because there are 48 hidden rows at the top of the spreadsheet before the table starts

Dim MyFolder As String, MyFile As String

On Error Resume Next
Application.ScreenUpdating = False

MyFolder = "*path to my network location*" 
MyFile = Dir(MyFolder & "*.txt")

Dim t As Integer, k As Integer
t = 0 'counter for calculating total files imported
k = 0 'counter for calculating total files checked

Do While MyFile <> ""
    TxtFile = MyFolder & MyFile
    If FileDateTime(TxtFile) > LastDate Then 
        Open TxtFile For Input As #1 
        Do Until EOF(1)
            Line Input #1, textline
            text = text & textline
        Loop
        Close #1
        Call CommonImportCode 'separate sub which picks out information from the .txt file string and adds it to the table as a new entry
        k = k + 1
        t = t + 1
        MyFile = Dir()
    End If

        k = k + 1
        MyFile = Dir()
Loop

Application.ScreenUpdating = True

MsgBox "Number of files searched = " & k & vbNewLine & "Number of files imported = " & t

End Sub

我遇到的问题是这样的:

我可以检查网络位置,看看有10个新文件。但是,宏仅导入其中的5个,并且似乎仅导入新文件中的所有其他文件。当宏满足IF语句的条件时,它们会跳过文件吗?

gazzz0x2z
    k = k + 1
    MyFile = Dir()

该代码是重复的。如果上面的“ If”为true,则表示您正在跳过一个文件。您的循环应为:

Do While MyFile <> ""
    TxtFile = MyFolder & MyFile
    If FileDateTime(TxtFile) > LastDate Then 
        Open TxtFile For Input As #1 
        Do Until EOF(1)
            Line Input #1, textline
            text = text & textline
        Loop
        Close #1
        Call CommonImportCode 'separate sub which picks out information from the .txt file string and adds it to the table as a new entry
        t = t + 1
    End If
    k = k + 1
    MyFile = Dir()
Loop

或即将到来的东西。

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章