Excel VBA - Run-time error '53': File not found. But file was found

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.

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.

The Excel sheet is in H:My DocumentsLoma CW3 Reports

The data .txt files are in H:My DocumentsLoma CW3 ReportsProduct Statistics

The first 3 files that it should be pulling are:

- PR20180912T153019.txt

- PR20180913T070005.txt

- PR20180913T153002.txt

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.

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.

Code below:

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 DocumentsLoma CW3 ReportsProduct 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

asked Nov 14 '18 at 19:03

Jonathon Loch

113

Put Debug.Print CurDir right above Open sFile. Are you in the correct directory? You really should be using a full path with Open (and also using FreeFile instead of hard coded file numbers).

– Comintern
Nov 14 '18 at 19:30

I'd vote for a timing issue. The file was created some (milli)seconds after your code was run

– peakpeak
Nov 14 '18 at 19:31

@Comintern I could be misunderstanding you, but aren't I using a full path with Open? sFile = Dir("H:My DocumentsLoma CW3 ReportsProduct StatisticsPR20180912T153019.txt.") followed by the "open sFile" line. I did the Debug.Print CurDir, it reads "H:My Documents"

– Jonathon Loch
Nov 14 '18 at 20:07

Ummm... No. Dir doesn't return the full path. You said yourself that sFile had a value of "PR20180912T153019.txt".

– Comintern
Nov 14 '18 at 20:12

Oh, I guess you're right. duh. haha I tried copying that "PR20180912T153019.txt" file to H:My Documents, and then it worked for that single document. So something is messing up my directories then? I did a Save As for the Excel doc, into the same folder it currently was. Now I'm getting CurDir = "H:My DocumentsLoma CW3 Reports"

– Jonathon Loch
Nov 14 '18 at 20:14

|
show 2 more comments

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.

The Excel sheet is in H:My DocumentsLoma CW3 Reports

The data .txt files are in H:My DocumentsLoma CW3 ReportsProduct Statistics

The first 3 files that it should be pulling are:

- PR20180912T153019.txt

- PR20180913T070005.txt

- PR20180913T153002.txt

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.

Code below:

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 DocumentsLoma CW3 ReportsProduct 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

asked Nov 14 '18 at 19:03

Jonathon Loch

113

Put Debug.Print CurDir right above Open sFile. Are you in the correct directory? You really should be using a full path with Open (and also using FreeFile instead of hard coded file numbers).

– Comintern
Nov 14 '18 at 19:30

I'd vote for a timing issue. The file was created some (milli)seconds after your code was run

– peakpeak
Nov 14 '18 at 19:31

@Comintern I could be misunderstanding you, but aren't I using a full path with Open? sFile = Dir("H:My DocumentsLoma CW3 ReportsProduct StatisticsPR20180912T153019.txt.") followed by the "open sFile" line. I did the Debug.Print CurDir, it reads "H:My Documents"

– Jonathon Loch
Nov 14 '18 at 20:07

Ummm... No. Dir doesn't return the full path. You said yourself that sFile had a value of "PR20180912T153019.txt".

– Comintern
Nov 14 '18 at 20:12

Oh, I guess you're right. duh. haha I tried copying that "PR20180912T153019.txt" file to H:My Documents, and then it worked for that single document. So something is messing up my directories then? I did a Save As for the Excel doc, into the same folder it currently was. Now I'm getting CurDir = "H:My DocumentsLoma CW3 Reports"

– Jonathon Loch
Nov 14 '18 at 20:14

|
show 2 more comments

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.

The Excel sheet is in H:My DocumentsLoma CW3 Reports

The data .txt files are in H:My DocumentsLoma CW3 ReportsProduct Statistics

The first 3 files that it should be pulling are:

- PR20180912T153019.txt

- PR20180913T070005.txt

- PR20180913T153002.txt

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.

Code below:

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 DocumentsLoma CW3 ReportsProduct 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

asked Nov 14 '18 at 19:03

Jonathon Loch

113

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.

The Excel sheet is in H:My DocumentsLoma CW3 Reports

The data .txt files are in H:My DocumentsLoma CW3 ReportsProduct Statistics

The first 3 files that it should be pulling are:

- PR20180912T153019.txt

- PR20180913T070005.txt

- PR20180913T153002.txt

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.

Code below:

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 DocumentsLoma CW3 ReportsProduct 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

excel vba

asked Nov 14 '18 at 19:03

Jonathon Loch

113

asked Nov 14 '18 at 19:03

Jonathon Loch

113

asked Nov 14 '18 at 19:03

Jonathon Loch

113

asked Nov 14 '18 at 19:03

Jonathon Loch

113

asked Nov 14 '18 at 19:03

Jonathon Loch

113

Put Debug.Print CurDir right above Open sFile. Are you in the correct directory? You really should be using a full path with Open (and also using FreeFile instead of hard coded file numbers).

– Comintern
Nov 14 '18 at 19:30

I'd vote for a timing issue. The file was created some (milli)seconds after your code was run

– peakpeak
Nov 14 '18 at 19:31

