Run-time Error 91 when opening file

[Alex Lee]

When I run the following routine, the file opens with no error:

Sub Test1()

Workbooks.Open (ThisWorkbook.Path & "\Export_CitiMD_20231221_114052.csv")

i = 1

End Sub


However, when I try to put the name of the CSV file into a cell, immediately AFTER the CSV file opens, Run-time error 91 pops up. May I know why?

Sub FindNewISINs()

Dim currentWorkbook As Workbook
Dim csvWorkbook As Workbook
Dim csvFileName As String

Set currentWorkbook = ThisWorkbook
csvFileName = currentWorkbook.Path & "\" & currentWorkbook.Sheets("Control").Range("G44").Value

Set csvWorkbook = Workbooks.Open(csvFileName) ' Error occurs at this line. CSV file did open. But Error 91 comes up and the rest of the code cannot be executed.

' <Other codes>

End Sub

 

[Joe4]

Welcome to the Board!

I tested it out and your code works just fine for me.
Are you sure that the name is EXACTLY the same?
Do you have the ".CSV" extension as part of the file name?

An easy way to confirm everything looks just right, add a MsgBox to your code to return the full path and file name you are building, i.e.

Sub FindNewISINs()

Dim currentWorkbook As Workbook
Dim csvWorkbook As Workbook
Dim csvFileName As String

Set currentWorkbook = ThisWorkbook
csvFileName = currentWorkbook.Path & "\" & currentWorkbook.Sheets("Control").Range("G44").Value
MsgBox csvFileName

Set csvWorkbook = Workbooks.Open(csvFileName) ' Error occurs at this line. CSV file did open. But Error 91 comes up and the rest of the code cannot be executed.

' <Other codes>

End Sub

 

[Alex Lee]

The file name was OK. That's why the CSV file did open. But immediately after that, the code stopped running due to the Run-time Error 91.

 

[Alex Lee]

More specifically, the sequence of event is this after adding the MsgBox command:

-> CSV file opens -> MsgBox pops up, showing the correct path name -> Run-time Error 91 occurs -> the rest of the code aborted.

Anyway, I found the problem. It turns out that about 5 rows down where the error occurs, there is one line of code that has an undefined variable used in an IF statement. The error is probably triggered by that.

Thanks for the help.