CODEWITHSUNDEEP
excelvba
giveitaway
giveitaway

Reputation: 25

Looping code error-Excel says that it can't find the file

I'm trying to write a code that will refresh all workbooks starting with 'FY' in a folder. With the current code, the first two workbooks refresh, but when it comes to the third workbook, I get this error:

Sorry, we couldn't find FY20 11-15.xlsm\FY20 1-5.xlsm. Is it possible it was moved, renamed or deleted?"

The path to the workbook is "C:\Documents\Database\Sales".

Here's the code:

Sub refreshdata()
    Dim file As String
    Dim book As String

    file = Application.ThisWorkbook.Path
    book = Dir(file & "\FY*.xlsm")

    Do While file <> ""
        Dim wb As Workbook
        Set wb = Workbooks.Open(file & "\" & book)

        Call Refresh
        wb.Close savechanges:=True
        file = Dir
    Loop
End Sub

Upvotes: 1

Views: 103

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

You named your variables not clearly and file contains actually a path file = Application.ThisWorkbook.Path therefore you mixed everything up. Get your variable names more meaningful! Make sure your variables are well named after what content they contain or you confuse yourself.

Sub refreshdata()
    Dim Path As String
    Path = Application.ThisWorkbook.Path

    Dim FileName As String    
    FileName = Dir(Path & "\FY*.xlsm")

    Do While FileName <> vbNullString
        Dim wb As Workbook
        Set wb = Workbooks.Open(Path & "\" & FileName)

        Call Refresh
        wb.Close SaveChanges:=True
        FileName = Dir
    Loop
End Sub

What was wrong?

Here file = Dir you set your file variable which actually was the path to filename. And in the next iteration of the loop Set wb = Workbooks.Open(file & "\" & book) is twice a filename, the new filename in file and the old in book.

Upvotes: 1

Related Questions