Software

Ask something
Question Locked

How to write Excel VBA code for a returned ORACLE database query only

0 helpful
8 replies

by chibuikei

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi guys,

I have an ORACLE database which I am using in Excel (front end). Lets say there are 300,000 records and I run a query and it returns 15,000 recors; I am trying to write a simple count (i.e. count = count + 1) macro which considers only the returned records (15,000) not the 300,000 but it keeps using the 300,000

I am using:
EXCEL VBA MACRO
For Each rng In ActiveSheet.UsedRange

after the query has been run but I keep getting 300,000.

How can I get the procedure to run on only the returned records of the query in Excel?

Thanks.

8 replies

by gedwards

Sounds like you want to have a row count to loop through.
See if this might work to set a row counter

Dim iRow As Integer
Sheets(1).Range("A2").Select
Selection.End(xlDown).Select
iRow = ActiveCell.Row -1

Greg E

by chibuikei

Your suggestion solved the problem!!!

You are a genius!

Thanks.

by gedwards

Not a genius. Just had to figure out ways to dynamically adjust to changing number of rows of data.
You may want to explore resizing of named ranges too. Very useful if you use a named range to base a pivot table off of.
Good Luck.
Greg E

by brbaeckl

You can't ask the Oracle database something he/she/it does'nt know.
If you have post a question to the database and se the result in Excel.
You need to either ask the database the same question again but whith SELECT Count(*) ... instead of SELECT * ...
Or you could do a loop in excel that count the rows you just have filled.

by erik.van.roon

In my humble opinion a front end is just that: a front end.
If you wish to know how many rows Oracle returns, let oracle tell you, don't build tricks in the UI.

if your original query looks like this:

select whatever
, and_something_else
from some_table
where my_value = 'my_condition
;

Returning:

WHATEVER AND_SOMETHING_ELSE
-------- ------------------
A V
B W
C X
D Y
E Z

You could add 1 column to your query:

select whatever
, and_something_else
, COUNT(1) OVER () "TOTAL_ROWS"
from some_table
where my_value = 'my_condition
;

Returning:

WHATEVER AND_SOMETHING_ELSE TOTAL_ROWS
-------- ------------------ ----------
A V 5
B W 5
C X 5
D Y 5
E Z 5

As you can see, used this way the analytic function gives you the total number of returned rows in every row.
So your VBA script can get the value it wants from any row that is returned.

Good luck

by gedwards

It sounded to me like they wanted to loop through row by row and do some other manipulation of the data.
Many ways to get to the same end. Just depends on where you want to put your code.
Greg E

by erik.van.roon

"Many ways to get to the same end"
Very, Very true.
And, as I always say: Have a randomly chosen programmer produce any code, have another randomly chosen programmer review it, and the remark will most probably be: "What kind of moron produced this $#!+?".
Meaning: no 2 programmers will ever choose the same route to their goal.

Without exactly knowing the specs I can't say if the chosen approach is suitable or downright silly.
I was mealy pointing out that 'in general' it's wise to let every tool do the tricks it's meant for.

This discussion is closed to new replies.