@Comintern I could be misunderstanding you, but aren't I using a full path with Open? sFile = Dir("H:My DocumentsLoma CW3 ReportsProduct StatisticsPR20180912T153019.txt.") followed by the "open sFile" line. I did the Debug.Print CurDir, it reads "H:My Documents"

– Jonathon Loch
Nov 14 '18 at 20:07

Ummm... No. Dir doesn't return the full path. You said yourself that sFile had a value of "PR20180912T153019.txt".

– Comintern
Nov 14 '18 at 20:12

Oh, I guess you're right. duh. haha I tried copying that "PR20180912T153019.txt" file to H:My Documents, and then it worked for that single document. So something is messing up my directories then? I did a Save As for the Excel doc, into the same folder it currently was. Now I'm getting CurDir = "H:My DocumentsLoma CW3 Reports"

– Jonathon Loch
Nov 14 '18 at 20:14

|
show 2 more comments

Put Debug.Print CurDir right above Open sFile. Are you in the correct directory? You really should be using a full path with Open (and also using FreeFile instead of hard coded file numbers).

– Comintern
Nov 14 '18 at 19:30

I'd vote for a timing issue. The file was created some (milli)seconds after your code was run

– peakpeak
Nov 14 '18 at 19:31

@Comintern I could be misunderstanding you, but aren't I using a full path with Open? sFile = Dir("H:My DocumentsLoma CW3 ReportsProduct StatisticsPR20180912T153019.txt.") followed by the "open sFile" line. I did the Debug.Print CurDir, it reads "H:My Documents"

– Jonathon Loch
Nov 14 '18 at 20:07

Ummm... No. Dir doesn't return the full path. You said yourself that sFile had a value of "PR20180912T153019.txt".

– Comintern
Nov 14 '18 at 20:12

Oh, I guess you're right. duh. haha I tried copying that "PR20180912T153019.txt" file to H:My Documents, and then it worked for that single document. So something is messing up my directories then? I did a Save As for the Excel doc, into the same folder it currently was. Now I'm getting CurDir = "H:My DocumentsLoma CW3 Reports"

– Jonathon Loch
Nov 14 '18 at 20:14

Put Debug.Print CurDir right above Open sFile. Are you in the correct directory? You really should be using a full path with Open (and also using FreeFile instead of hard coded file numbers).

– Comintern
Nov 14 '18 at 19:30

I'd vote for a timing issue. The file was created some (milli)seconds after your code was run

– peakpeak
Nov 14 '18 at 19:31

@Comintern I could be misunderstanding you, but aren't I using a full path with Open? sFile = Dir("H:My DocumentsLoma CW3 ReportsProduct StatisticsPR20180912T153019.txt.") followed by the "open sFile" line. I did the Debug.Print CurDir, it reads "H:My Documents"

– Jonathon Loch
Nov 14 '18 at 20:07

Ummm... No. Dir doesn't return the full path. You said yourself that sFile had a value of "PR20180912T153019.txt".

– Comintern
Nov 14 '18 at 20:12

Oh, I guess you're right. duh. haha I tried copying that "PR20180912T153019.txt" file to H:My Documents, and then it worked for that single document. So something is messing up my directories then? I did a Save As for the Excel doc, into the same folder it currently was. Now I'm getting CurDir = "H:My DocumentsLoma CW3 Reports"

– Jonathon Loch
Nov 14 '18 at 20:14

|
show 2 more comments

1 Answer
1

active

oldest

votes

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 DocumentsLoma CW3 ReportsProduct Statistics"

sFile = Dir(directory & "*.txt*")



Do Until sFile = ""

    Open (directory & sFile) For Input As #1

    blah blah blah

Thanks @comintern

answered Nov 14 '18 at 20:22

Jonathon Loch

113

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53307106%2fexcel-vba-run-time-error-53-file-not-found-but-file-was-found%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

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 DocumentsLoma CW3 ReportsProduct Statistics"

sFile = Dir(directory & "*.txt*")



Do Until sFile = ""

    Open (directory & sFile) For Input As #1

    blah blah blah

Thanks @comintern

answered Nov 14 '18 at 20:22

Jonathon Loch

113

add a comment |

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 DocumentsLoma CW3 ReportsProduct Statistics"

sFile = Dir(directory & "*.txt*")



Do Until sFile = ""

    Open (directory & sFile) For Input As #1

    blah blah blah

Thanks @comintern

answered Nov 14 '18 at 20:22

Jonathon Loch

113

add a comment |

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 DocumentsLoma CW3 ReportsProduct Statistics"

sFile = Dir(directory & "*.txt*")



Do Until sFile = ""

    Open (directory & sFile) For Input As #1

    blah blah blah

Thanks @comintern

answered Nov 14 '18 at 20:22

Jonathon Loch

113

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 DocumentsLoma CW3 ReportsProduct Statistics"

sFile = Dir(directory & "*.txt*")



Do Until sFile = ""

    Open (directory & sFile) For Input As #1

    blah blah blah

Thanks @comintern

answered Nov 14 '18 at 20:22

Jonathon Loch

113

answered Nov 14 '18 at 20:22

Jonathon Loch

113

answered Nov 14 '18 at 20:22

Jonathon Loch

113

answered Nov 14 '18 at 20:22

Jonathon Loch

113

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ndtyjky