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
setText and a complex multi-line SQL statement (Read 3707 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
setText and a complex multi-line SQL statement
17.05.09 at 16:24:38
 
setText and a complex multi-line SQL statement
 
Quote:
I am trying to follow your 3 section example for how to setup an Excel
macro, but having some issues with my SQL statement.  I'm not finding
any reference as to how to use SQLXL.Sql.setText to set up a more
complex, multi-line SQL statement.  Other than your example, I'm not
seeing any reference to SQLXL.Sql.setText at all, not even under the
Object Browser in the VB editor.  I could really use a more complete
reference.  Object Browser is not providing any help information.

I'm guessing that it may require a variable to be built up over several
quoted lines.  Do you have an example of how to do this?

 
An example of the setText statement is:
SQLXL.Sql.setText "select * from test"
 
You are asking how this statement is coded when the SQL statement is complex or very long. To begin with the last, a very long SQL statement can be coded in 2 ways.
 
Line continuation:
A too long line can be continued on a second line using the underscore character. As an example I have added the line continuation character to the statement above. As you can see it also needs the SQL string to be broken in two. So first you break the SQL statement in multiple sections using the & string concatenation operator: "select * from test" becomes "select * " & "from test". Note that I have added a space at the end of the first string. Now the line continuation character is added to continue on the next line. Note however that only a limited number of line continuations can be used. I recall that about 25 or so of these are allowed.
   SQLXL.Sql.setText "select * " & _
       "from test"

 
Using an SQL variable:
You need to first declare a variable using the Dim command. After that you assign a value to it and finally you pass it to the setText procedure:

Dim str as String
str = "select * from test"
SQLXL.SQL.setText str

 
Line continuations are much easier and also unlimited in number when using variables. The following code fragment shows the line continuation:

Dim str as String
str = "select * "
str = str & " from test"
SQLXL.SQL.setText str

 
You can also incorporate other variables or cell content as parameters. For example:

Dim str as String
str = "select * "
str = str & " from test"
str = str & " where "
str = str & " col1 = " & Range("mydata!D12").Value
SQLXL.SQL.setText str

Back to top
 
« Last Edit: 17.05.09 at 17:07:21 by Gerrit-Jan Linker »  

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