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.
Bookmarks