We help IT Professionals succeed at work.

Why does Excel try to reopen when using as an ODBC data source?

wordswithfriends
on
Medium Priority
291 Views
Last Modified: 2012-06-21
I'm currently accessing Excel as an ODBC data source via java.  The spreadsheet receives live stock market data from the Internet.  I constantly poll this data for my java program.  Intermittently my java program will stall and excel will ask if I wish to reopen the spreadsheet.  Why does it do this?
Comment
Watch Question

Commented:
The reason is that when you make subsequent calls to the spreadsheet via the Excel object, it thinks you want to open the same file.  Excel will do the same thing if you do it from the UI as well.  It sounds like as your Excel spreadsheet is receiving data, cells are modified, and therefore the spreadsheet is flagged as changed.  So when you access the file subsequently, it doesn't want to assume that you want to re-open the file without letting you know that there were changes and that they might get lost if the file is reopened.

Author

Commented:
So is there a work around for this?

Commented:
I think it would be tough, because if the code is written as a macro INSIDE of the excel spreadsheet, there wouldn't be an issue with this.  However, since you are accessing the spreadsheet from outside the Excel object, I think you have to find a way not to let the Excel object expire.  In other words, you have to find a way to open the Excel spreadsheet only once, keep it live, and retrieve what you need to on your subsequent calls.  Don't keep trying to run the "open" command unless you have previously called the Save command and closed the spreadsheet.

Author

Commented:
I'm not running the open command.  I am accessing the spreadsheet via ODBC

Commented:
When you access the spreadsheet via ODBC, you are essentially running the open command.  Make sure that when you are done receiving data from the spreadsheet, that you properly close the ODBC connection.

Author

Commented:
I close the connection after every single access.  That does not seem to stop excel from intermittently trying to reopen

Commented:
I don't recall if there is a an object destructor in Java, because ODBC has a connection pool and it will keep trying to use the same one if it wasn't destroyed.  
How often do you try to make a connection?

Author

Commented:
Once every few seconds

Commented:
Ok.. that might be an issue with the pool maxing out and recycling.  You are brave for trying this with Excel & Java.  

Try seeing how far you get if you try your connections spaced out with a larger time frame in between connections - being sure to continue to close your ODBC connection.

Author

Commented:
Unfortunately I don't have a choice.  I need to do constant polling because I'm getting live stock data

Commented:
I understand.  But on my stock data apps, I don't use Excel because it's a slow "middleman".  We get free stock data from the web by using AJAX calls to web sites like Google and Yahoo to retrieve .csv data.  It just seems to be a bloated solution having to let Excel use it's GetExternalData feature and then you query Excel, especially via ODBC.

I suggested the lowering of the frequency in connecting to Excel only as a way to help you figure out if it is an ODBC pool recycle/timeout issue.

You can also look at your ODBC connection string...  There are some options for connection pooling that you can play with.

Author

Commented:
I don't use GetExternalData I use RTD which was specifically designed for this purpose.  Unfortunately I cannot lower the frequency real Time Data is required.  Which connection pooling options would you recommend?
Would it be possible (although this too is far from a perfect solution) to make use of an intermediate file ?

excel writes the most recent value to a text file and waits until this file is deleted. when the file is deleted, excel again writes most recent valeus to the file
java app processes the file and deletes it, waiting until the file appears again.

Commented:
You could go the other way and just not close the connection so it can be reused si9nce you are making such rapid connections.  In fact, I think that is probably a better idea.  Just write it so that it opens the connection once and doesn't close it after it makes the query.  Only close it once you close the application.
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.