Excel vba is not finding file even though file is present in folder-CodePudding

I'm trying to copy the contents of a sheet in a directory to a specific sheet in the current Excel workbook. This is what I have tried so far -

Files I'm trying to copy -

Testfile1.csv Testfile2.csv

Dim reportList As String, reportType As String, partialfilePath As String, filename As String, filePath As String
Dim reportArray() As String
Dim i as Integer

On Error GoTo here

reportList = Range(B1).Value 'B1 value here is = Testfile1,Testfile2
reportArray = Split(reportList, ",")

partialfilePath = Range(B2).Value ''B2 value here is = "E:\test files\excel-sheets"

For i = LBound(reportArray) to UBound(reportArray)
   reportType = "*" & reportArray(i) & "*"
   filename = Dir(partialfilePath & "\" & reportType)
   filePath = (Chr(34) & partialfilePath & "\" & filename & Chr(34))
   Call copySheets(filePath, filename)
Next i

here:

    MsgBox Err.Description

End Sub

Function copySheets(Path As String, filename1 As String)

Workbooks.Open Path

Workbooks(filename1).Worksheets(1).Range("A1:XFD1048576").Copy
Workbooks("Reports").Worksheets(1).Range("A1:XFD1048576")

Workbooks("Reports").Save
Workbooks(filename1).Close

End Function

But for some reason, the above code throws the following error -

Sorry, we couldn't find "E:\test files\excel-sheet\Testfile1.csv". Is it possible it was moved renamed or deleted?

I'm confused that even though it mentions correct path and file name in the error and even though file is available, it still says it's unable to find the file. I even though this might be due to the fact that its a .csv file. But I'm getting the same error .xlsx file as well.

What am I missing here?

CodePudding user response：

filePath = (Chr(34) & partialfilePath & "\" & filename & Chr(34))

You're adding literal quotes to the file path, and that's why you get "file not found"

filePath = partialfilePath & "\" & filename

should work.