Linker IT Software
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
Recalculate SQL*XL functions in a protected sheet (Read 2613 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Recalculate SQL*XL functions in a protected sheet
02.10.09 at 10:28:07
Recalculate SQL*XL functions in a protected sheet
I find that once I have protected the worksheet, it can connect to the database but the Calculate option does nothing when pushed, therefore the figures never update. This is very inconvenient. Please help.

I have tried your scenario. I did the following:
I created a new workbook.
In a cell I added the following formula to retrieve the system date:
=SQLQueryValue("select sysdate from dual")
Then I set the cell formatting to time with seconds so you can see easily if the function is recalculated.
Finally I protected the sheet and the workbook.
I saved the workbook and closed down SQL*XL.
Reopening the workbook I saw the old time displayed.
I reconnected to the database. After the connection was established the function was actually recalculated! So, SQL*XL recalculates the formulas automatically after connecting to the database. No explicit recalculate command is necessary.
I did the manual recalculate anyways by selecting the force recalculate all option on the SQL*XL calculate menu. It displays rightfully the following message:

While forcing to recalculate all functions the following error occurred in Excel:
You cannot use this command on a protected sheet. Top unprotect the sheet, use the Unprotect Sheet command (Tools menu, Protection submenu). You may be prompted for a password

The method used to force the recalculation of all functions is to update the functions. Clearly you cannot do this if the sheet is protected. You can unprotect the sheet, recalculate and then protect the sheet again. If you do that in a macro it can be very efficient.
Back to top
« Last Edit: 02.10.09 at 10:38:49 by Gerrit-Jan Linker »  

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