Let's talk today about a few tricks to optimize its utilization with Oracle.
Ok first you may have notice that this a very slow feature. Changes to the source query are uneasy. You need to open the source query. Then to use an old fashion query defintion tool. And then to go back to the Excel file to finally realize that you have to change some criteria in the SQL code.
How to speed up this?
You are the developper. You know where to get the information in the database.
Most of the time you define your queries in your fovarite Oracle tool and not in Excel or MS Query. So you don't need a wizard to explore the database. Instead you may need, as I, a quick way to seek-and-destroy bugs.
I prefer to use my own a VB statement (which will be not stored into the final file).
Here is the sub I use :
Sub createquery(strODBC, strSQL, strDestinationRange, strNAme)
With ActiveSheet.QueryTables.Add(strODBC, Destination:=Range(strDestinationRange))
.CommandText = strSQL
.Name = strNAme
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
strODBC is the an ODBC string something like :
ODBC;DSN=DSNName;UID=USer_ID;PWD=Password;DBQ=SCHEMA_NAME;"
strSQL is the SQL statement you want to execute here
strDestinationRange is something like "B2" or "$A$12:$H$52"
You should use this statement carefully at your own risq and, off course, fit it to your needs.
Notice that you may change the ODBC string structure, for instance if you omit thePassword excel will prompt you to logon. Notice that
Using parameters.
Parameters allow you to change criteria of a query without any VB code.
Here is the way I use parameters in MS query :
I use my favorite Oracle developpement tool and make a SQL query without parameters for instance :
select name from customers where max_ammount>1000
and min_amount <100
here is the versions with parameters
select name from customers where max_ammount>?
and min_amount < ?
Yes, there is no named parameters. Excel have specifical way to bind those things, just using order.
Try it, for instance with the preceding VB code, or with the MS Query tool.
When refreshing the data, Excel ask you to fill those parameters.
At any time you can specify the source for each parameters:
- Right click on the range of data.
- Choose "parameters"
- Select the parameter you
- Then select the option you want for each parameter.
The first option is interesting. It allows you set the text of a question which will be asked to the user for this parameter.
Notice that when you use VB code to create a query with parameter, Excel gives default names to these parameters. If you want set your own names use the following syntax in a vb module :
Dim qt As QueryTable
Set qt = ActiveSheet.QueryTables("MyQuery")
qt.Parameters("Parameter1").Name = ("myFirstParam")
qt.Parameters("Parameter2").Name = ("mySecondParam")
All properties of parameters seems to be reachable by rightclick->parameters but the name which can be change only by code.
Charts
Now you know how to make get data from parameterized query over Oracle into your Excel workbook, I suggest you the following if you need to produce many charts from yout datawarehouse.
I - Let's suppose you have built a View describing the monthly 's turnover of your products. In a new excel sheet type-in the product id of one of your products, build a query like the following :
Select MonthNumber, turnover from v_monthly_turn where product_Id = ? and year = 2004
II - When the wizard prompt you for the parameter's value, select a cell which contains your product's ID and check the box to make this permanent.
III - When the data are returned select the range. Click on the chart button an choose your chart's style and location.
IV - Change your product's ID and refresh the data by right click->refresh data over the data range.
You may customise this:
Instead of a cell based parameter use a parameter whith a question like "What is the product ID?" . You may then set the "Refresh on file open" property to yes :(right click on the range then "properties") your file will then become a little decisional tool. And this without any VB code!
That 's all for today thank you for your attention.
All comments are welcome.
This is what i have:
Sub Macro4()
'
' Macro4 Macro
' Macro grabada el 25-01-2007 por aruizs
'
'
Sheets("Dat_NO").Select
Range("c2").Select
Año = ActiveCell.FormulaR1C1
Range("c3").Select
RangoDeAproximacionEnAños = ActiveCell.FormulaR1C1
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\practica\depositos.mdb;Defa" _
, "ultDir=C:\practica;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTi" _
), Array("meout=5;")), Destination:=Range("A7"))
.CommandText = Array( _
"SELECT d.instrumento, d.reajuste,d.`fecha cintap`, abs(d.duracion-" & Año & "" _
, "*365) as Alcance, d.Duracion, d.TIR" & Chr(13) & "" & Chr(10) & "FROM `C:\practica\deposito" _
, "s`.Capital d inner join(select c.instrumento, c.reajuste, c.`fecha cintap`, min(abs(c.duracion-" & Año & "*365))" _
, "as Alcance " & Chr(13) & "" & Chr(10) & "from `C:\practica\depositos`.Capital c " & Chr(13) & "" & Chr(10) & "where (c.instrumento='bcp' and c.reajuste='no' and (c.duracion>" & Año & "" _
, "*365-" & RangoDeAproximacionEnAños & "*365/2 and c.duracion<" & Año & "*365+" & RangoDeAproximacionEnAños & "*365/2)) " & Chr(13) & "" & Chr(10) & "group by c.instrumento," _
, "c.reajuste, c.`fecha cintap` " & Chr(13) & "" & Chr(10) & "having (min(abs(c.duracion-" & Año & "*365)))) a " & Chr(13) & "" & Chr(10) & "on a.alcance=abs(d.duracion-" & Año & "*365) and" _
, "d.instrumento=a.instrumento and d.reajuste=a.reajuste and d.`fecha cintap`=a.`fecha cintap`")
.Name = "Consulta desde MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
If yo know how to work it out, please let me know.
aaron.ruiz@gmail.com
: January 25, 2007 1:38 PM
<< Home
