CODEWITHSUNDEEP
vba
Daisy
Daisy

Reputation: 1

why VBA cannot find the files in a specified folder

Sub ReformatForALFA()

    Filename = Dir("C:\Users\Daisy\Desktop\Cloudy\*.*")
    Do While Len(Filename) > 0

    Workbooks.Open (Filename)

    row_num = Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Rows.Count

    myRng = Empty
    For i = 1 To row_num
        If myRng = Empty And Cells(i, 1) Like "*value*row*" = False Then
            myRng = Rows(i).Address()
        ElseIf Cells(i, 1) Like "*value*row*" = False Then
            myRng = myRng & "," & Rows(i).Address()
        Else
            Pos1 = InStr(Cells(i, 1), "=") + 2
            Pos2 = InStr(Cells(i, 1), ">") - 1
            Pos3 = InStr(Cells(i, 1), "/") - 1
            Age = Mid(Cells(i, 1), Pos1, Pos2 - Pos1)
            Num = Mid(Cells(i, 1), Pos2 + 2, Pos3 - (Pos2 + 2))

            Cells(i, 2) = Age
            Cells(i, 3) = Num
        End If
    Next i

    Range(myRng).Delete
    Columns(1).EntireColumn.Delete

    Filename = Dir()
    Loop
End Sub

Hello, I wrote the macro above to loop through all the text files in a folder. there are two text files located in that folder. When I ran it, it shows me "we cannot find Tuesday.txt due to it has been deleted, moved etc. Can anyone help me fixing this issue. Thank you! Daisy

Upvotes: 0

Views: 568

Answers (1)

Storax
Storax

Reputation: 12187

Filename = Dir("C:\Users\Daisy\Desktop\Cloudy\*.*")

will just retrieve the filename without the path. You could either change to the path with chdir or add the path to the file you want to open

ChDir ("C:\Users\Daisy\Desktop\Cloudy")

or 

Workbooks.open("C:\Users\Daisy\Desktop\Cloudy\" & filename)

Upvotes: 3

Related Questions