I didn't see that this question was addressed specifically, so I'm convinced I'm just doing something wrong and would appreciate any guidance anyone can provide.

I need to be able to create a recordset of data from an excel file that allows me to then close the connection and continue to use the data. Here is the bit of code that gives the error ...

Public Function OpenRS(ByVal sql, ByVal FilePath)

If Right(FilePath, 4) = "xlsx" Or Right(FilePath, 3) = "xls" Then

Set ExcelApp = CreateObject("Excel.Application")
Set WBook = ExcelApp.Workbooks.Open(FilePath)

End If

Set conn = CreateObject("ADODB.Connection")
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Mode = adModeRead
conn.CursorLocation = adUseClient

conn.Open = "Data Source=" & FilePath & ";" &_
"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;"";"

Set rs = CreateObject("ADODB.Recordset")
rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockReadOnly

rs.Open sql, conn

Set OpenRS = rs

Set rs.ActiveConnection = Nothing


End Function

I've read the key to creating a disconnected recordset is to first set the 'cursorlocation' of the recordset to 'adUseClient', then Open, then set the 'ActiveConnection' to Nothing, but no matter what order I've tried I continue to get the error ...

"Operation is not allowed when the object is open"

Any guidance would be greatly appreciated.