Excel VBA-运行时错误'53':找不到文件.但是找到了文件 [英] Excel VBA - Run-time error '53': File not found. But file was found

问题描述

我有一个Excel工作表，该工作表从充满.txt文档的文件夹中提取数据.上周星期五，它起作用了.没有改变.本周一，我收到运行时错误'53':找不到文件.

I have an Excel sheet that pulls data from a folder full of .txt documents. Last week Friday, it worked. Nothing changed. This week Monday, I get a Run-time error '53': File not found.

有趣的是，当我单击调试"时，它突出显示了代码中的一行，当我将鼠标悬停在"sFile"变量上时，它告诉我显然找不到的文件名..，但是它只能知道它的名称，如果它找到了....是的，我已经验证过，该文件确实存在.

What's interesting, is that when I click "Debug" it highlights a line in my code, and when I mouse over the 'sFile' variable, it tells me the name of the file that it apparently can't find... but it could only know the name of it if it found it... And yes, I've verified, that file does exist.

Excel工作表位于H:\ My Documents \ Loma CW3 Reports \

The Excel sheet is in H:\My Documents\Loma CW3 Reports\

.txt数据文件位于H:\ My Documents \ Loma CW3 Reports \ Product Statistics \

The data .txt files are in H:\My Documents\Loma CW3 Reports\Product Statistics\

它应该提取的前三个文件是:

The first 3 files that it should be pulling are:

- PR20180912T153019.txt
- PR20180913T070005.txt
- PR20180913T153002.txt

就像上面提到的，当我调试代码并将鼠标悬停在打开输入为#1的sFile"行中时，它会告诉我:

Like mentioned above, when I'm debugging the code and mouse-over "sFile" in the line "Open sFile For Input As #1", it tells me:

sFile = "PR20180912T153019.txt"

由于我不对其中的任何文件名进行硬编码，因此它只能知道它是否已成功扫描了文件夹.

Which it could only know if it was successfully scanning the folder since I don't hardcode any of those file names in.

我尝试删除该文件，将文件重命名为"apple"之类的词，然后检查该文件是否变为只读(不).我在这里被圈了一下，因为它像上周一样工作，而本周打开并尝试它后，一切都没有改变.

I have tried removing that file, renaming the file to a word like 'apple', checked to see if it became read-only (nope). I'm thrown for a loop here, because it worked as is last week, and nothing changed from when I opened it up this week and tried it.

以下代码:

Private Sub CommandButton1_Click()

' Dim myFile As String
Dim text As String, textLine As String
Dim sFile As String, rowTarget As Long

rowTarget = 2

' myFile = Application.GetOpenFilename()

sFile = Dir("H:\My Documents\Loma CW3 Reports\Product Statistics\" & "*.txt*")
Do Until sFile = ""
    Open sFile For Input As #1
    Do Until EOF(1)
        Line Input #1, textLine
        text = text & textLine
    Loop
    Close #1

    Do stuff here

    rowTarget = rowTarget + 1
    sFile = Dir()
    text = ""
Loop
End Sub

推荐答案

我最终将目录指定为单独的变量，并在打开文件时将sFile名称附加到该变量上.

I ended up specifying directory as a separate variable and appended the sFile name to it when opening the file.

Dim directory As String

directory = "H:\My Documents\Loma CW3 Reports\Product Statistics\"
sFile = Dir(directory & "*.txt*")

Do Until sFile = ""
    Open (directory & sFile) For Input As #1
    blah blah blah

感谢@comintern

Thanks @comintern

这篇关于Excel VBA-运行时错误'53':找不到文件.但是找到了文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！