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
How can LITlib automatically track changes in CSV? (Read 4133 times)
grid
YaBB Newbies
*


I Love SQL*XL

Posts: 3
How can LITlib automatically track changes in CSV?
02.01.10 at 22:18:32
 
Hello,
 
I currently use LITlib - Excel Power Functions Pack.
 
I load a .csv with it to Excel 2007 (Dutch version).
 
The formula I use is: =csv("C:\file.csv";"file";";")
 
This works pretty well.
 
Now file C:\file.csv will change sometimes. There are other data in the file. This happens every 5 minutes for example. How can I automatically reload the file? Or better yet, How can LITlib automatically track changes in C:\file.csv?
 
I've already clicked F9. I also clicked on "Refresh All Data". Without result so far.
 
How can I load the changed file? (without typing in the formula bar)
 
 
Thank you very much,
 
grid
 
Smiley
Back to top
 
 
  IP Logged
grid
YaBB Newbies
*


I Love SQL*XL

Posts: 3
Re: How can LITlib automatically track changes in
Reply #1 - 02.01.10 at 23:09:13
 
I know there is a Recalculate button. This button can be pressed with a function perhaps? This way I can maybe set the button to the 5 seconds automatically pressed with VBA.
 
A useful answer I appreciate very much.
 
 
Best regards,
 
grid
 
Smiley
Back to top
 
 
  IP Logged
grid
YaBB Newbies
*


I Love SQL*XL

Posts: 3
Re: How can LITlib automatically track changes in
Reply #2 - 03.01.10 at 00:59:34
 
I tried this:
 
Public Sub recalculate()
    Dim litlib As Object
    Set litlib = Application.COMAddIns("litlib.clsAddin").Object.CallbackObject.Application
     
    litlib.calculateworksheet
End Sub
 
Sub timerMsg()
Dim alertTime
alertTime = Now + TimeValue("00:00:05")
Application.OnTime alertTime, "msg"
End Sub
 
Sub msg()
recalculate
timerMsg
End Sub
 
 
That works.
 
But there might be another solution to solve this maybe.
 
 
Best regards,
 
 
Smiley
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: How can LITlib automatically track changes in
Reply #3 - 03.01.10 at 11:00:58
 
Hi,
 
That is an interesting question you ask. Thank you.
 
First the answer how you can do this currently with litLIB. litlIB's formula's are calculated when Excel thinks they should be calculated. When no input changes and that is the case how you have used the formula by typing all input into the formula itself Excel will not recalculate the function and hence litLIB has no opportunity to refresh the file.
 
There are 2 solutions. Either we can force the recalculation or the csv function can be made 'volatile'. Volatile functions are recalculated whenever Excel calculates. However you don't want all formulas to be recalculated. A good example of a function that needs recalculation is a function that displays the current time.  
 
I rather would see you use option 1, that you force the recalculation of the csv function. Manually this is easiest done by editing the function (just press F2 to load the formula in the Ecxel formula editor) and pressing Enter to make Excel calculate it.
 
This can also be done using litLIB's recalculate function. It is available in the menu or if you use Excel2007 you can use it from the ribbon.
 
If required you can setup a button or other control to call this litLIB recalculation macro.  
 
I now see that you have already successfully done this and put it even on a timer. That will work fine but it requires this little bit of programming. I agree that it would be nice to be able to display the actual contents of the file and that it refreshes when the file changes. I will have a look if that can be done. Interesting question!
Back to top
 
« Last Edit: 03.01.10 at 11:08:59 by Gerrit-Jan Linker »  

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