cancel
Showing results for 
Search instead for 
Did you mean: 

Retrieving BEx Query Results Area for use in Excel formulas

bntb_bntb
Explorer
0 Kudos
149

Hello,

I run BEx queries in workbooks and then use Excel formulas ( mostly VLOOKUP(LookupValue, ResultsArea, ColumnReturned,) ) to retrieve certain information from the query ResultsArea.

****Problem:

Depending on the query parameters, the query "Results Area" is larger or smaller.

****Non-optimal solution:

One way to define the ever changing Excel range occupied by the Results Area is to create an Excel name as:

QueryRange = OFFSET(QueryFirstCell,,,COUNTA(OFFSET(Sheet1!$A:$A,,COLUMN(QueryFirstCell)-1,,)),COUNTA(OFFSET(Sheet1!$1:$1,ROW(QueryFirstCell)-1,,,)))

Then, the only input required is the top-left cell of the Results Area, which stays the same (unless the query definition changes).

****Question

What is the best way to refer to an ever-changing query Results Area in an Excel formula?

Thank you!

Louis

Accepted Solutions (0)

Answers (2)

Answers (2)

bntb_bntb
Explorer
0 Kudos

No VBA is required to retrieve a BEX query result range.

Each Excel workbook containing a BEx query has a sheet named "SAPBEXqueries". That sheet can be unhidden by going in the Visual Basic Editor.

That sheet contains sheet-specific range names. The name "SAPBEXq000_" ("_" being the query number) refers to the result area.

Louis

Former Member
0 Kudos

It is actually much easier than that.

Use a VBA statement like the following:

Set RptRng = ActiveCell.CurrentRegion

This will retrieve the data in the Results Area, no matter what the size is.

Hope this helps...

Bob

bntb_bntb
Explorer
0 Kudos

Thank you Bob,

Your answer confirms to me that we either use a long formula as I posted in the itinial tread or use VBA.

Louis