Open .csv file with Excel VBA

Question

I'm having a problem with this piece of my code in VBA and I can't figure out why it isn't working! I've tried another code to see if it was the loop that has the issue but the issue is specifically opening the files and not iterating through them.

Sub fileloop(Path)
Dim strFile As String, strPath As String
Dim MyBook As Workbook

strPath = Path '& "\*.csv"
strFile = Dir(strPath & "\*.csv")

MsgBox (strFile)

While strFile <> ""

'placed another messagebox here to see if the strFile was the same inside the loop.
MsgBox (strFile)

'this line has the error.   
    Workbooks.OpenText FileName:=strFile, _
DataType:=xlDelimited, Comma:=True, Local:=True

  set MyBook = ActiveWorkbook

    Call SortColumnB(MyBook)

    strFile = Dir

 Wend

End Sub

The error message I get goes something like this:

'AC000W0009.csv' could not be found. Check the spelling of the file name, and verify that the file location is correct.

If you are trying to open the file from your list of most recently used files, make sure that the file has not been renamed, moved, or deleted.

I've tried so many variations apart from the one listed above and I can't understand why VBA won't recognize that the file exists.

EDIT

Going off of what Mike said about opening a file with the complete path the changes I made to the code to let it open .csv files:

 strPath = Path & "\"
strFile = Dir(strPath & "*.csv")



While strFile <> ""
MsgBox (strFile)                 'added path to file name.
    Workbooks.OpenText FileName:=strPath & strFile, _
DataType:=xlDelimited, Comma:=True, Local:=True

Place the MsgBox below the While to be able to view all the attempted Opens — Gary's Student
– Gary's Student, Commented Aug 6, 2014 at 14:51
Or at least add some Debug.Print "..." if you don't want pop-ups, it will help you find what's going wrong and you will keep a trace (You need the exec zone open in the editor) — smagnan
– smagnan, Commented Aug 6, 2014 at 14:54
Does Debug.Print work for every error within a subroutine if I put it at the start of the sub? Or does it just work on that specific error line? Sorry I've never used it before I usually just have the popups come up. — Daniella
– Daniella, Commented Aug 6, 2014 at 14:58
@Mike this is probably a silly question, but isn't Dira way of finding files in a path specified and with the appropriate extension? Maybe vba is just not recognizing the path. I'll go ahead and see if adding the path in helps as well — Daniella
– Daniella, Commented Aug 6, 2014 at 15:00

Mike · Accepted Answer · 2014-08-06 15:11:35Z

5

I beleve the Dir only returns only the filename.

If you want to open it, you need to add the path to the file name returned by Dir.

There's some good examples here

answered Aug 6, 2014 at 15:11

Mike

3,3113 gold badges31 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Open .csv file with Excel VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related