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
Prevent screen updates by SQL*XL (Read 4219 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Prevent screen updates by SQL*XL
04.11.05 at 13:43:51
Jorma from Finland asked the following question today.
Excel is still a bit 'jumpy' because of the selected cell is changed by SQL*XL after each query. Could the
focus remain unchanced during SQL select executions somehow?
A very pragmatic solution to this is to prevent Excel's screen from updating. As the first line of your macro switch the automatic updating of the screen off and as the last line of your code switch it back to normal. If you want to select a particular cell after the execution you need to do that after you have turned the screen updating back to true. Otherwise selecting a cell will not result in the Excel screen getting refreshed.
Example. This is code I recorded with the macro recorder. I added the first line and the two lines at the bottom:
Sub Macro1()
  Application.ScreenUpdating = False

  SQLXL.Sql.setText "select * from emp;  select * from user_objects;  select * from all_objects where rownum <=1000;"
  Set SQLXL.Sql.Statements(1).Target = Targets(litExcel)
  SQLXL.Sql.Statements(1).OptimiseForLargeQuery = False
  With Targets(litExcel)
    .AutoFilter = False
    .AutoFit = True
    .Headings = True
    .Sort = False
    .StartFromCell = "Sheet1!$A$20"
    .Transpose = False
    .SQLInNote = True
    .FormatData = True
    .FreezePanes = False
    .PasteInsert = False
  End With
  With SQLXL.Sql.Statements(1)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  With SQLXL.Sql.Statements(2)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  With SQLXL.Sql.Statements(3)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  Application.ScreenUpdating = True
End Sub

Back to top

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