Querying data from Oracle based on a Date(s) in Cell H1 (or H1 & H2).

[Brott]

This is a long read, thanks for you time in advance.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Before I begin here is some background. I currently have an Excel file with the ODBC connection and using my own SQL. Doing it this way would require the super user to go into each sheet then to the SQL each time to change the dates. I know there is a way to allow them to simply change the dates in cells H1 (or H1 and H2) then refresh data and whoolaa it would be updated.<o></o>
<o></o>
My goal if possible via this board and all the knowledge available would be to have a spreadsheet allowing a super user to change the date in Cells H1 and H1 & H2 on sheet Tst1 and have the data returned to sheets (Tst2, Tst3) based on my own SQL statements. <o></o>
<o></o>
Here are some parameters that I think you might need to know. <o></o>
(And as you may guess I do not have much VBA experience so if at all possible use my naming convention shown below. and feel free to write for a first grader.)<o></o>
<o></o>
User Name (not actual): "UID"<o></o>
Server (not actual): "DEV"<o></o>
Password (not actual): "PWD"<o></o>
File Name: Excel_ODBC.xls<o></o>
Sheet Name: "Tst2"<o></o>
"Tst3"<o></o>
Output Cell: "A1" for both sheets.<o></o>
Oracle table name: UAB <o></o>
Current Sql scripts (one for each sheet): <o></o>
Sql for sheet "Tst2"<o></o>
SELECT A.UAB_USER_ID_AUTHOR "Clerk", <o></o>
SUM(A.UAB_TOTAL_AMT)"Total Amount",<o></o>
COUNT(*)"Total Number of PAs"<o></o>
FROMUAB A<o></o>
WHERE TRUNC (A.UAB_DATE_CREATED)= TO_DATE ('02/26/2008','MM/DD/YYYY') <o></o>
GROUPBY A.UAB_USER_ID_AUTHOR;<o></o>
Sql for sheet "Tst3"<o></o>
SELECT A.UAB_USER_ID_AUTHOR "Clerk", <o></o>
SUM(A.UAB_TOTAL_AMT)"Total Amount",<o></o>
COUNT(*)"Total Number of PAs"<o></o>
FROMUAB A<o></o>
WHERE TRUNC (A.UAB_DATE_CREATED)>= TO_DATE ('01/01/2008','MM/DD/YYYY')AND<o></o>
TRUNC (A.UAB_DATE_CREATED)<= TO_DATE ('01/31/2008','MM/DD/YYYY') <o></o>
GROUPBY A.UAB_USER_ID_AUTHOR;<o></o>
<o></o>
At this point that is all I can think of. If it would be best served having this in two separate files for each SQL statement that would not be that bad but I would need to know how to handle a single date then the two date ranges. I do appreciate your assistance in helping me with this as I am sure this is very common task. Whewww the end.

 

[Brott]

I have spent the last hour or so reviewing that site previously suggested. I came across many familiar terminology and only one place where it refered to ODBC and Excel. Is there another site that will help with actual syntax?

 

[Brott]

I tried using this and changed names but nothing happened, not even any error messages.

The sheet name is named "Daily" the output would begin in Cell A1 and the date I want to use for my SQL is in Cell E1.

What am i doing wrong...anyone?

I have the below entered into Module 1

Sub UABPYAR_Query()
Dim RunDate As String
Dim sSQL As String
Dim WriteRow As Integer
RunDate = Range("Daily!E1").Value
'--------------------------- Query 1 ---------------------------
sSQL = "SELECT A.UAB_USER_ID_AUTHOR ,SUM(A.UAB_TOTAL_AMT) ,COUNT(*) FROM UAB A WHERE TRUNC (A.UAB_DATE_CREATED)= '" & RunDate & "' GROUPBY A.UAB_USER_ID_AUTHOR;"

'Find the last row containing data and add 2
WriteRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 2
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"ODBC;DSN=UAB;DATABASE=UAB;Trusted_Connection=Yes"), Destination:=Range("A" & WriteRow))
.CommandText = Array(sSQL)
.Name = "Run Query from UAB"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
'.Refresh BackgroundQuery = False
End With
End Sub

 

[Brott]

Thanks for everyone's help. Through trial and error I got this to work.

Sub VBA_ODBC()
'
Dim RunDate As String
Dim sSQL As String
RunDate = Range("Daily!E1").Value

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=dev1;UID=bro;;DBQ=DEV1 ;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM" _
), Array("=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;")), Destination:=Range( _
"A1"))
.CommandText = Array( _
"SELECT A.UAB_USER_ID_AUTHOR , " & Chr(13) & "" & Chr(10) & " SUM(A.UAB_TOTAL_AMT) ," & Chr(13) & "" & Chr(10) & " COUNT(*) " & Chr(13) & "" & Chr(10) & " FROM UAB A" & Chr(13) & "" & Chr(10) & " WHERE TRUNC (A.UAB_DATE_CREATED) = TO_DATE ('" & RunDate & "','MM/DD/YYYY') " & Chr(13) & "" & Chr(10) & " GROUP BY A.UAB" _
, "PYAR_USER_ID_AUTHOR;")
.Name = "Query from cisdev1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwrightDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[kyrgan]

Good job!

How did you manage to get the ODBC working correctly? Looks rather painful.

K.