Excel ADO Recordset Issue

[robbuntin]

Hello, I am using ADO to query a worksheet from within a workbook. The development went well and the query returned data from the "BEx Data" tab. However if i refresh the the source data in "BEx Data", the old data is what is returned in the recordset. This is my code:

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1
Dim strsql As String
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim Cmd As ADODB.Command

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=""Excel 8.0;HDR=Yes;"";"

strsql = "Select * FROM [BEx Data$]"

rs.Open strsql, cn, adOpenStatic, adLockOptimistic, adCmdText

Do While Not rs.EOF
foo = rs.Fields(3).Value
rs.MoveNext
Loop

rs.Close
cn.Close


Is there some way to clear a "Query Cache" or something i am missing? Any help is appreciated.

[Aflatoon]

Try saving the file.

By the way it is not recommended to use ADO on an open workbook due to memory leaks.

[robbuntin]

I've saved off a local copy on another machine and somehow even it finds the old data.

[JKwan]

After you refresh your data, then save your file, then run your SQL again