How to open files contained in a folder in current file path

Question

I want to open a file (file) that is stored in a folder (Source) which is in the same directory as the current workbook. I get a runtime error 1004 indicating that it the file can't be located. What am I doing worng?

Set x = Workbooks.Open(ThisWorkbook.Path & "\Source\file*.xlsx")

Answer 1

Since you want the wildcard to stay, you need to loop through the files in the folder. Something like this may be of interest to you:

Sub FileOpen()

Dim sPath As String
Dim sFile As String
Dim wb As Workbook

    sPath = ThisWorkbook.Path & "\Source\"
    sFile = Dir(sPath & "file*.xlsx")

    ' Loops while there is a next file found in the specified directory
    ' When there is no next file the Dir() returns an empty string ""
    Do While sFile <> ""

        ' Prints the full path of the found file
        Debug.Print sPath & sFile

        ' Opens the currently found file
        Set wb = Workbooks.Open(sPath & sFile)

        ' Place your code here
        ' Place your code here
        ' Place your code here

        ' Close the current workbook and move on to the next
        wb.Close

        ' This line calls the Dir() function again to get the next file
        sFile = Dir()

    Loop

End Sub

Good luck!

Answer 2

Set x = Workbooks.Open(ThisWorkbook.Path & "\Source\file.xlsx"

I changed the file*.xlsx to file. xlsx...hope your code works.

thanks.

Answer 3

Replace the wildcard with actual filename.