Excel Sheet drops embedded query

[Todd M]

I have been using embedded odbc queries to Oracle databases extensively.
Recently, Excel completely drops the query, so the data can't be refreshed. I
can rebuild the query, and it works fine again for a while. Then later on a
random basis the query is no longer available.

Any ideas?

Thanks much!

 

[Jan Karel Pieterse]

Hi Todd,

I have been using embedded odbc queries to Oracle databases extensively.
Recently, Excel completely drops the query, so the data can't be refreshed. I
can rebuild the query, and it works fine again for a while. Then later on a
random basis the query is no longer available.

I haven't seen that one before. What happens if you execute code like this on
the offending sheet when the problem has occurred:

Msgbox ActiveSheet.Querytables.Count

If it returns less than you expect, then all I can think of is code or some
other action that deletes rows including the QueryTable Area.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[Todd M]

Hello Jan,
Thank you much for the response. The code you supplied yields zero on the
tab where the query was lost, and one on a tab that hasn't lost the query
yet. It is very strange - the queries seem to disappear without a trace -
expect that Excel won't let me use the old data range name - saying it
already exists. I always check "Save Query Definition", and the retrieved
data remains - just no query . . .

Thanks again!

 

[Bruce]

Todd is not the only one with this problem. I have one user that has been
going nuts with an embedded query being "dropped" when they save and reopen
the spreadsheet. I have the user copying and pasting the query from another
working spreadsheet to get it working again, but the fix does not last. No
one else is reporting the problem. I have not experienced it myself.

Bruce

 

[Jan Karel Pieterse]

Hi Bruce,

I have one user that has been
going nuts with an embedded query being "dropped" when they save and reopen
the spreadsheet. I have the user copying and pasting the query from another
working spreadsheet to get it working again, but the fix does not last. No
one else is reporting the problem. I have not experienced it myself.

If the book doesn't contain proprietary information, could I have it to
investigate?

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com