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
Prevent screen updates by SQL*XL (Read 3156 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.
 
Question:
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?
 
Answer:
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:
Code:
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
  SQLXL.Sql.Statements(1).Execute
  With SQLXL.Sql.Statements(2)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  SQLXL.Sql.Statements(2).Execute
  With SQLXL.Sql.Statements(3)
    .ShowParametersDlg = False
    .ShowResultsetDlg = False
  End With
  SQLXL.Sql.Statements(3).Execute
  
  Application.ScreenUpdating = True
  Range("A1").Select
End Sub
 

Back to top
 
 

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