oracle into excel form/report

[BackStageJim]

Perhaps this is the wrong thread, but I’m losing my patients with Excel external data from Oracle.

I need to connect to Oracle to pull data from query and display the information in an Excel sheet that ‘looks’ like a report or single page from word mail merge. Now the query will return one row for each page that is reported. I was able to generate a ‘form’ in SHEET1, but when I try to link SHEET2 with query data, only the first row shows. What am I doing so wrong? Is there an example I’ve failed to find using various keyword searches?

 

[xlbo]

could you provide more details please - how are you trying to

link SHEET2 with query data

Do you mean you want a seperate sheet for each row of data that the query returns ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Jim,

When you state,

"but when I try to link SHEET2 with query data"

how are you trying to do that?

Please post your code or explain your method.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[xlbo]

Skip - your connection must be slowing !!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

It's amusing at times --

my wife asks me to help her design a form and I ask all these probing question about reason & intent and she's taken aback. Comes "naturally" from background & experience.

So ANOTHER "analyst" asks the SAME question!

Hmmmmmmmmmmmmmmmmmm? Some conspriacy!

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[BackStageJim]

I have no real code other then a form in sheet1. Imagine a 1040 tax return. Text on left side, with values on right side.
In sheet2 is the Oracle data in rows … A1 B1 … AA3 [please excuse the primitive Excel, I’m new to Excel other then using it as a column calculator]

What I need to see is each 1040 page on sheet1 filled by sheet2 rows. I think I have to loop thru the rows, but not sure how. Excel is not the same as other languages I have used. Took me 30 minutes to realize CELL(1,1) and RANGE(A1:A1) are the same as A1.

Sheet1
-------------------------------------------]
NCP Enter Date: CQH_DT

Customer Name: CQH_CUST_NAME
Customer Code: CQH_CUST_CODE
Contact Name: CQH_FLEX_01
Contact Phone: CQH_FLEX_02

Administered By: CQH_FLEX_03
Assigned To: CQH_FLEX_04
Assigned Date: CQH_FLEX_05

Response Due Date CQH_FLEX_06

Reviewer: CQH_FLEX_07
-----------------------------------------]

Sheet2
CQH_CUST_CODE CQH_CUST_NAME CQH_ANNOTATION CQH_FLEX_01 CQH_FLEX_02 CQH_FLEX_03 CQH_FLEX_04 CQH_FLEX_05 CQH_FLEX_06 CQH_FLEX_07
Z09-1 10/18/2005 G.W.LO GWL Defective 9140-1420-0000 does not work George Long 215-230-0665 COPPOLSJ COPPOLSJ 10/18/2006 10/19/2006 COWA
Z09-2 10/19/2005 IQS-1 IQS 9140-0138-0002 Receptor Cover Paint Defect Po 222 George Longwell 215-230-0665 COPPOLSJ COPPOLSJ 10/19/2005 10/19/2005 COWA

Sorry about the copy/paste, but each row of data would link to sheet1. Now either the sheet1 data changes as page-down occurs or multipl sheet1 occur per data row count.



Just lost in a fog of Excel functions. Gosh, I miss .net already.

Thanks in advance
-Jim

 

[SkipVought]

How does source row Z09-1 & Z09-2 get mapped to your sheet1?


Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[SkipVought]

BTW,

This could be done quite simply using Named Ranges from Sheet2.

Select ALL data/headings in sheet2 (this ASSUMES a UNIQUE heading for each column of data)

menu item -- Insert/Name/Create -- Create names in TOP row.

On Sheet1, in the cells you want your data, enter expression for each value.

For instance, assuming that this begins in A1 AND there is only ONE row of source data...
[tt]
Customer Name: CQH_CUST_NAME =INDIRECT(B1)
Customer Code: CQH_CUST_CODE =INDIRECT(B2)
Contact Name: CQH_FLEX_01 =INDIRECT(B3)
Contact Phone: CQH_FLEX_02 =INDIRECT(B4)
....
[/tt]
If there are more than One row, the formul for the FIRST row would be
[tt]
=INDEX(INDIRECT(B1),1,1)
[/tt]



Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[BackStageJim]

Thanks:

How does source row Z09-1 & Z09-2 get mapped to your sheet1? Right now using simple =Sheet2!B1, etc. Which is part of the problem I will always have a header row, comming from ORacle. And, at this point, be more then one row.

