[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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

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?
0
wordswithfriends
Asked:
wordswithfriends
  • 8
  • 6
1 Solution
 
Ken FayalCommented:
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.
0
 
wordswithfriendsAuthor Commented:
So is there a work around for this?
0
 
Ken FayalCommented:
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
wordswithfriendsAuthor Commented:
I'm not running the open command.  I am accessing the spreadsheet via ODBC
0
 
Ken FayalCommented:
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.
0
 
wordswithfriendsAuthor Commented:
I close the connection after every single access.  That does not seem to stop excel from intermittently trying to reopen
0
 
Ken FayalCommented:
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?
0
 
wordswithfriendsAuthor Commented:
Once every few seconds
0
 
Ken FayalCommented:
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.
0
 
wordswithfriendsAuthor Commented:
Unfortunately I don't have a choice.  I need to do constant polling because I'm getting live stock data
0
 
Ken FayalCommented:
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.
0
 
wordswithfriendsAuthor 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?
0
 
Arno KosterCommented:
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.
0
 
Ken FayalCommented:
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.
0
 
Ken FayalCommented:
The more I think about it - it makes sense this way.  If you were closing your connection, then opening it again, the possibility of Excel reopeneing is pretty high.  Whereas if you just open the connection once and keep your app running - don't close it - the app can just keep using the same connection - as it would with ODBC connection pooling, also causing Excel to stay "open".
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now