VBA Cannot find/open CSV file if I set FileName dynamically

I hope someone can help me because I am really confounded. I have often used VBA to open .csv files with no issue, but for some reason, I am getting "can't find file errors" for a specific application.

The goal is a simple one. I would like to open a .csv file that I name dynamically. Then I would like to copy the contents of the CSV file into my active workbook. I know that all of my subsequent code works, because if I hard code the directory/file string, it opens fine. However, if I construct the string dynamically through concatenations, I will get a "can't find file" error.

The errors I get are either an Error 1004 or an Error 53. The latter I know is a "Can't Find File" error, while the former is a more generic catch all, but is triggered for the same reason.

If I use:
Dim rootPath As String
rootPath = ThisWorkbook.Path & "\"
Dim contourWb As Workbook
Set contourWb = Workbooks.Open(rootPath & contourDir)

Where contourDir points directly to the file I want to open, and changes dynamically, I get a 1004 Error. I have confirmed that the string is correct with the StrComp function, but it still doesn't work.

If instead I use:
Open contourDir For Input As #1

Where in this case contourDir includes the rootPath, I get an Error 53. However, if I hardcode the matching string from the StrComp function:
Open "C:\Users\mlevine3\Desktop\GREAT-ANGIM\OUT_S04\Contours\Contours_KATL_2015_ATL_65.csv" For Input As #1

Then my code works fine. I have been looking through every forum I can find, and almost every solution I have seen just requires including the folder path along with the file name, but I have confirmed over and over that that is NOT my problem. Can't seem to figure out what is. Any help would be much appreciated.

From that code I think you need to use the name property.

Dim contourWb As Workbook
Set contourWb = Workbooks.Open(rootPath & contourDir.Name)

Workbooks open requires the full path string, including extension

Normally I use

Dim strFullPath as string
strFullPath = rootPath & contourDir.Name
Debug.print strFullPath ' to confirm
Set contourWb = Workbooks.Open(FullPath)

Sorry, I should clarify that contourDir is a STRING with the full filepath, not an Object. From what I understand, the .Name property just gets the string from that property of an object, so it isn't actually any different from my solution (thus problem not resolved).

Appreciate the quick response, but still having issues.

Ok, thanks for clarity. I should have seen that. I've had this very same issue before. Recently in fact.

To resolve it, I declared a new excel instance.

Please Login or Register  to view this content.

Then

Please Login or Register  to view this content.

Then to work with the required book

Please Login or Register  to view this content.

If this doesn't work for you, post your book and I'll go over it

Journeyman3000 thanks for the reply again. Just figured out my issue though, and it was rather simple.

In my dynamic strings, I was pulling a .Value from my Excel sheet instead of .Text. Once I made that change everything started working. Issue resolved!

No problem. Glad you've solved the problem. Remember to mark the thread as solved.