Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Formula errors when opening workbooks (Read 2257 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Formula errors when opening workbooks
07.12.10 at 11:04:49
 
Formula errors when opening workbooks
 
Quote:

After opening a workbook in which I used SQL*XL formulas the formulas show the following error:
#SQL*XL Universal error: Not connected to the database.

Formulas that depend on this cell show:
#Value

My scenario is:
1. Open the sheet with “double click” on the Excel icon.
2. Connect to DB…..
3. Calculate
4. Save Sheet
5. Disconnect DB.
6. Exit Sheet
7. Reopen the sheet again… all results are gone …

How is this possible?

 
I investigated this problem and found precisely this behavior. Some code in SQL*XL that did not work anymore - but I have reinstated that now - warned the user when closing a workbook with SQL*XL formulas when the calculation mode was set to automatic. SQL*XL offers to set the calculation method to manual.
 
When the calculation method in Excel (Excel options) is set to automatic a calculation will be performed of the SQL*XL functions when a workbook opens. When the calculation method is set to manual this will not happen. Hence the solution is to set the calculation method to manual.
 
The problem with this is that the calculation method is an Excel property and not a workbook property. I experimented a little. When I set Excel to manual calculation I can now save a spreadsheet, disconnect from the database, re-open the spreadsheet without getting errors. That is good! However when I set the calculation method to automatic re-opening the workbook that was saved when the calculation method was set to manual will cause the formulas to be recalculated.
 
Therefore I suggest that if you wish to ensure that the calculation method is set to manual that you enter the following 2 lines in your Workbook_Open event:
Application.Calculation = xlManual
Application.CalculateBeforeSave = False
 
It is best to set your Excel calculation option to Manual when working with SQL*XL.
 
Back to top
 
« Last Edit: 07.12.10 at 11:21:30 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1