I like the [=INDEX(INDIRECT(B1),1,1)] format, but need to know how to roll from row 2 to 3, etc. This is where I would use a FOR loop.

I'm just not going to quit ..... Excel wil not beat me.


Thanks for the direction.

 

[SkipVought]

the 2^nd argument is the row index (not the row number). For instance, row index 1 & 2...
[tt]
=INDEX(INDIRECT(B1),1,1)
=INDEX(INDIRECT(B1),2,1)
[/tt]
You have not provided very much information about sheet1 and my crystal ball is kinda cloudy. But if you had a layout like this...
[tt]
A B C D
1 2
Customer Name: CQH_CUST_NAME =INDEX(INDIRECT($B2),C$1,1) =INDEX(INDIRECT($B2),D$1,1)
[/tt]

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[BackStageJim]

Thanks for the directions Skip, here is more for your crystal ball.


SHEET1 ------------------------]
A B
1 NAME [VALUE]
2 ADDRESS
3 CITY
4 STATE
5 ZIP
6 PHONE
-------------------------------]



SHEET2
A B C D E F
1 NAME ADDRESS CITY STATE ZIP PHONE
2 NAME ADDRESS CITY STATE ZIP PHONE
3 NAME ADDRESS CITY STATE ZIP PHONE
4 NAME ADDRESS CITY STATE ZIP PHONE
5 NAME ADDRESS CITY STATE ZIP PHONE

The data is SHEET2 is working from connection to database.
However, I need to roll thru all the records in SHEET1,
similar to using page-down/up. So each SHEET1 is a single
page being printed.


Sorry for confusion in first post.

Jim

 

[SkipVought]

Then it's quite simple -- you create a loop to count each row of source data. Put the result in C1.

     A              B              C
                                   [red][b]1[/b][/red]
Customer Name: CQH_CUST_NAME  =INDEX(INDIRECT($B2),C$1,1)

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[BackStageJim]

guess I get to use that or r1c1 logic.

thanks for the intell

 

[SkipVought]

I wouldn't.

for i = 2 to Sheet2.UsedRange.rows.count
   Sheet1.[C1].Value = i - 1
' print of do whatever for the data in the row
........

Next

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[BackStageJim]

Thanks will have fun today.
-Jim

Great support guys.

 

[abaldwin]

Is there a particular reason you use Excel for the output of what is generally considered a report function?? In other words "Is there a reason you are required to do this in excel?"

I would think that a crystal report or some other reporting tool connected to the Oracle database would be the more efficient means to report results.

Andy

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[SkipVought]

Andy,

Excel is an Excel-ent tool for 1) data mining/analysis from a variety of remote sources and 2) data reporting (pivot table, sub-total, chart, filter...)

Many shops don't have the other reporting tools that you refer to. More than often, you go with what you know.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[abaldwin]

Skip,
I am very familiar with the inability to utilizie other tools because of
1) Budgetary constraints.
2) Lack of other reporting tools.
3) Lack of knowing how to use other tools.

I agree that Excel is an excellent tool for some data mining, analysis etc. No where in the original posters question is there a mention of either trying other tools or the lack of knowing other tools. Hence I simply asked the question to see if they had even considered other tools as a possibility exist that straight up output in a certain format, with page breaks etc is generally considered a reporting function (i.e. Crystal, InfoMaker, etc) and not data mining, or analysis.
Simply putting forth options they may not have considered rather than trying to write code to handle simple output.

Andy





Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[SkipVought]

Andy,

Offering a "why not try..." is a noble effort.

However, this is the [blue]VBA Visual Basic for Applications (Microsoft)[/blue] forum. Options are offered and welcomed within that domain.

Reminds me of a time I ordered a Sweet & Sour Chicken platter ($4.95) and the waiter came back an informed me that they ran out of that, would I like the PooPoo Platter instead? Turned out that the PooPoo Platter ($20.00) couldn't even be consumed by 2 people.

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[BackStageJim]

Ouch, right cross against my face. I'll duck next time.

1) Budgetary constraints. No.
2) Lack of other reporting tools. No, we have Oracle Reports, Cognos, and Crystal. This is an ISO and FDA related issue. They want the report to be sent via email, but not a PDF. The original format was WORD, but we want a quick process to drill into ISO issue, save and send to FDA. They all have Excel, but not other tools.
3) Lack of knowing how to use other tools. Gee thanks! Excel is really the LAST tool I would use for anything.