Tag Archives: vba

BEx Analyzer Macro to Locate position of GRID_1 and position of first data cell

The below macro will locate the top left cell of the BEx DataGrid GRID_1 and the position of the top left data cell.

See also the following post for additional info on VBA and BEx Analyzer
http://sapbwbw.com/bex-analyzer-workbooks-and-vba/ ‎

To make this macro work you need to add the BEx Macros (go to Workbook Settings in the BEx Add In, Exits, Add Macros)

 

===============================================
[Visual Basic light=”true”]
Sub Macro1()
Dim oBexItem As BExItem
Dim oBexItems As BExItems

Dim oBex As Object

Dim range1 As Range

‘ Connect to Bex Analyzer
Set oBex = Application.Run(“BEXAnalyzer.xla!GetBex”)

‘ Obtain all BEx Items in the worksheet
Set oBexItems = oBex.Items

‘ Find the GRID item by name and unhide it plus a range that is offset to the row below it
For Each oBexItem In oBexItems
If (oBexItem.Name = “GRID_1”) Then
‘Set range1 = oBexItem.Range
‘set the correct worksheet
Application.Worksheets(oBexItem.Worksheet.Name).Activate
Dim lFirstDataCell As Variant
lFirstDataCell = oBexItem.DataProvider.Result.Grid.firstdatacell
MsgBox oBexItem.Range.Row
MsgBox oBexItem.Range.Column
MsgBox lFirstDataCell.X
MsgBox lFirstDataCell.Y

GridName = oBexItem.Name
MsgBox GridName
End If
Next

End Sub
[/Visual Basic]
===============================================