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 execution of SQL from a worksheet (Read 4492 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Automate execution of SQL from a worksheet
21.11.07 at 19:04:23
 
Automate execution of SQL from a worksheet
 
I am creating a Macro to execute an insert statement which resides on my testing workbook and Load Data worksheet on the range A1 to E51.  What I usually do I select the range, copy then select SQL item from SQL*XL set the SQL source as SQL Editor and Paste my selection then press OK to run the statement. After that I click on the commit button to commit.  
 
I tried several times but I could not. Can you please assist me in achieving this goal? The last Macro that I have created is attached. Please assist me.  
 
 
Code:
Sub LoadData()
    Range("A1:E51").Select
    Selection.Copy
    Selection.Paste
    setTextFromWorksheet SelectedCells:=Range("A1:E51").Select
    SQLXL.Sql.Statements(1).Execute
    SQLXL.Database.Commit
End Sub
 

Back to top
 
 

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




Posts: 75
Re: Automate execution of SQL from a worksheet
Reply #1 - 21.11.07 at 19:08:22
 
First, I noticed that you write the SQL*XL VBA code by hand. The best thing to do is to record a macro while you manually run the scenario. When finished you can change the macro if required.
 
To prepare for the exercise I have put a query in range A1:E51. In A1 I typed "select *" and in E51 I typed "from emp". I recorded the following macro to run the SQL:
 
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 21/11/2007 by Gerrit-Jan Linker
'

'
    ActiveWindow.SmallScroll Down:=15
    Range("A1:E51").Select
  SQLXL.Sql.setTextFromWorkSheet Range("$A$1:$E$51"), True
  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 = "$A$53"
    .Transpose = False
    .SQLInNote = True
    .ShowNote = 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
End Sub
 


 
The only edits I want to make is the first statement where I scrolled down to be able to select the cells. I further want to give the macro a good name so I rename it from Macro1 to RerunQuery. I also remove the comments. The resulting macro is:
 
Code:
Sub RerunQuery()
  SQLXL.Sql.setTextFromWorkSheet Range("$A$1:$E$51"), True
  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 = "$A$53"
    .Transpose = False
    .SQLInNote = True
    .ShowNote = 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
End Sub
 


 
That is all done. Reruning the macro simply executed the query without displaying any screens or asking for the input of any options.
 
Please note that if you use parameters and you do want SQL*XL to ask for the parameters values you can set the ShowParametersDlg = True.
 
To make it easier to run you can also make a button on the worksheet and attach it to the macro. When clicking the button the macro will run.
 
See also:
Run queries with a button
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1130852876
Back to top
 
« Last Edit: 21.11.07 at 19:26:54 by Gerrit-Jan Linker »  

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