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
Refresh on a timer (Read 6340 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Refresh on a timer
27.03.07 at 14:39:05
Refresh on a timer
SQL*XL course participant George from Singapore asked the following question:
When we pull the data from the SQL server into Excel, can SQL*XL refresh (by itself with a timer) so that we can have the latest update. We have this feature in the excel worksheet.
Yes, you can do this. Follow these simpel steps to accomplish it:
  • Connect to the database
  • Start the Excel macro recorder (Tools, Macros, Record)
    Name the macro: refresh
  • Go to the SQL*XL SQL dialog and run your query
  • Stop the Excel macro recorder
    Personally I like to add a button but this is optional...
  • Go to View, Toolbars, Control toolbox
  • Use the toolbox to draw a new button somewhere on the worksheet
  • Right click the button and go to properties
  • Change the caption to Start
  • Double click the button to add the following code
    Private Sub CommandButton1_Click()
      If CommandButton1.Caption = "Start" Then
        CommandButton1.Caption = "Stop"
        CommandButton1.Caption = "Start"
      End If
    End Sub

  • Then add the following small sub in module1 (where your refresh macro also was recorded).
    Public Sub do_refresh()
      If Sheet1.CommandButton1.Caption = "Stop" Then
        Application.OnTime Now + TimeValue("00:00:10"), "do_refresh"
      End If
    End Sub

  • On the controls toolbar, exit the design mode
    Now you can click the button to start the timer and press the button again to stop it. Each 10 seconds it will refresh.

For your convenience, I have attached the workbook in which I made the above example. Simply connect it to an Oracle database and press the start button.
Back to top
« Last Edit: 27.03.07 at 15:32:48 by Gerrit-Jan Linker »  

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