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
Automate recalculating formulas (Read 2766 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Automate recalculating formulas
29.07.11 at 11:56:56
 
Automate recalculating formulas
 
Quote:
I'd like to know if SQL*XL can refresh queries on a sheet AUTOMATICALLY as manually clicking the SQL XL Calculate button every hour or so is a bit tedious, especially as I have to do so for 4 different workbooks...

 
Yes, it can. But you are correct, there is no option for it. It is very easy to program into VBA. It would be the easiest if you start the Excel macro recorder and let it record you recalculating the formulas. Then stop the recorder and inspect the recorded code. You can also code it manually if required (don't forget to add a reference to sqlxl.xla in that case).
 
Press Alt-F11. You will drop to the VB environment. Select your XLS file and add a new code module (the macro recorder will have added a new one for you automatically). Enter the following code (or look at the code the recorder recorded):
 
 
Sub Macro2()
  SQLXL.mnuCalculateSQLXLFunctions_Click
  'Do it again in 30 seconds.
  Application.OnTime Now() + TimeValue("00:00:30"), "macro2"  
End Sub
 
 
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Given
YaBB Newbies
*


Goon!

Posts: 1
Re: Automate recalculating formulas
Reply #1 - 29.07.11 at 15:47:07
 
Awesome stuff! Thanks again Gerrit!  Smiley Cool
Back to top
 
 
  IP Logged
Pages: 1