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
SQL Query from within Excel using date variables (Read 5479 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
SQL Query from within Excel using date variables
15.03.10 at 10:36:56
 
SQL Query from within Excel using date variables
 
Quote:
Hi there,

I was wondering if your SQL XL software would allow me to run normal SQL queries within VBA code of an excel file?

Here is an example query:
SELECT
Q.NAME AS QueueName,
CONVERT(CHAR(10),fnLocalTime(QH.TIME),101) AS Date,
CONVERT(CHAR(10),fnLocalTime(QH.TIME),8) AS Time,
QH.CALLVOLUME AS ContactVolume,
QH.ABANDONS AS Abandons
FROM
dbo.HISTORYTIMESERIES QH
INNER JOIN
dbo.QUEUE Q
ON
QH.QUEUEID = Q.ID
WHERE
fnLocalTime(QH.TIME) BETWEEN DateFrom AND DateTo
AND Q.NAME = QueueName
ORDER BY
QH.TIME

This query returns 4 columns of data of a specified queue and timeframe.

I am wanting to make this query have variables of the dates and run in VBA.

Does your program make this possible to run normal queries like this?

 
This is what SQL*XL does best. Absolutely no problem.
 
What I recommend you to do is to get the trial version from www.oraxcel.com/downloads. You can work 10 days to see whether it does what you want it to do.
 
After install, first use it interactively. The VBA is as simpel as recording a macro. Do that as a last step.
 
Try to connect to your database. The connection wizard is recommended to be used. Just go to the connection dialog and press new...
 
Then, run your query without parameters first. Open the SQL dialog and type the SQL. You can save the SQL as a favorite or as a file for convenience...
 
Parameterise your query. Please note that time parameters are never easy. Decide between substitution variables (text replacement variables really) or bind variables (true dates).
A substitution variable is prefixed with & and a bind var with a :. In the following a is a subst var and b is a bind var:
select * from mytable where cola = &a and colb = :b
 
In general substitution variables have the least unexpected behaviour but bind variables tend to be more accurate. Be aware for implicit conversions with these parameters. The usual source of problems.
 
Finally, when you have got your parameterised query ready to use record a macro. Restart Excel so SQL*XL does not remember parameter definitions etc and then record a session where you connect, run the SQL, define the values and disconnect. Observe what SQL*XL generated. First test it as it is. Then tweak the code. You may want to prompt for variables, etc.
 
Back to top
 
« Last Edit: 15.03.10 at 10:39:03 by Gerrit-Jan Linker »  

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




Posts: 75
Re: SQL Query from within Excel using date variabl
Reply #1 - 15.03.10 at 10:40:57
 
Quote:
Thanks for this! It worked perfectly for executing a SQL query.

My only issue is the record macro records nothing for this so I don't
understand that part of your email.

 
Please switch on the macro recorder. In SQL*XL go to program preferences - general and enable macro recording support.  
 
Nice to hear you like SQL*XL!
Back to top
 
 

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